Collect direct reports list and display ALL results

[nmn_lamba]

Hello everyone,

I am facing challenge with showing list of records from SharePoint list based on the direct reports which is captured through:

ClearCollect(
             myTeam,
             {Result: " "},
             Filter(
                     Office365Users.DirectReports(User().Email),
                     AccountEnabled = true
             )
)

This works fine, it shows the list of employees of a manager and when selected through dropdown also shows results from the SharePoint list. However, I am not able to show ALL filtered results from SharePoint list with this code, so a manager basically has to select each employee to see what they submitted.

Please can someone advise what formula/code this collection needs to make a manager's life easy.

Thanks in advance.

[v-albai-msft]

Hi @nmn_lamba ,

What do you mean about “when selected through dropdown also shows results from the SharePoint list”? What is the relationship between dropdown and myTeam collection?

What is the formula on your dropdown? “myTeam.XXX”?

Do you want to display the results in a gallery? And now you have some display issue in this gallery?

Can you show me a screenshot of your SP list?

Please give more information about your scenario for helping us understand your requirement better.

Best regards,

Allen 

[nmn_lamba]

Hi @v-albai-msft ,

1. SharePoint list has a column which stores users email address in a single line text format (called Submitter).

2. Office365Users connector helps map those submitter's email with the manager.

3. The filtered results of the SharePoint are shown in a gallery.

4. The items property of dropdown contains only 'myTeam' keyword, which is basically the list of items stored in collection.

The code on gallery is:

SortByColumns(
    If(
        !IsBlank(ddDirectReports.Selected.Result),
        Filter(
            AllItems,
            Submitter = varUser.myProfile.userPrincipalName && Submitter <> varUser.myProfile.userPrincipalName
        ),
        Filter(
            AllItems,
            Submitter = ddDirectReports.Selected.UserPrincipalName && 
            SubmissionDate >= FromDate.SelectedDate && 
            SubmissionDate <= ToDate.SelectedDate
        )
    ),
    "ID",
    Descending
)

I would usually put Result: "All", however, that doesn't seem to show any result, so I kept it blank to make more sense, if no results are going to display.

ClearCollect(
             myTeam,
             {Result: "All"},
             Filter(
                     Office365Users.DirectReports(User().Email),
                     AccountEnabled = true
             )
)

 
I hope this information helps?

Thanks!

[v-albai-msft · Accepted solution]

Hi @nmn_lamba ,

Seems that your dropdown is set to "myTeam.UserPrincipalName", since you used this in your formula as a filter condition. 

If yes, I need to know why you use !IsBlank(ddDirectReports.Selected.Result) as a condition, because ddDirectReports.Selected.Result should prompt error message, the .Result is not a valid value from dropdown. 

Also, I need to know why you use "Submitter = varUser.myProfile.userPrincipalName && Submitter <> varUser.myProfile.userPrincipalName" ? Do you want to return whole list?

Based on current description, I did a test on my side, filter based on the dropdown. When select "All", display whole list, and when select a specified user, display records related to him.

I set my dropdown to myTeam.UserPrincipalName, so I edit the ClearCollect function to below({UserPrincipalName:"All"}):

ClearCollect(
             myTeam,
             {UserPrincipalName: "All"},
             Filter(
                     Office365Users.DirectReports(User().Email),
                     AccountEnabled = true
             )
)

So that my dropdown has four results:

For my gallery, I use this:

If(
    Dropdown2.Selected.UserPrincipalName = "All",
    list7,
    Filter(
        list7,
        Dropdown2.Selected.UserPrincipalName = Submitter
    )
)

On your side, if AllItems is your list name, the code should be:

SortByColumns(
    If(
        ddDirectReports.Selected.UserPrincipalName= "All",
            AllItems,
        Filter(
            AllItems,
            Submitter = ddDirectReports.Selected.UserPrincipalName && 
            SubmissionDate >= FromDate.SelectedDate && 
            SubmissionDate <= ToDate.SelectedDate
        )
    ),
    "ID",
    Descending
)

Best regards,

Allen

[nmn_lamba]

Thank you @v-albai-msft , I think we are getting close. Just to add some more information.
I have 1000+ records in the SharePoint list and there could be 20+ managers who will have x/y/z number of direct reports.
Its good to know that .Result is not a valid value from dropdown, so I changed to UserPrincipleName as suggested.

We just need to work on the True part of IF condition. Since, this is a manager's personal view of his/her team.

Using this function displays all the items of the list:
ddDirectReports.Selected.UserPrincipalName= "All", AllItems,

This must have some filter condition, that when ALL is selected, the manager sees Cumulative submissions of his/her direct reports coming from the SharePoint list.

Taking example from your screenshot, on selecting ALL should display results of anna/clover23/clover1 only.

If there is anna23 reporting to another manager, that should not show here.

Please let me know if that's doable?

Thanks for all your assistance with this 🙂

[v-albai-msft · Accepted solution]

Hi @nmn_lamba ,

Yes, this can be done.

Continue with the previous steps, add another collection to keep all the users managed by each manager(not including "All"):

ClearCollect(var1,Filter(
                     Office365Users.DirectReports(User().Email),
                     AccountEnabled = true
             ).UserPrincipalName)

This var1 collection is a one-column table:

Then use this formula when select "All":

