use of iterative macros to allocate levels in the data

[dd]

I have a dataset which has 2 fields "EmployeeID" and "ReportsToID". I am trying to allocate levels to each employee in the data based on at what level do they report to with respect to the level 0.

Example:

RowID	EmployeeID	ManagerID	Level
1	1001	0
2	1002	1001
3	1003	1001
4	1004	1002
5	1005	1002
6	1006	1003

A) I have created a workflow (1.yxmd) that finds the level 0 manager and sets the level 0 for all the employees reporting to manager (rows 1 in this table above).

RowID	EmployeeID	ManagerID	Level
1	1001	0	0
2	1002	1001
3	1003	1001
4	1004	1002
5	1005	1002
6	1006	1003

B) After this I created a macro that iterates through this data, filters for the employees reporting to the manager at max "level" in the data at this stage>> RowID 2 and 3. Macro name: "2.yxmc"

C) These records are passed to another macro that finds employees reporting to these employees (1002 and 1003) and allocates level=max_level+1. Macro name: "1.yxmc"

Macro (C) works fine but macro (B) is executing once only and hence not able to allocate teh levels to rest of the records. Any suggetsions/help on why this is not working?

[Joe_Mako · Accepted solution]

How about the attached?

After removing the known level from your example data, the workflow adds the Level field with a value of 1 when the "ReportsTo ID" is 0, and then calls the macro.

The macro will take the iterative input, filter if Level is Null, joining those two streams together by "Employee ID" from the Left and "ReportsTo ID" from the Right, formula to add 1 to the known level, union all the data back together, and output all to the loop.

Using the Interface Designer view, I set the iterations to to stop at 20, if you had more than 20 levels, you would need to increase this.

[dd]

Thanks a lot for a very elegant solution.