I am trying to bring together a 'Global' stock count for each product record in an 'invoices' style solution.
I have a number of factors that affect the stock count but effectively it is ( stock purchased - stock sold ) with a few variables in between.
- Purchase Orders
- Online Orders
- ProductOptions (this holds size variations for each product e.g. Small, Medium, Large etc)
Each parent table here has a boolean field called 'Deduct Stock' (except purchase orders).
Doing a simple 'Sum' calculation works but does not take into account the 'Deduct Stock' variable. By using an 'if' statement only repeats once, so it takes the 'deduct stock' value from the first record only and sums all records based on that result.
This is what I mean:
Let ( [
$purchased = Sum ( PurchaseOrderLineItems::Qty_S_Received ) ;
If ( Invoices::Deduct_Stock = 1 ; Sum ( InvoiceLineItems::Qty_S ) ; 0 ) ;
$onlineOrders = Sum ( OnlineOrderLineItems::Qty S ) ]
; $purchased - ( $invoices + $onlineOrders )
In the above calculation, if the first record deduct stock is 1, it assumes everything is 1. If it isEmpty, it gives a "?".
My question is: How can I make this work using native calculation? Or do I need to be using a relational eSQL query to calculate?