I am trying to write a Sum If formula so that my inventory table reflect how many items of each products I have depending on the status (Ordered, In Transit, In stock etc). The calculation is based on a Purchase Order Line Items table where
Inventory>-----Products----<Purchase Order Line Items
Basically I want the calculation to sum the total submitted quantity field of the Purchase Order Line Items tables where the record shows a "Submitted" Inventory Status.
I have tried the calculation " Evaluate ( Case (Purchase Order Line Items::Inventory Status) = "submitted" ; Sum (Purchase Order Line Items:Total Quantity Submitted))
But it does not seem to sum every record where the first condition is true, it seems to only return the value of the first record found where the condition is true.
I can't find the right formula.
Screen shot attached.