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!

[ChristianAbata]

Hi @JimSutt  you can use do ultil.

In my example I look into json response many time unless the answer is empty

[v-bacao-msft]

Hi @JimSutt ,

Maybe you could consider using Do until to meet your needs.

Please consider sharing the current Flow configuration so that we can try to apply Do until to your Flow.

Best Regards,

[JimSutt]

Hi @ChristianAbata and @v-bacao-msft ,

I used your feedback and reconsidered my approach.  Below is what my process looks like thus far. 

My approach is as follows:

1. Prompt for manager's email
2. Get manager's profile (email, name, job title)
3. Initialize an array and add the manager's profile to first line of array
4. Loop through the array, appending the manager's direct reports to the array.  As the array grows, the intent is the loop will keep evaluating the direct reports of each user, and continue to append the full-time users to the end of the array. 

Currently, the main loop does not continue to process newly added users to the array, only the original.  Is there a way to continue to loop through the array as it grows?

[JimSutt]

Note that I did not use the "Do Until" loop as I don't want the loop to stop when it hit the first user with no direct reports.  I want to keep building the list of direct reports and checking each one for additional reports.  I hope that makes sense.  I wonder if I need to use a different style of loop in order to loop through an array that is expanding.

[tootuff]

I am also trying to accomplish this. Is there any update from Microsoft on a possible solution?

[JimSutt]

Successfully implemented the logic using the Do Until function (see screenshots) and a counter.  It appears the Apply To Each loop only processes the rows of the array present at the START of the loop and ignores rows appended to the array during the loop.  

The Do Until loop does not use the array as an input, but rather compares a simple counter to the size of the array.  Since the array is initialized with 1 row and the counter is initialized at 0, the loop will continue to process the array until the counter catches up to the size of the array, which is re-evaluated each iteration of the loop.  

*Note:  The Get Direct Reports function returns inactive users, so my next course of action (not shown here) will be to ignore those records.  

[mikeyroth]

I would really appreciate some help.  I cannot figure out the variables used in the JSON Parse Content field in your screen shots.    I know I want the data from the items we stored in the array, I am just not sure how to get them.   Any advice?

[JimSutt]

Hello @mikeyroth, the following is the code in the "Content" field of the Parse JSON step:

variables('FinalArray')[variables('ArrayCounter')]

The output of this step will give us the components of the object array for the current row, which we can then reference the "mail" element to pass into "Get direct reports (V2)".  If you need it, the following is the Schema used in the Parse JSON step:

{
    "type": "object",
    "properties": {
        "Mail": {
            "type": "string"
        },
        "DisplayName": {
            "type": "string"
        },
        "JobTitle": {
            "type": "string"
        },
        "AccountEnabled": {
            "type": "boolean"
        }
    }
}

Hopefully this addresses your question.  If you need more help, please let me know.

[mikeyroth]

@JimSutt   Thanks  I got it to run!   I found I had to change the limit of the do until to get all of the reports, but it did work.  Did you ever get where you could filter those accounts that were not enabled?

[JimSutt]

Hi @mikeyroth , I'm glad you were able to get the flow to run.  Good catch with the loop limiter.  In order to filter out the disabled accounts, you will need to include "accountEnabled" field from the "Get direct reports (V2)" step.  After the "Get direct reports (V2)" step, add the "Filter array" step to only keep the records where accountEnabled = true.  You can also update the Select statement within your Do Until loop to save the value of the accountEnabled field (useful for testing to ensure you're only returning the correct values!)  The following is my updated steps immediately after "Get direct reports (V2)": 

The "Filter array" in advanced mode will look like:

@equals(item()?['accountEnabled'], true)

[mikeyroth]

@JimSutt   I thought I had it running, but apparently it wasn't doing what I wanted.  Any idea why it would fail to append the array?   It's interesting, because the item show up in the output of the csv table, but not all items.    I showed a sample above, removed the names and items for privacy.   Please advise what I might be doing wrong.  

And just to clarify, is length(variables('FinalArray')) the right variable for the do Until compare to the array counter?

Thanks so much for your help

[JimSutt]

@mikeyroth my guess is your "Append to array variable" step is outside of one of the two loops.  Your outer loop should be your Do Until loop, which has an input of a specific row of FinalArray.  Within that Do Until loop is an "Apply to each" loop, in which the output of "Get direct reports" is appended to the FinalArray variable. 

--Do Until loop
|
|  Get direct reports
|
|  --Apply to each loop
|  |
|  |  Append to array
|  |
|  --
|
--

Also, make sure that you initialized your counter variable with a value of 0 (zero).  

After you confirm all this, if you're still having the problem, go ahead and add screenshots of your flow, and I'll try to help troubleshoot further.

[JimSutt]

@mikeyroth 

Yes, your formula for the Do Until statement is accurate.  Assuming you're using the same variable names, if you edit the statement in advanced mode it should read:

@equals(variables('ArrayCounter'), length(variables('FinalArray')))

[mikeyroth]

@JimSutt   It looks right to me, but not sure.  I did initialize counter before do until

[JimSutt]

@mikeyroth it looks like you're incrementing your counter within the "Apply to each" loop.  Move it outside of that loop.  It should be the last step of the "Do until" loop.  

[mikeyroth]

@JimSutt   That did the trick.    Thank you.   I also had to add in the Parse JSON    Type:["string","null"] on everything but the email, as I had some with null records in location.   

I sure do appreciate your help!  

[antonyclark]

@mikeyroth  and @JimSutt would one of you guys please OneDrive or other fileshare the final working Flow?

You've done great work figuring out the solution to a widely re-usable solution and I don't want to re-invent the wheel or try to cobble together something from all your screen-shots.

Thank you very much in advance for your hard work on this generalized problem.

[JimSutt]

@antonyclark , I'm not able to save off the flow without identifying information from my company, which I'm not permitted to share.  Is there something in the screenshots that you have questions about?  

[antonyclark]

Thanks for the quick response Jim. I am confused about which of the posts actually details the working solution.

Is it the one marked as Accepted Solution? Could you link to the one post I should follow to completely recreate the Flow from scratch?

Thank you in advance 🙂