Mapping Employees to their Managers

[zerow08]

I have a reminder flow set up which sends reminders to the respective managers of the employees that their Leave request is apporaching. I was running a For each loop and for each employee I was sending a separate email to the manager.

What I need to do is , combine all the emails of each Manager's Employees and send them as 1 email.

Example:

 X is the manager of a,b,c;

Y is the manager of p,q,r;

The travel requests of a,b,q,r are approaching

X gets ONE combined email of a,b and Y gets one combined email of q,r;

The for each loop i am running is clubbing a,b,q,r together . However I need to put a,b in a separate cluster and q,r in a separate cluster. I need to use a map to do this , but how can i do so?

[RobElliott · Accepted solution]

@zerow08 You can do this by following the flow below which I've done in the classic designer as I still prefer it. The SharePoint list shows the staff who are going on leave and we'll just send the email each day if the start date is less than or equal to 5 days from today.  So that will be Rob and Michael (for manager Jonathan) and Kevin and the first leave by Daren (for manager Neill).

The overall flow looks like this:

Flow

1. The trigger is a recurrence schedule and runs each day at 6am:

2. Next, add a Get items action to get back just those items where the start of the leave is within the next 5 days. So the Filter Query is StartDate ge 'utcNow()' and StartDate le 'addDays(utcNow(),5)'

3. Next add a Select. In the left field I've just yped in UniqueManager and in the right field selected Manager from the dynamic content box.

Next, add a Compose and for the Inputs field enter the following expression union(body('Select'),body('Select')). Union with the 2 selects gets just the unique managers.

4. Next, add an apply to each and inside it add a filter array. For the From field select value from the get items section of the dynamic content box. Then select Manager is equal to items('Apply_to_each')?['UniqueManager']

5. Next add a create html table action, select Body from the Filter array section of the dynamic content box then select custom columns. In the left field I've typed Staff Member and the right field is an expression item()['Staff']  and the other one StartDate and the expression item()['StartDate']

 

6. The default html table action has no styling so I've added a compose with with the CSS styles in it followed at the end by the outputs of the create html table action. The CSS is in the sppoiler below:

Spoiler

<style>
table {
border: 1px solid #1C6EA4;
background-color: #EEEEEE;
width: 80%;
text-align: left;
border-collapse: collapse;
}
table td, table th {
border: 1px solid #AAAAAA;
padding: 3px 2px;
}
table tbody td {
font-size: 13px;
}
table thead {
background: #1C6EA4;
border-bottom: 2px solid #444444;
}
table thead th {
font-size: 15px;
font-weight: bold;
color: #FFFFFF;
border-left: 2px solid #D0E4F5;
}
table thead th:first-child {
border-left: none;
}
</style>

7. Finally, add the send an email action and for the To field enter the expression item()['Manager']

A single email is sent to each manager but for this example I've just sent them to myself.

Rob
Los Gallardos
Principal Consultant, SharePoint, Forms and Power Platform, WSP Global (and classic 1967 Morris Traveller driver)

[zerow08]

Thanks a lot for this. Sorry I couldn't reply back earlier. This seems like the solution I needed. Really appreciate the detailed answer. Thanks again

[zerow08]

Hey just a small query. My manager column is a lookup column in which I have entered the email ID. I am also filtering by email ID only. When I am trying to use Manager is equal to items()['Apply_to_each']?['UniqueManager'] it giving an empty output for whatever reason. Dont know why this is happening