Multiple Row Formula with 2 Conditions

[mlgauthier]

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 :) 

[aatalai]

@mlgauthier  does he attached workflow help 

[mlgauthier]

That's partly helpful, thanks!

The “explanation” column doesn't need to be modeled. It was simply to support my explanations above.

I think it helps with the first part. Now, considering that item 101 of order #2 is unavailable, how do we make sure that item 102 of order #2 doesn't affect the inventory? The idea would be that the order will be processed differently so I don't want it to affect inventory.

Thanks again, I feel that I'm getting there. 

[aatalai]

so to clarify if you can't do one part of the order you don't do any of it?

[mlgauthier]

Exactly! The challenge is that the file I'm using contains a lot of SKUs and a lot of orders, so I can't link the orders together, especially since I don't know which other part of the order is on which row.

[aatalai]

OK check this out a lot more complex but should answer your question