AnsweredAssumed Answered

Q: Global Stock Count Calculation

Question asked by lcot17 on Mar 24, 2016
Latest reply on Mar 30, 2016 by DanielShanahan

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.

 

Tables Associated:

  • Purchase Orders
    • PurchaseOrderLineItems
  • Invoices
    • InvoiceLineItems
  • Online Orders
    • OnlineOrderLineItems
  • Products
    • 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 ) ;

$invoices    =

    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?

Outcomes