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.
