cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ChadVKealey
Level 10

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 Team
Community Support Team

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

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 Team
Community Support Team

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

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

ChadVKealey
Level 10

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

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
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,880)