2 Replies Latest reply on Sep 4, 2013 11:33 PM by bradleyboggs

# Newb Question on Logical Calculations

Hello all,

Pardon me if this is ridiculously basic, but I'm just getting going with Filemaker Pro after a few classes with The Support Group and have hit a wall on my calculated field...

I have a "Total On Hand" field which is a sum of the in/out transactions in a separate Inventory Transactions table (this is used to track incoming materials for inventory purposes, and materials as they're pulled, to decrement the On Hand amount and also have a record of what material lots are used in each order. With these basics, the calculation works just fine.

One wrench in that plan: Sometimes we use left-overs or scraps to fill an order. When this is the case, I need to still keep track of the material lot used for an order, but don't want my calculation to count those records. I've created a "Scap" Field with a Checkbox on the layout - if it's checked it has a 1 in the field, if it's not checked the field is empty.

I only want this "Total On Hand" Calculation to count when the "Scrap" Field is empty, but no matter what I do with IF and CASE and CHOOSE functions I can't seem to get it right.

Here's the working calculation for the on-handbefore considering the scap:

If

(IsEmpty (Sum (InvTrans::UnitsIn) = Sum (InvTrans::UnitsOut) ); 0; Sum (InvTrans::UnitsIn) - Sum(InvTrans::UnitsOut)

Just need to modify that not to count the records where InvTrans::Scrap is Not Empty (or where InvTrans::Scrap = "1").

Any help is greatly appreciated!

Thanks!

• ###### 1. Re: Newb Question on Logical Calculations

If you make a calc field in the InvTrans table with the formula:  if(Scrap = 1; UnitsOut ; "")

and use a Sum to count that field should work

Hope that helps,

Best regards,

Ruben van den Boogaard

Infomatics Software

ruben@infomatics.nl

• ###### 2. Re: Newb Question on Logical Calculations

Thanks a bunch! I knew the solution would be something ridiculously simple, and indeed that turned out to be the case.

Your tip also gave me a better understanding of these calculations as well, so thanks for that!

On to the next hurdle.