How to create a batch macro where the output of one row is used as an input in the next

[Loai95]

Hey all,

I am trying to fill out the starting amount from the ending amount of the previous row for each row. I want each row to be filled out before the workflow proceeds to the next row. Please find an example of the data attached.

Thank you

[randreag]

Hi @Loai95 

I don't really understand what you trying to accomplish. It seems you need is a multirow-formula.

If you could give an example, perhaps I can help you

[clmc9601 · Accepted solution]

Hi @Loai95,

I built an iterative macro that I believe accomplishes what you are trying to calculate (find ending inventory for one row, then populate that to the next row's beginning inventory, then calculate ending inventory for the second row). However, you'll probably want to change how ending inventory is calculated because the formula I used resulted in a lot of negative numbers. This should be a fairly easy change though. I hope this helps! 

 

[Loai95]

a multi-row formula could be a part of the solution. What I am trying to do is calculate the Ending inventory balance for each month, the ending balance of the first month is used as the opening balance for the second month. I have some data attached.

[Loai95]

When I run it using my dataset which has the same exact format it doesn't work. Any idea why that might be happening?

[clmc9601]

@Loai95 what error message is it giving you and on which tool? Can you show screenshots of the problem? There are many ways the workflow could "not work" so more specific is helpful for troubleshooting!

One thing you can try is making sure the input data tool is set to "xlsx" instead of "legacy xlsx", which is likely where I left it (that's the only way excel files run on my computer).

[Loai95]

@clmc9601 Here is the error I have been getting from all iteration except the first, and for the first the Ending balance isn't calculated:  Error: Fill from previous row iterative macro (3): Iteration #2: Tool #11: The field schema for the output "Exit" changed between iterations.

Also, the input file is .xlsx and not legacy.xslx.

[clmc9601]

This error can usually be fixed by changing the macro settings (View > Interface Designer > Settings (fourth icon on the left) > Auto-configure schema by name) or by deselecting "dynamic or unknown fields" right before the macro output. 

[Loai95]

Thanks @clmc9601  your solution worked perfectly

[Jack6627]

Hi @clmc9601 , I have a similar problem that I need to solve and this macro is a good start for me. However when I run it, it throws an error saying

Iteration #1: Tool #4: Parse error at char (27). Unknown variable "Inv# Beg" (Expression 1)

The output connection "Exit" was not valid

Also when I run the attached .yxmc file separately, the values in the Inv# Beg and Ending columns is not completed as expected as shown below.

I have changed the settings of the macro to Auto-configure schema by name as suggested above but even that does not seem to work. Appreciate your help!