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!