Iterative Macro Help

[craigja]

I think I need to create an iterative macro... I have a set of data like in the example below, I need to somehow get from the base Task_ID 2324701 to the top level and pull out the Icon Code. You can see that it all links up via Task_ID -> Parent_Project_Id -> Task_Id -> Parnet_Project_Id etc all the way to the top

Task_Id  Icon_Code  Parent_Project_Id
2324635  FLNICA  
2324650                    2324635
2324681                    2324650
2324701                    2324681

[abacon]

@craigja Did you try a multi-row formula? if [Parent_Project_Id]=[Row-1:Task_Id] then [Row-1:Icon_Code] else [Icon_Code] endif got the below output for me, I believe that is what you are looking for.

Bacon

[craigja]

that was just an example from a massive table and I dont believe I can sort the table to get them in the correct order

[abacon]

An iterative macro would work too but this could also get you your output without having to put together a macro. Or you could take this logic and build an iterative macro if that is really what you need.

[craigja]

I think I need a macro as I dont know how many levels i need to drill up to get to the parent level that has the Icon code populated

[abacon]

@craigja That makes sense, then just use this logic and have a check on if the join spits out more than 1 record, keep looping. That should get it because once the join spits out 1 record, you have gone to the deepest level you can.

Bacon

[craigja]

What I need to get it the Icon Code FLNICA and Task ID 2324701 in this example, so just 1 row of data, that says the Icon Code associated with the Task ID 2324701 is FLINCA

[craigja]

But For any Task ID - so some may never drill up to have an Icon Code, some might have 3 levels of drill up, some might have 7 levels of drillup

[Hsandness]

@craigja ,

Without seeing the full dataset I'm not sure why a multi-row formula tool can't be used to solve this but if that's the case then this iterative macro should work. Please try and run this iterative macro against your entire dataset and let me know if it works or if you have questions.

[abacon]

And if you want the first record (RecordID 4), then add in a sort on RecordID descending, then a select records tool to grab 1, this would get the record you are looking for dynamically.