What does your quantity balance calculation look like?
My Quantity Balance looks like this (Indexed, from Materials Table, = Quantity Received - Quantity Used).
This is the part of your original post that I am puzzling over. I can read it more than one way:
"My Problem now is, I want that when I search the Material Name the Total Quantity Balance will automatically disregard or subtract the number or amount of the materials received which are Expired. Kindly help me to figure out the calculation."
Do you want the calculation to compute a different result when you "search the material name"? Do you need a second calculation that reads like this:
IF ( Quantity Status = "Expired" ; 0 ; Quantity Received - Quantity Used )
Perhaps that will be the new definition for Quantity Balance?
I'm sorry for making you a little bit confuse.
Do you want the calculation to compute a different result when you "search the material name"? My answer is Yes Please.
I want that if I search the Material (Because I have many materials having the same name but different in Lot No. some are expired and some are not), in my Total Quantity Balance it will automatically subtract the quantities of the expired materials.
Thank you for your help.
If you define a new field named "Balance Active" and define it as IF ( Quantity Status = "Expired" ; 0 ; Quantity Received - Quantity Used )
A summary field defined as the "Total of" Balance Active will return a total that ignores all "expired" entries.
Does that do what you need?
Thank you very much PhilModJunk, It works. Thanks a lot.
I hope you guys can help me here. PhilModJunk has helped me many times and thank you for that. I am a filemaker novice at best. I have created a database for my a youth football league. I am trying to figure out out to carry a balance for a player who hasn't made all of his/her required paments. As seen in the sample below. I want to be able to let this person make another payment (creating a new record) and carry over the balance. I have a player table and a payment table with a relationship in the "player" field.
Thank you in advance!
For the total calculation i have this: league_fee + jersey_fee + raffles + buyout + late fees
For the Balance I have this: payment amount - total