7 Replies Latest reply on Feb 27, 2011 6:44 AM by Ween

Calculation

Title

Calculation

Post

Hi to Everyone,

Kindly Help me please to my problem, I have fileds:

1. Material Name

3. Quantity Balance,

4. Quantity Status (Active, Expired)

I have also a Field, I called it Total Quantity Received with a type of Summary (= Quanity Received (running with restart) when sorted by Material Table:: Quanity Received) and Total Quantity Balance with a type of Summary (= Quanity Balance (running with restart) when sorted by Material Table:: Quantity Balance).

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.

Thank you very much.

thong

• 1. Re: Calculation
What does your quantity balance calculation look like?
• 2. Re: Calculation

My Quantity Balance looks like this (Indexed, from Materials Table, = Quantity Received - Quantity Used).

thong

• 3. Re: Calculation

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?

• 4. Re: Calculation

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.

thong

• 5. Re: Calculation

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?

• 6. Re: Calculation

Thank you very much PhilModJunk, It works. Thanks a lot.

thong

• 7. Re: Calculation

Hello

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.