Multi-Row Formula Tool

Question

Hi, I am trying to use the multi-row formula tool to calculate a new field but I cannot seem to get it. For this new field, we are concerned with 3 fields : Product Number, Back Order and Quantity Received.

I want a field which will calculate the Back Order Clearance. The issue is that each product code may have multiple lines showing different quantities that have been received in. The back order remains the same for these lines as they are all the same product number. I want the Back Order Clearance field to take the minimum quantity between 'Back order' and 'Quantity Received' but also to factor in whether this back order has been cleared already on previous lines. So for example, Product 1 has 4 lines of 10 , 9 , 9 and 7 units received in. There is 15 units on back order. I want back order clearance to be 10 & 5 on the first two lines and 0 on the next two.

I have used the running total tool for 'Quantity Received' by Product Number as I know running total is required but I am struggling with the multi-row tool.

Any help would be greatly appreciated.

Thanks

CianOR99 · Answer

Perfect, seems to be working. Thanks!

FinnCharlton · Answer

@CianOR99 Good spot, here's the fix:

Just need to change the 'Values for Rows that don't exist' to '0 or Empty'. See Product 3 as an example. If 20 are received with 10 on back order, BO Clearance is now 10

CianOR99 · Answer

Thanks for the solution.

It works to an extent. However, if the quantity received in on the first line of a particular product number is bigger than the back order, it returns 0.

So an example I see at the moment is Product X has 38 qty received in and 20 on back order. The BO Clearance using this calculation is 0. and it should be 20.

Any adjustments?