cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kudo Kingpin
Kudo Kingpin

Build a collection of user profiles from O365 users AND a SharePoint List

I'm working on a Leave Request App and one of the requirements is a screen where a manager will be able to view Leave Events submitted by their direct reports. This is actually not that difficult to do because I can use this:

ClearCollect(
    col_MyReports,
    Office365Users.DirectReportsV2(User().Email)
);

to use the O365 Users Connection and pull those subordinates into the collection (col_MyReports). Then, I can filter the list of requests to items where the Requestor is in that collection.

 

However, one of the other requirements is to allow for "alternate approvers". For example, a manager will be on extended leave and they need to designate someone else to approve their direct reports' leave while they are out. To accomplish this, there is SharePoint list ("Approval Exceptions") which has a column for the Requestor and the Approver. The Requestor <-> Approver list is a one-to-one relationship, so there may be more than one entry where the current user is the Approver. What I need to do is get the same type of data (the User Profile?) for each Requestor in that Exceptions list where the current user is the Approver.

 

Working from the inside out, I (think I) need to first filter the Exceptions list based on the current user's Claims (stored in a variable populated with this formula: "i:0#.f|membership|" & User().Email) being in the Claims property of the Approver field (Approver.Claims). I then need to get the email (Requestor.Email) of those users to use as the argument for the "Office365Users.UserProfile" function. This is what I've tried, which has not worked (although I like to think I'm close):

Collect(
    col_MyReports,
    Office365Users.UserProfileV2(       
        Filter(
        col_ApproverExcept,
        var_CurrUserClaims in 'Leave Approver'.Claims).Requestor))

I may be trying to do this in the wrong order or missing some step or argument. Like, is this a place where a ForAll is needed to "cycle" through the Exceptions list? Any help or guidance would be greatly appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Hi @ChadVKealey ,

Based on the formula that you mentioned, I think there is something wrong with it.

The Filter(col_ApproverExcept, var_CurrUserClaims in 'Leave Approver'.Claims).Requestor formula returns a Table value, but the argument of the Office365Users.UserProfileV2() function is required to provide a Text value.

 

Based on the needs that you mentioned, I think the ForAll function could achieve your needs. I have made a test on my side, please consider modify your formula as below:

 

Collect(
         col_MyReports,
         ForAll(
                Filter(col_ApproverExcept, var_CurrUserClaims in 'Leave Approver'.Claims).Requestor,
Office365Users.UserProfile(Requestor.Email) /* <-- Please use Office365Users.UserProfile() rather than Office365Users.UserProfileV2() function */ ) )

please take a try with above formula, check if the issue is solved.

 

Note: The Office365Users.UserProfileV2() formula would return different data structure with Office365Users.DirectReportsV2() formula.

 

More details about ForAll function, please check the following article:

ForAll function

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Community Support
Community Support

Hi @ChadVKealey ,

Based on the formula that you mentioned, I think there is something wrong with it.

The Filter(col_ApproverExcept, var_CurrUserClaims in 'Leave Approver'.Claims).Requestor formula returns a Table value, but the argument of the Office365Users.UserProfileV2() function is required to provide a Text value.

 

Based on the needs that you mentioned, I think the ForAll function could achieve your needs. I have made a test on my side, please consider modify your formula as below:

 

Collect(
         col_MyReports,
         ForAll(
                Filter(col_ApproverExcept, var_CurrUserClaims in 'Leave Approver'.Claims).Requestor,
Office365Users.UserProfile(Requestor.Email) /* <-- Please use Office365Users.UserProfile() rather than Office365Users.UserProfileV2() function */ ) )

please take a try with above formula, check if the issue is solved.

 

Note: The Office365Users.UserProfileV2() formula would return different data structure with Office365Users.DirectReportsV2() formula.

 

More details about ForAll function, please check the following article:

ForAll function

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Thank you @v-xida-msft ! I had to make a few other tweaks to get it working the way I wanted, but that formula was the key. Here's the final code that I got to do the job:

ClearCollect(
    col_MyStaff,
    Office365Users.DirectReports(var_CurrUserUPN)
);
Collect(col_MyStaff,
    ForAll(
        Filter(
            col_ApproverExcept,
            var_CurrUserClaims in 'Leave Approver'.Claims
        ).Requestor,
        Office365Users.UserProfile(Requestor.Email))
);

Initially, I was using Office365Users.DirectReportsV2 for the first part, but there is a discrepancy between the output of the original and V2 versions of (apparently all) these Office365Users actions/functions. However, this raises some other questions:

  • When the original versions are deprecated (as I assume they will, eventually), will this App (or this function in it) stop working?
  • Will I need to find a way to do this using the V2 versions of these actions/functions?
  • If so, shouldn't I do that now (to avoid an unexpected failure down the road)?

Thanks again for your help so far and hopefully you can provide some authoritative answers to the above questions.

Helpful resources

Announcements
News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

Power Apps Community Call

Power Apps Community Call- January

Check out the Power Apps Community Call for January 2021

PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

Top Solution Authors
Top Kudoed Authors
Users online (9,065)