Power Platform Community Forum Thread Details

Okay, a bit of a complex scenario, but hopefully I can explain it clearly. I will also post a picture of the larger flow.
*Help with building the original flow courtesy of @creativeopinion *

I have a flow built that pulls from a SharePoint List.
The list is, for all intents and purposes, an employee roster that tracks when specific certifications are expiring.

Here is how the current functional flow works (a synopsis, not step-by-step) :

The flow checks the expiration date for each cert against a date 2 months from now (I.e. it expires in 2 months).
It takes all items from the list that meet these criteria, pulls the Safety Manager's Name and Email from the person column, then filter's the results by Safety Manager and prepares an email to be sent out to each S. Manager which outlines what employees of theirs have an expiring certification and what it is.

That's sort of the mass over-view; here is my problem...
As in every business, there is a Safety Manager and there is a Direct Supervisor (or Line Manager).
The flow was originally built only to send the email to the Safety Manager, but I would now like to include the Line Manager.
I made a few small tweaks (and almost got it working) but it failed stress testing.
It worked well when only returning only ONE result per Safety Manager; however, if there is more than one result and the two employees have DIFFERENT Line Managers, it begins duplicating and sending the email numerous times.
I will try to demonstrate the email result using a table.
Data Set (retrieved from list)

Safety Manager A	Safety Manager B	Safety Manager B
Line Manager A	Line Manager B	Line Manager C
Employee A	Employee B	Employee C

The returned emails will properly format Safety Manager A's information.
It will however duplicate Safety Manager B's information FOR EACH Line Manager.
Returned in Emails

Safety Manager A	Safety Manager B	Safety Manager B	Safety Manager B	Safety Manager B
Line Manager A	Line Manager B	Line Manager B	Line Manager C	Line Manager C
Employee A	Employee B	Employee C	Employee B	Employee C

Here's my assessment of the problem:
Because the Line Manager's information is held within a person Column on the S.P. List, it makes it more difficult to pull the information needed from the list (into the flow) while keeping the data set in a cohesive string so as make it usable (with the other data).
I other words, I tried to use a Compose to pull the L. Manager information into the flow (from SP), but it was not part of the working data-set (which has been filtered and converted to HTML Data sets at this point) in the flow.

So, what I ended up doing was including it in my original Select function to get the S. Manager's information. But this has caused a problem, because now when it tries to compile the data it creates duplicate S. Manager data points which cause duplication errors later in the flow (as shown in the above table and in the opening of the problem).

So, here is where I am at now...
After this all failed, I then thought that perhaps I could create the Item()?[' '] from the working Data Set (which flows out of the Filter Array).

In other words, if I were to use a Compose action, perhaps I could grab the data output from the filter array. This would hopefully keep the correct Employee and L. Manager together, but I could not figure out how to do so.
The problem has more to do with the person column inside of the string. The data is nested inside of the greater column information. This may not be a problem to some, but I am a bit out of my depth when it comes to coding.
So, to explain, this is what I mean by nested, there is the Bracket{} which opens the entire string from the filter array;
then, Line Manager{ has its own bracketed data set (as shown below). I am not sure of how to use Compose{ Item()?['xxxxxxx'] } to pick these lines ['DisplayName'] and ['Email'] out of the Data Set.

This lack of knowledge is exactly why I tried to pull the data in using the Select.
*Note: other names, email, and company info have been changed in this string before posting, I'm sure you understand why.*

 "LineManager": {
 "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
 "Claims": "i:0#.f|membership|PHolder@EMAIL.com",
 "DisplayName": "Place Holder",
 "Email": "PHolder@EMAIL.com",
 "Picture": "https://Company.sharepoint.com/teams/HSENALand/_layouts/15/UserPhoto.aspx?Size=L&AccountName=PHolder@EMAIL:.com",
 "Department": "Quality Health and Safety (QHSSE)",
 "JobTitle": "Manager, Operations Compliance"
 },
 "LineManager#Claims": "i:0#.f|membership|PHolder@EMAIL.com",
 "HSEManager": {
 "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
 "Claims": "i:0#.f|membership|tmalonson@SomeCompany.com",
 "DisplayName": "Taylor Malonson",
 "Email": "TMalonson@SomeCompany.com",
 "Picture": "https://Company.sharepoint.com/teams/HSENALand/_layouts/15/UserPhoto.aspx?Size=L&AccountName=TMalonson@SomeCompany.com",
 "Department": "Operations Personnel",
 "JobTitle": "Office Administrator"
 },
 "HSEManager#Claims": "i:0#.f|membership|tmalonson@SomeCompany.com",

Sorry for the long post, it just required a lot of explanation to get here.
My ASK: could someone please tell me how to use Compose{ Item()?[' '] } to pull the information from this nested data set. I think this would solve my problem, as I could simply Compose Items, then assign a variable inside of the ForEach (with a duplicity of 1) used to formulate the Employee Information that flows into the Email.
If you don't think this would solve my problem, please, give me some greater insight.

Flow Pictures:

Categories:

Business Process Flows

I have managed to solve my own problem once again.
Okay, so here is the solve, hope it can help someone else.
I found out how to grab the details from the outputs('FilterArray') using
Items('Apply_to_each')['LineManager']?['DisplayName'] & Items('Apply_to_each')['LineManager']?['Email']

From there, I appended them to the pre-established String Variables.
*Note: make sure for appending emails to place a semicolon and space after the outputs so that they work inside of the Send Email Action.*

Then, once out of that ForEach Loop, I re-composed those Variables (not sure if this is totally necessary, but it is how the person originally helping me build the flow showed me, and I don't want to try and fix what isn't broke).

Then I re-set variables to null so that they wouldn't carry over to the next iteration or Safety Manager's Email.
Then I simply inserted the Dynamic Contents of the last compositions into the Send Email Action.

Community site session details

Adding to a functional Flow: attempting to sort by a second manager's name, then include them on the sent Email

Helpful resources

News and Announcements

Quick Links

Paul Stork – Community Spotlight

Congratulations to the June Top 10 Community Leaders!

Announcing the Engage with the Community forum!

Leaderboard > Power Automate

Featured topics

Product updates