Iterative macro

[oumaimak]

Hello,

My goal is to build a macro that will allow me to put in an input such as :

File_id	File_parent_id
1
2	1
3	2
4	3
7
9
10
465
6789	465
568	6789

And get as a final result :

File_id	File_parent_id
1
2	1
3	1
4	1
7
9
10
465
6789	465
568	465

The idea is to be able to identify the original parent file for each file_id.

Seeing that I never used macros especially the iterative ones, I have some trouble finding the right logic to get to my result.

Highly appreciate any indications and explanations

[Stosh · Accepted solution]

I would probably need more information to be sure that this is the best approach, but based on what you have provided, I would do the following: Perform an outer-join between the table and itself, setting one sides parent equal to the other sides child. What this will do is return parents of parents if they exist. For example, row two would match nothing because 1 has no parent, but row three would return 1 as 2, the parent of row three has it's own parent. Then, if the new parent field is not null, replace the original parent with the new one. Then you push that list back into the loop and iterate until every parent is the absolute base parent. Now, all you need is a way to verify if each new parent is parent-less, as that is when you would want to stop iterating. You can probably achieve this by performing another join like you did initially and if there are no records in the J output, then you know that each parent has no parent of their own. This is a cool problem, I would love to hear about how you get it done. Good luck!

[Jeana769Hadley]

Hello!

You want each file to point to its original root parent instead of just the immediate one. The logic is to trace each File_id up its parent chain until you reach the first ancestor with no parent, then replace the parent with that root. For example, file 4 → 3 → 2 → 1, so its root parent is 1; file 568 → 6789 → 465, so its root parent is 465. A VBA macro can loop through rows, follow each chain, and update the parent column with the root, giving you the final table where every file shows its top-level parent. 

[OllieClarke]

@oumaimak No need for an iterative macro. The Make group tool will solve your problem here

This workflow will group the connected ids, attach their ultimate parent and update the file_parent_id

Hope that helps,

Ollie

[OllieClarke]

@Jeana769Hadley 

I think @oumaimak knew what they wanted to do, your post just restates the problem. Also this is an Alteryx forum, so VBA macro talk is uncommon and normally unhelpful, but I'm guessing you used an LLM to write this reply?

[oumaimak]

Hey everyone,

Thanks so much for your responses, and sorry for the delay in getting back to you! I ended up going with @Stosh’s suggestion. I’d already started down a similar path but got stuck on the join logic, so his answer really helped me finish the macro. It’s not super clean yet, but it works for now. I used a single join and counted the number of files where I could identify the parent file. If the count matched the one I entered, the macro completed successfully.

Regarding the VBA macro, that’s not really what I’m looking for—I prefer keeping everything visible and clear in my workflow, and I’m not familiar with VBA at all.

@OllieClarke, thanks for the suggestion! Your approach with the "make group" tool sounds really interesting, even though I’m not familiar with it yet. I’ll definitely check out your workflow to get a better understanding.

I’ve attached my macro in case you’re interested.

[OllieClarke]

<deleted>