Problems determining levels in hierarchy with iterative macro

[heh]

Hi all,

I've stumbled across a problem when I'm trying to designate levels on a quite simple organizational hierarchy. 

The hierarchy looks roughly like this - complete example in the files included.

Unique Identifier	Name	Reports To	Level
0	Employee_0
3	Employee_3	0
8	Employee_8	0
9	Employee_9	0
11	Employee_11	0
13	Employee_13	0
14	Employee_14	0
147	Employee_147	3
148	Employee_148	3
149	Employee_149	3

I want to determine the level of all the employees from the top (Employee_0) - like this: 

Unique Identifier	Name	Reports To	Level
0	Employee_0	0
3	Employee_3	0	1
8	Employee_8	0	1
9	Employee_9	0	1
11	Employee_11	0	1
13	Employee_13	0	1
14	Employee_14	0	1
147	Employee_147	3	2
148	Employee_148	3	2
149	Employee_149	3	2

I think the problem I'm facing is that I need to be able to determine the correct level for Employee_0 in iteration 1 and then somehow deliver it back to the input for iteration 2 in order to correctly set the level for Level 1 employees.

Any ideas on how to solve this problem would of course be greatly appreciated as I can't see to wrap my head around it. 

Thanks,

Hans Erik

[kat]

Hi @heh

Take a look at this. It was a weekly challenge that dealt with the same problem and should help you along.

Cheers

Katrin

[jdunkerley79 · Accepted solution]

I attach a quick attempt.

The trick I sue is to send the all the data round and round and stop only when no new rows are added to hierarchy or all are assigned.

I do this by counting records and then using a filter to determine whether or not to loop.

[heh]

Thanks @jdunkerley79 for an elegent solution. And thanks @kat for pointing me in the direction of one of the useful articles so I actually get a little bit better grasp on the iterative macros. 

I really appreciate it guys.

All the best,

Hans Erik