Hello,
I have a list of orders with different SKUs and I'm looking to reduce inventory accordingly. I want to have a total running based on order ID and SKU.
So, if I sort the data by SKU, I get the remaining quantity. However, I can't have visibility on the whole order. If I don't have enough invenotry to fill a SKU in an order, I want to be able to not count the order in the remaining quantity (the order will be cancelled).
For the moment, I am using the tool "sort" followed by the tool "multiple row formula". Here's the expression I'm using:
IF [Row-1:SKU]=[SKU] THEN [Row-1:Remaining]-[Quantity]
ELSE [Inventory]-[Quantity]
ENDIF
Here's a very abbreviated version of what I'm trying to do:
| Order ID | SKU | Quantity | Inventory | Remaining Quantity | Expected Remaining Quantity | Explication |
| 1 | 101 | 2 | 3 | 1 | 1 | 3-2 = 1 |
| 1 | 102 | 1 | 5 | 4 | 4 | 5-1 = 4 |
| 2 | 101 | 4 | 3 | -3 | N/A | 1-4 = -3 (not possible) |
| 2 | 102 | 2 | 5 | 2 | N/A | considering previous result |
| 3 | 101 | 1 | 3 | -4 | 0 | 1-1 = 0 (based on first row) |
Thank you for your support 
