11 Replies Latest reply on Feb 24, 2016 10:58 AM by Mike_Mitchell

Calculating Total Quantity Balance

Hello all,

I have a Materials Table having the following fields

Material Name

Material Quantity Received

Material Quantity Balance

Material Quantity Used

Material Status Drop down List (Quarantine ; Used ; Rejected ; Near to Expiry; Expired ; Rejected)

I want to calculate the total Quantity Used excluding the materials with the status of (Used ; Rejected ; and Expired)

Any formula for the calculation.

Thank you.

Lola

• 1. Re: Calculating Total Quantity Balance

Maybe something like this?

Let ( [

F1 = "SELECT

SUM ( \"Material Quantity Received\" ),

SUM ( \"Material Quantity Used\" )

FROM

\"Materials Table\"

WHERE

STATUS NOT IN ( 'Used', 'Rejected', 'Expired' )" ;

F2 = ExecuteSQL ( F1 ; " - " ; ¶ ) ;

F3 = Evaluate  ( F2 )

] ;

F3

)

• 2. Re: Calculating Total Quantity Balance

One more thing, if Status has nulls, you may need an additional WHERE statement.

• 3. Re: Calculating Total Quantity Balance

Hi Taylor,

Thanks for the reply. Sorry Im using Filemaker 7.

Thanks.

• 4. Re: Calculating Total Quantity Balance

You'll probably want to create a relationship that has the status codes you want as a list. You can get that via the List function (for simplicity):

List ( Quarantine ; Near to Expiry )

Then add this field to whatever other relationship you have in place. Your calculation can then just use summary fields in the child table to get the totals. (e.g., a summary field that is the total of Quantity Used)

HTH

Mike

• 5. Re: Calculating Total Quantity Balance

FileMaker 7.... hmmmm... goodness... what are you able to run that on?  Why?????

• 6. Re: Calculating Total Quantity Balance

Unfortunately the OP has FileMaker Pro 7 so they have no List function.

So you can use a calculation field to get the quantity for each record:

Materials Quantity Used Reportable

If ( status ≠ "Used" or status ≠ "Rejected" or status ≠ "Expired" ; 0 ; Material Quantity Used )

Then use a Summary field to sum those values for a group.

• 7. Re: Calculating Total Quantity Balance

I saw "7" and thought ".fp7". Oops.

Nevertheless, you can just create the field this way:

"Quarantine¶Near to Expiry"

And yes, you can use the "old school" method to get the total as well. But I would still use a summary field rather than a calculation. More flexible, and performs a bit better.

• 8. Re: Calculating Total Quantity Balance

Sorry my bad...Im supposed to say..filemaker 11.

thank you.

• 9. Re: Calculating Total Quantity Balance

As a further point: If you just use a Count summary field and a filtered relationship, such as what I've suggested, then there's no need for the additional field. It'll be more flexible as well, since you don't have to change database schema if the list of status codes changes.

• 10. Re: Calculating Total Quantity Balance

Hello All,

I tried using case function

Case (  Material Remarks = "Expired" ; 0 ;

Material Remarks = "Rejected"  ; 0 ;

Material Remarks = "Used" ; 0 ;

Material Quantity Received - Material Quantity Used )

But not giving me the correct total supposed to be it will give me 150.

Thanks

• 11. Re: Calculating Total Quantity Balance

You need two fields. The first is the one you already have. The second is a summary field that totals the first field.

The layout is showing the value of the last record. That is correct, if it's the first field. Create the second field and put that in the footer.