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!

[JimSutt1]

@J__N I don't know what is not working in your flow.  Please try a brand new flow with just these steps and see if it works.  You should be able to test it with a legitimate email in your organization, go into the Run History, and expand the Select step to see the details of the individual who's email you entered.  

[JimSutt1]

@Diti see the following screenshot of the code I wrote to check a SharePoint list if the record exists or not, and then write to the list if the record does not exist.  This code can be added in the loop before or after appending the row to the FinalArray.  NOTE:  You'll need to change the source to the Output.  My screenshot is checking for the Manager, but the concept is the same:  For each record you're adding to FinalArray, check if that record already exists in the SP List and add the record if it does not.  

 

[Diti]

hey Jim - I have tried to apply the sample you've provided to my scenario but with no success. Below is what I'm using: the first screenshot is the summary view and the following screenshots show the detail for each step.

[Anonymous]

Hi JimSutt - Thanks for sharing the flow. I tried to replicate it - somehow it doesn't work for me. Also I'm unable to save the flow as it gives below error message:

Flow save failed with code 'InvalidTemplate' and message 'The template validation failed: 'The inputs of template action 'Initialize_array_variable:_FinalArray' at line '1 and column '1561' is invalid. Action 'Apply_to_each' must be a parent 'foreach' scope of action 'Initialize_array_variable:_FinalArray' to be referenced by 'repeatItems' or 'items' functions.'.'.

I'm attaching the Flow for your quick reference. Would really appreciate if you could guide me on this.

Note: The first 'Apply to Each' gets automatically applied - I'm unable to prevent it from happening.

P.S - I'm a new to Power Automate - Apologies in advance if any of my doubts seem silly.

[JimSutt1]

@Diti In the "Check SP List for Employee" step, change the Filter Query to:

Mail eq '<expression>'

You are missing the single quotes.

Next change the above <expression> to:

items('Apply_to_each')?['Mail']

 When finished, it should say:

Mail eq 'items('Apply_to_each')?['Mail']'

In the "Create Item" step, change all of the expressions to:

items('Apply_to_each')?['<field name>']

For instance, DisplayName should be set to:

items('Apply_to_each')?['DisplayName']

[JimSutt1]

@Anonymous In the "Search for users (V2)" step, click "Show advanced options" and set Top = 1.  In the "Apply to each" loop, click and drag the Select step to the space between the "Search for users (V2)" and the "Apply to each" step.  This should allow you to get the Select step outside of the loop.  Then delete the "Apply to each" step.  

[Diti]

You sir are amazing!! 

If I wanted to delete any items that show in the SP list and not in my list of direct reports (e.g when an employee leaves), do I add a delete item action such as below:

[Anonymous]

Hi Jim, the 1st part worked. However Unable to remove 'Apply to each' even after doing drag & drop.

Any workaround for this?

I'm happy to add couple of steps if need be; but just want to cross this last mile & make the flow work.

[JimSutt1]

@Diti I don't think that will work where you have it.  I haven't done this before, so it would take a lot of trial and error, but I think you would need to add steps at the end of your flow that would do the following:

1. Get all the rows in your SharePoint List (SharePoint.Get Items)
2. Loop through all the rows from Step 1.
   1. Search the FinalArray for the current item in the loop.
   2. If found, do nothing.  If not found, delete that row from the SP list.

[Anonymous]

Hi Jim.. I re-did the entire flow & got over the previous issues. When I did a test run - it's giving error in the 2nd last step i.e. Append to Array Variable. It says "The action type 'AppendToArrayVariable' only supports values of types 'Float, Integer, String, Boolean, Object'."

Could you help with this please.

[JimSutt1]

@Anonymous I'm sorry, but I'm not sure why you're getting that error.  The input to the "Apply to each" loop is an array containing strings and a boolean.  You could try to simplify it for testing purposes in the "Select 2" step by only including one field (ex. "Mail") and see if that works.  If so, try adding the fields back in until you determine which is causing the issue.  

[Diti]

Thank you! Will try to play around with this today! I will post it if i find the solution

[Anonymous]

You are a Legend Jim. The Flow worked wonders...! Thank you so much for your help.

A quick question, If I may - While the output was great of the flow, it's missing the Reporting to / Manager field for subordinates. Any way we can get this added in this flow itself?

[JimSutt1]

@Anonymous The manager email is included in the flow already.  Expand the "Finally" scope and see "Scope 2".  You can use any of the fields returned in the "Get direct reports (V2)", then add them to the "Select 2" step.  If you're not sure what fields are available, look in your flow history, expand "Get direct reports (V2)", and look in the Outputs section.  Hope this helps.

[Diti]

Unfortunately I've had no luck with this yet. Please let me know if you're able to find a solution.

[Anonymous]

Thank you Jim - This too worked for me.

Unfortunately, I ran into another issue & can't seem to find a fix for it anywhere online - request if you could guide me here as well please.

The Headcount of my organization is 15,000+; The 'Do Until' default limits are set to 60 iterations in Period Time of 1 Hour; however 60 iterations only yield me a very tiny subset of the overall 15,000+ employees. I tried increasing the limits to 200 iterations & set the PT to PT5H. Below are the results I got in both scenarios:

60 Iterations: If I copy-paste the 'Body' field under 'OUTPUTS' (attached screenshot for your reference) in to an Excel Spreadsheet; the data arranges itself into a proper Tabular format where every column is separated & have headers too.

200 Iterations:  This does not display anything under 'OUTPUTS' in the 'Body' field. I'm assuming it's obviously because the size of the data retrieved is much bigger than that at 60 Iterations. But when I do click on 'Click to download' - it opens up another tab in the browser & has long string data which is not getting arranged in Tabular format when copied to Excel spreadsheet.

 

Is there any workaround for this - because setting iterations at 60 (just for the sake of getting structured format data) will not serve the purpose.

Note: At 200 iterations also, I did not get entire data of my Org - it was just few hundreds of employee more than at 60 iterations. I was planning to set the limits to maximum (5000 iterations); However since 200 iterations did not work, there was no point trying to go for any higher limits.

Request you to kindly share your expertise on this please...

[JimSutt1]

@Anonymous I'm sorry, but I don't have an answer for you to cover your entire organization in a single run.  My best suggestion is to reduce the job into multiple runs.  For instance, try running the job from several levels below the leader in your company hierarchy.  That is how I would do it if I wanted to run the flow for my entire company, which has over 100k employees.  

[Anonymous]

@JimSutt1 : No worries, Jim. Thanks once again for all your help on this topic. 

[dawis]

Hi @JimSutt1,
Could you please share this image with higher resolution? I can not see the details. 😞
I am really interested in the scope solutions in your flow, and also about the sidenotes!
Thank you in advance.

[BobJohnston]

Thank you so much, this flow is a major time saver.