List of all direct reports and subordinates

[JimSutt]

I'm trying to build a Power Automate flow that will output a list of all every user that reports (both directly and indirectly) to a manager user ID (input).  I have tried to use the "Office 365 Users.Get direct reports" action, save the output to an array variable, and then loop through that array with the same "Get direct reports" action to return the next layer of users.  When finished, I would append all these lists together for a single complete list of all direct and indirect reports.

My issue is there could be 5+ layers of management in the organization, so I would like the flow to keep digging into each user until the "Get direct reports" returns nothing.  There has to be a simpler method than essentially an infinite number of embedded loops!

[JimSutt]

@antonyclark, everything you should need will be included in the two posts marked as the accepted solutions.  If you have issues, I will do my best to help troubleshoot.  

[antonyclark]

Super, I'll be having  crack at this later this evening or tomorrow.

Thanks for you help man.

[RohitD]

@JimSutt - Thanks for sharing the flow. Can you please help me with the code for "Length" field in the JSON step

[JimSutt]

Hi @RohitD , the length statement is as follows:

length(variables('FinalArray'))

This statement returns the number of rows in the array.  The Do Until loop will continually run until the counter catches up to the the number of rows in the array, indicating that there are no new rows.  

[Anonymous]

Hoping someone can help me, I followed the screenshots but am getting an error of "resource / user not found: in the direct reports v2 step ? It looks like it's finding the the email from JSON string? 

{
  "error": {
    "code": "ResourceNotFound",
    "message": "User not found",
    "innerError": {

Also, it looks like by default the do until runs 60 times. We have 200+ employees under our VP so I'm not sure what to put in the limit under advance settings in do until. thoughts? 

[Joe_Cronk]

Hi @JimSutt , I'm completely new to Power Automate, so please excuse me if this is a stupid question.  I'm trying to recreate this flow from your screenshots, and everything looks the same until I get to the point where I'm adding the 'Select' action.  When I select 'Email' from the Dynamic Content window as my value, it wraps the Select action with an 'Apply to each'.  Do you have any advice on this? 

Before:

After:

Thanks,

Joe

[KennyJ]

Hi JimSutt,

I have managed to re-create your flow, which is great by the way.  I do have 2 questions:

1) My flow is stopping once it reaches the first person who does not have any direct reports?  How did you actually get around this?

2) Does the flow continue to keep checking as every user for direct report reports as the array grows?

Thanks

Kenny

[KennyJ]

what do you put in the formula to check if it was empty?

Thanks

[Anonymous]

Use "Value" and not "body" in the select statement, "Value" is the array.

[Anonymous]

There is another method which is safer than the Get Manager option because sometimes the Get Manager can crash if the employee is not in the company. A better method is using the the Get Direct Reporters connector and find the direct reporters of the manager's direct reporters , and do this till a certain number of times.

[Anonymous]

There is another method which is safer than the Get Manager option because sometimes the Get Manager can crash if the employee is not in the company. A better method is using the the Get Direct Reporters connector and find the direct reporters of the manager's direct reporters , and do this till a certain number of times.

[jake]

Came here to say thanks! This is pretty brilliant and I was able to get this adapted for our needs fairly quickly. Made a few small enhancements, in case anyone is curious:

1. Collect (and count) the number of direct reports for each person in the array:

2. Create a separate array (not used for looping or iteration) to hold an object for each person discovered via recursion

3. Use that secondary array to batch write those objects over to SharePoint

Creating objects and storing them in a separate array allows us to easily record additional data for each individual, like who they report to, and how many direct reports they have.

[Joe_Cronk]

Thanks RBowker, that worked!

[Joe_Cronk]

Hi JimSutt,

Sorry, I have another stupid question for ya.  I've setup my flow, which runs successfully, but I don't know where I'm supposed to see the CSV file that gets outputted.  Can you advise?

Thanks,

Joe

[JimSutt]

Hi @Joe_Cronk ,

In the solution, the last step is just to create the CSV data, which you can see in the run history within Power Automate (expand "Create CSV" and look at the output).  If you want to write it to a file or build an Excel file from the output, you would need to add those steps to the end of the flow.  I do not run this flow very often so I just copy the results from the run history into Notepad or Excel.  However, if I was going to use this flow more frequently, I would probably save the CSV date to a file on OneDrive.

Regards,

Jim

[ericshufro]

Did you manage to resolve this?  Happens for me as well.

[ericshufro]

Same issue?  Did you find a solution?  Perhaps a condition branch to avoid empty records when a report has no reports?

[ericshufro]

solved.  missed some step when following the directions the first time.  had to add some additional filtering for null fields.

[Anonymous]

I know this is an old thread but when I reproduced the flow, I'm getting this error message:

Error: Action 'Parse_JSON' failed

Error Details: The 'content' property of actions of type 'ParseJson' must be valid JSON. The provided value cannot be parsed: 'Unexpected character encountered while parsing value: v. Path '', line 0, position 0.'.

What does this mean? I had to use "search for users V2" as "search for users" is no longer available, but the output from that step looks successful. It found the user and returned their info, it's at the parse JSON where the flow fails every time. I've double and triple checked that I copied/pasted the JSON exactly and that my "select" fields match the fields in the JSON exactly. I'm not sure why it would be failing.

[emunroginn]

Just in case you haven't worked this out yet, I was having the same problem and fixed it by:

• going into the Content field
• selecting Add dynamic content
• clicking on the two variables from the dynamic content (in the right sequence)
• adding in the [] around the ArrayCounter variable.

When I look at the peek code, it now shows the content as: @variables('FinalArray')[variables('ArrayCounter')]

Hope this helps!