Can you help me with a simple way to perform a conditional calculation? I have 2 tables - Item Master which holds static information about an item - and Item Detail which holds purchase detail information of that item. In each table I have a calculated field for cost per serving. Currently in the Item master, my calculation is Max (Item Detail::Serving Cost). My problem is I only want to consider the detail records that I actually still have in inventory (Item Detail::Qty OH > 0). Is there a way to add a conditional statement to the Item Master Calculation?
Not without using a WHERE clause inside an ExecuteSQL query.
But you can set up a calculation field inside the ITem Detail table to get the desired result:
If ( Qty OH > 0 ; Serving Cost )
Then modify your Max function in Item Master to take the Max of this calculation field.