Iterative and Batch Macro help

[Kaseykreid]

Hi all,

I have started to use batch and iterative macro's in my workflow recently to tackle more complex problems. I'm having trouble understanding how to create iterative macro's properly. 

Below is the current problem I'm trying to solve. I have an example of the data and a "Notes" column that details what I'm trying to get my iterative macro to do:

Product	Lead Time (M)	Lead Time (D)	ROP	ROQ	Year Month	Inventory	Order Placed	Notes
Product 1	2	4	13191	5355	2024_01	1934	Yes	I want the iterative macro to check if the inventory is below the ROP. If it is, then I want a "Yes" in the Order Placed column.
Product 1	2	4	13191	5355	2024_02	628
Product 1	2	4	13191	5355	2024_03	-678	PO Onboard	Based on the Lead Time (M) column, I want "PO Onboard"  to added to the "Order Placed" column.
Product 1	2	4	13191	5355	2024_04	-1984
Product 1	2	4	13191	5355	2024_05	2065	Yes	Then when the order has arrived (based on the Lead Time (D) column,) I need the process to start over. Checking if the Inventory is below the ROP again. If it is, the "Order Placed" is populated with "Yes".
Product 1	2	4	13191	5355	2024_06	759
Product 1	2	4	13191	5355	2024_07	-547	PO Onboard	The same happens again and continues for each row.  "PO Onboard"  is placed in the "Order Placed" column.
Product 1	2	4	13191	5355	2024_08	-1853
Product 1	2	4	13191	5355	2024_09	2196	Yes
Product 1	2	4	13191	5355	2024_10	890
Product 1	2	4	13191	5355	2024_11	-416	PO Onboard
Product 1	2	4	13191	5355	2024_12	-1722
Product 1	2	4	13191	5355	2025_01	2327	Yes
Product 1	2	4	13191	5355	2025_02	1021
Product 1	2	4	13191	5355	2025_03	-285	PO Onboard
Product 1	2	4	13191	5355	2025_04	-1591
Product 1	2	4	13191	5355	2025_05	2458	Yes
Product 1	2	4	13191	5355	2025_06	1152
Product 1	2	4	13191	5355	2025_07	-154	PO Onboard
Product 1	2	4	13191	5355	2025_08	-1460
Product 1	2	4	13191	5355	2025_09	2589	Yes

For further context, I already have the inventory calculated in a separate batch macro and iterative macro and then the result of  the macros would feed into the above new macro which will apply the new details in the new Order Placed column. I have a batch macro that batches the various products I want to evaluate and then the iterative macro to do the above. I'm just really stuck on building it out properly to get it to do what I detailed above.

Any help to point me in the direction would be deeply appreciated. 

[rzdodson]

@Kaseykreid would you happen to have a copy of the original workflow you are working off of? Having a hard time understanding what you are saying with your last context statement.

For advice, for iterative macros, I would always lead with a RecordID/Filter tool combo to start your iterative macro. This will allow you to use the RecordID tool's value to test whether it is equal to the iteration number of the macro (or, the number of times we have passed through the macro). 

For broader advice on the approach, I would suggest a nested batch and iterative macro setup. First, we'll go through each Product (Product 1, Product 2, etc.) then each line item in that product's records (the iterative macro portion I am referencing). In the iterative macro section, we then start layering in the testing logic you are describing  in your Notes column to get this workflow firing off the way you intend to.

Hope this guides you along. Happy to help further once we can take a look at the original workflow. :)

[Kaseykreid]

Hi @rzdodson thanks for your response. Attached is the original workflow along with the batch and iterative macros that are producing/correcting the inventory data I want to then apply the above logic to.

I'm still learning how iterative macro's work so I appreciate anything the helps solidify my understanding of them.  

[CoG · Accepted solution]

Here is a sample workflow that I believe will work for your use case (its not the prettiest, but I believe its functional):