ddDirectReports.Selected.UserPrincipalName= "All", Filter(AllItems,Submitter in var1),

Check results this time😊

Best regards,

Allen

[nmn_lamba]

Thank you @v-albai-msft , super solution, managers are happy to see cumulative results 🙂
The final request on this, promise (hope its not too much).

1. Can our collection var1 work for Directors/VPs who are 2-3 level above managers can see the results of their entire reporting chain, instead of just seeing their direct reports when they access the app?

2. Please help in modifying my code to add another filter, which is called ddFilterStatus and has 'All' and some other values coming from SharePoint choices column called 'Status'. Usually I use With function to accommodate multiple filters but this doesn't seem to work here.

Here's my final code for the items in gallery.

SortByColumns(
    If(
        ddDirectReports.Selected.UserPrincipalName = "All",
        Filter(
            AllItems,
            Submitter in var1 && SubmissionDate >= FromDate.SelectedDate && SubmissionDate <= ToDate.SelectedDate
        ),
        Filter(
            AllItems,
            Submitter = ddDirectReports.Selected.UserPrincipalName && SubmissionDate >= FromDate.SelectedDate && SubmissionDate <= ToDate.SelectedDate
        )
    ),
    "ItemID",
    Descending
)

[v-albai-msft]

Hi @nmn_lamba ,

1. It may be tricky but can be done. See my above example, I have three direct reports, if clover1 has 2 direct reports, clover23 has 1 direct reports, I can use ForAll to get 6 results.

Since we have get var1, then we need to use ForAll function to get all direct reports of each user in var1. Create a new collection using below(because the first record of my results is blank, so I remove it. Whether or not to use the remove function depends on your situation 😞

ClearCollect(var2,
Ungroup(
ForAll(var1,
If(!IsEmpty(Office365Users.DirectReports(UserPrincipalName)),Filter(Office365Users.DirectReports(UserPrincipalName),AccountEnabled= true).UserPrincipalName)),"Value"));
Remove(var2,First(var2))

Then combine var1 and var2 into one collection. Using this:

ClearCollect(varAll,var1,var2)

Using formula like above to get all reports for users who are 3/4 level above current managers.

2. Where do you want to add the filters?

Usually, we can use"," to add more condition, formula like this(assume both your ddFilterStatus and Status are Choice column):

Filter(listname, ddFilterStatus .Value="All" , Status.Value="XXX")

Best regards,

Allen 

[nmn_lamba]

Hey @v-albai-msft , I got the filters working and the collection shared above does the job for 2 Levels. Though for some reason it showed a lots of blank rows and was not giving the correct count. So, I thought to keep it simple and did something like this:

ClearCollect(
    var1,
    Filter(
        Office365Users.DirectReports(User().Email),
        AccountEnabled = true
    ).UserPrincipalName
);
Clear(var2);
ForAll(
    var1,
    Collect(
        var2,
        Office365Users.DirectReports(UserPrincipalName)
    )
);
Clear(var3);
ForAll(
    var2,
    Collect(
        var3,
        Office365Users.DirectReports(UserPrincipalName)
    )
);
ClearCollect(varAll,var1,var2,var3)

I have learnt that if I need to do a VP level, it is almost 5/6 levels up. Though this way it does help collect the results, but it gets very slow during var3 collection, if I add var4/5/6, it won't be able to load the data in good amount of time!.
I am happy to mark this post as resolved now, though if you have any tips to make it work fast please do share.
At last, a Level 3 manager may not be interested in seeing Level 0 employees on the dropdown. Is it possible to hide Level 0 and on selection of Level 2 or Level 1 manager, gives their cumulative count as well?

Again, thank you very much for all your assistance, really appreciated!

[v-albai-msft]

Hi @nmn_lamba ,

That is why i mentioned " It may be tricky", since ForAll always take some time to complete, especially in production environments with a large number of users. The only way I can think of is to periodically save the results of collections such as VarAll to a data source like an SP list, and then dropdown can refer directly to that SP list. 

For your second question, for a level 3 manager, you can try to add a condition to judge the selected user's level then create another collection called "VarLevel3All ", like:

If(Office365Users.UserProfile(User().Email).JobTitle="Level3",
ClearCollect(var1,...);
ClearCollect(var2,...);
Var(VarLevel3All,var1,var2)
)

Then the dropdown should be:

If(Office365Users.UserProfile(User().Email).JobTitle="Level3",
VarLevel3All.UserPrincipalName,
varAll.UserPrincipalName
)

To get the count of selected user's reports(level 2 or level1), you need create a new collection, formula like this:

ClearCollect(
    var1,
    Filter(
        Office365Users.DirectReports(Dropdown1.Selected.UserPrincipalName),
        AccountEnabled = true
    ).UserPrincipalName
);
Clear(var2);
ForAll(
    var1,
    Collect(
        var2,
        Office365Users.DirectReports(UserPrincipalName)
    )
);
ClearCollect(varSelectedAll,var1,var2)

At last, add a label beside dropdown, set its Text to:

CountRows(varSelectedAll)

You can also set its Visible to:

If(Office365Users.UserProfile(Dropdown1.Selected.UserPrincipalName).JobTitle="Level2"||Office365Users.UserProfile(Dropdown1.Selected.UserPrincipalName).JobTitle="Level1",true,false)

Best regards,

Allen