Multi Row Formula Parsee Error

Greetings

I'm trying to allocate available stock based on the earliest requirement date until the unrestricted stock is 0 then noting how much we are short

However I'm getting a Parsee error

This is what I'm aiming to get

Order Material Required Date Required Qty Todays Stock Stock Remain Note

0020018728 10097-0100 20220522 30.000 51.000 21
0020020708 10097-0100 20220526 1.000 51.000 20
0020020739 10097-0100 20220527 24.000 51.000 0 Short 4
0020005591 10097-2100 20220407 1.000 51.000 0 Short 1
0020005591 10097-2100 20220407 1.000 51.000 0 Short 1
0020005591 10097-2100 20220407 1.000 51.000 0 Short 1
0020005591 10097-2100 20220407 1.000 51.000 0 Short 1

Attached is my attempt at the multi row formula and the excel with the data.

Am I using the right tool? or should I be considering a reiterative macro?

FIFO Allocation Multi row formula.yxmd

FIFO Stock.xlsx

Workflow

Developer Tools

Accepted answers

BAvitia88

Found the solution:

Created A Record # field used the multi row formula to group and number rows used this statement in a second multi row formula

IF tonumber([Unrest Stock])>0 and tonumber([Record #])=1 and tonumber([ReqQty])<=tonumber([Unrest Stock]) then tonumber([Unrest Stock])-tonumber([ReqQty]) Elseif tonumber([Unrest Stock])>0 and tonumber([Record #])>1
and tonumber([ReqQty])<=tonumber([Unrest Stock]) Then tonumber([Row-1:Remain]) -tonumber([ReqQty]) else "" endif

Then added a formula

IF !IsNull([Unrest Stock]) and [Remain]<'0' then "short"+" "+[Remain] elseif IsNull([Unrest Stock]) then "short"+" "+toString([ReqQty]) else "Available" endif

And it worked

Order Material Req Date Required Stock Row Remain Short/Available

0020018728 10097-0100 20220522 30.000 51.000 1 21 Available
0020020708 10097-0100 20220526 1.000 51.000 2 20 Available
0020020739 10097-0100 20220527 24.000 51.000 3 -4 short -4

All comments

IraWatt

Hey @BAvitia88,

Haven't checked the logic yet put the parse issue was just due to missing the "IF" at the beginning:

IF tonumber([Stock remain/short])>0 and  tonumber([ReqQty])<tonumber([Row-1:Stock remain/short]) Then "available" else "short" endif

FIFO Allocation Multi row formula.yxmd

binu_acs

@BAvitia88 as @IraWatt mentioned the multi-row formula tool missing the IF statement.

FIFO Allocation Multi row formula.yxmd

BAvitia88

Thank you

However it is not decrementing the stock remaining/short so its classifying as available when we are out

Order Material date Req Qty Available today remain

0020018728 10097-0100 20220522 30.000 51.000 21 Available we need 30 have 51 remain 21
0020020708 10097-0100 20220526 1.000 51.000 20 Available we need 1 Have 21 remain 20
0020020739 10097-0100 20220527 24.000 51.000 0 Short 4 we need 24 have 20 short 4

Capture.PNG

BAvitia88

Found the solution:

Created A Record # field used the multi row formula to group and number rows used this statement in a second multi row formula

Then added a formula

IF !IsNull([Unrest Stock]) and [Remain]<'0' then "short"+" "+[Remain] elseif IsNull([Unrest Stock]) then "short"+" "+toString([ReqQty]) else "Available" endif

And it worked

Order Material Req Date Required Stock Row Remain Short/Available

0020018728 10097-0100 20220522 30.000 51.000 1 21 Available
0020020708 10097-0100 20220526 1.000 51.000 2 20 Available
0020020739 10097-0100 20220527 24.000 51.000 3 -4 short -4

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703