You could try:
GetSummary ( Summary of StockIN ; Summary of StockIN ) - GetSummary ( Summary of StockOUT ; Summary of StockOUT )
But … this will be an unstored calc, and the summaries themselves are unstored, so beware of performance issues if the dataset is large.
You might be better to look at another method for tracking, e.g. replacing the summary fields with number fields that are set to auto enter by calculation and update as stock transactions occur.
I recommend you make your inventory table "transactional", storing inventory in/out "transactions" in a table related to your products table with a +/- value depending on the direction. Ideally you would want your "amount in inventory" to be in the products table, not in the inventory transactions table, to avoid tricky summary issues like you are running into.
Setting it up with the following relationship:
products::primaryKey --<- inventory::productKey
And configuring this auto-enter calc on a number field:
inventory::inventoryValue = if ( inventory::type = "outgoing" ; -1 * inventory::quantity ; inventory::quantity )
That way you could easily use this to get "Amount in stock":
products::amountInInventory = sum(inventory::inventoryValue)
This type of setup allows you to place a portal on your products detail view as well to see a complete transactional history of all inventory coming in and out, and makes audits and inventory checks a breeze. It also helps avoid duplicate products in your system. And you don't have to divide two summary fields, resulting in a faster calc as well.
Thanks Mike, I think I've sorted it now, by ensuring that stock in a positive integer value, and stock out is represented by a negative value (despite what's show to the user in the interface) the summaries have become much easier.
Thanks for all your help!
I tried this approach, but I just couldn't get the summary's to show the correct data given their placement on the specific part in the layout. Solved it another way now, but thanks for the idea...
No worries. The system proposed by Mike is much better anyway.