Interactive Macro: how to update two fields from two source inputs at the same time

[yiying]

Hi there. I have been trying to create an iterative macro but I just cannot figure it out. I have attached a simplified version of my workflow. I am planning to use this workflow to look at each month's demand separately and calculate the inventory changes based on that. For example I would like to look at May's demand in the first iteration and calculate the inventory remaining qty after that. We will use the new inventory remaining qty we just get and subtract June's demand qty from it in next iteration. 

I'm sorry that I cannot provide the actual data that I'm working with. Right now I'm generally confused and do not know if this can be done in a single iterative macro. Should I do two separate macro? one for updating the month and one connecting to the inventory file to update the qty? If someone can help me out I will be really appreciated! Thanks for your time

[danilang · Accepted solution]

Hi @yiying 

From what I understand from your workflow, your end goal is to show the decrease in inventory in each of your components for all the months in your sample.  You can easily do this in a standard workflow, without requiring the added complexity of an iterative macro

The key to this lies in getting the data in the correct order and then using a multirow formula to fill in the remaining qty of each component at the end of the month

The workflow is starts out similar to your, except that I removed your filter from the middle branch and summed the components used in each month in the top one.  The Summation tool is used to find the initial qty of each component.   After the second join, the Sort tool arranges all the records so that all the records are grouped by component and sorted in ascending month order within the component group.  After this, the Multi-row tool, which is set to group by component processes the records one by one.  If it's the first month for that component, it subtracts the qty used during the month from the Total Inv calculated and sets this as the final inv for that month.  If it's not the first month for that component, the qty is subtracted from the previous months final inv, which happens to be the previous record because of the sort order.  The process continues for all the months for that component.  When the component changes, the iterative subtraction process starts again.

The final output is this

    

which shows the qty on hand at the end of each month for each of the components

Dan

[yiying]

That works perfectly. Thanks a ton!