Reduce quantity due on a bunch of orders, based on dispatch quantity

[LLD]

I manufacture an SKU called "tableA"

Assume for e.g., I have various orders for tableA... 

Ord1 on 1 Jan 2021 for 75 tableAs

Ord2 on 8 Feb 2021 for 50 tableAs

Ord3 on 3 Mar 2021 for 110 tableAs

On 1 May 2021, I have dispatched 100 tableAs

On 9 Jun 2021, I have dispatched another 55

how do I check each order (date wise oldest to newest) and reduce the balance quantity due, based on the dispatched quantity? I need to link each unit, while it is being dispatched, to a specific order (priority oldest to newest, and, if there is quantity due on that order)

Note - 

• SKU names e.g. "tableA" are unique.
• Each unit manufactured/ dispatched of tableA has a unique serial number.
• Each unit when dispatched, automatically takes on a dispatch date and the dispatch status changes from N to Y.
• Each order has a unique order#. Each order has an order date.
• We do not receive more than one order on a given date, for an SKU.
• The SKU line entry (child record), takes on a unique ID, i.e. SKU-Ord# (e.g. "tableA-Ord1").. therefore the same SKU cannot be added to an Order more than once.
• Currently, I manually edit the quantity dispatched against each order.

[TeeSee1 · Accepted solution]

In your ORDERS table, you should be able calculate qty due by the following expression

[qty ordered] - COUNT( FILTER("SKU DETAIL", AND([DISPATCH STATUS]="Y",[SKU-Ord#] = [_THISROW].[SKU-Ord#])))

Here the requirement is that the ORDERS table has a col named [SKU-Ord#]. If not, either create one or use string concatenation like ([_THISROW].[SKU] & "-" & [_THISROW].[Ord#])

[TeeSee1 · Accepted solution]

In your ORDERS table, you should be able calculate qty due by the following expression

[qty ordered] - COUNT( FILTER("SKU DETAIL", AND([DISPATCH STATUS]="Y",[SKU-Ord#] = [_THISROW].[SKU-Ord#])))

Here the requirement is that the ORDERS table has a col named [SKU-Ord#]. If not, either create one or use string concatenation like ([_THISROW].[SKU] & "-" & [_THISROW].[Ord#])

[LLD · Accepted solution]

thank you.. I shall try this out and revert.