Newb Question on Logical Calculations

Discussion created by bradleyboggs on Sep 2, 2013
Latest reply on Sep 4, 2013 by bradleyboggs

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:



(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!