Send an email by grouping data from columns

[Shalom47]

Hi ,

I am new to power automate, I am trying to send a scheduled email as below:

I have a sql table DataTable :

User	UserEmail	SupervisorEmail	Company	Report
Peter	peter@abc.com	Rock@abc.com	Alpha	ExpenseReport
Peter	peter@abc.com	Rock@abc.com	Alpha	TravelReport
Peter	peter@abc.com	Rock@abc.com	Beta	ExpenseReport
Peter	peter@abc.com	Rock@abc.com	Beta	TravelReport
David	david@abc.com	Rock@abc.com	Alpha	MealReport
David	david@abc.com	Rock@abc.com	Alpha	SalesReport
David	david@abc.com	Rock@abc.com	Beta	MealReport
David	david@abc.com	Rock@abc.com	Beta	SalesReport
Richard	richard@abc.com	John@abc.com	Alpha	ExpenseReport
Richard	richard@abc.com	John@abc.com	Alpha	TravelReport
Richard	richard@abc.com	John@abc.com	Beta	ExpenseReport
Richard	richard@abc.com	John@abc.com	Beta	TravelReport
Ram	ram@abc.com	John@abc.com	Alpha	MealReport
Ram	ram@abc.com	John@abc.com	Alpha	SalesReport
Ram	ram@abc.com	John@abc.com	Beta	MealReport
Ram	ram@abc.com	John@abc.com	Beta	SalesReport

Note: The set of reports names are same for each company.

I want to send an email to each user's supervisor as following:

 Example : for Rock@abc.com The email will be like this

User: Peter

Company: Alpha,Beta

Report: ExpenseReport,TravelReport

User: David

Company: Alpha,Beta

Report: MealReport,SalesReport,

similarly another email for another supervisor as well, Could u kindly help me in acheiving this

[Pstork1]

I just finished a Blog post dealing with this very topic.  Here's the post with a full walkthrough example.

Send One Email per Person in a Mixed List – What Me Pa..Panic? (dontpapanic.com)

[ekarim2020 · Accepted solution]

For the demo flow I am reading the data from an Excel file. You just need to replace the data with your DataTable

I start by selecting all the users, then remove the duplicates:

We can use the following expression to remove duplicate users:

union(body('Select_Users'),body('Select_Users'))

For example, based on the data table, the Select Users and Compose UniqueUsers actions produce the following outputs:

Next, we will construct the body of the flow:

 

Create an Apply to Each loop, so we can loop through each of the users stored in the Compose UniqueUsers action:

For each user, get the user's record (rows) from the table using the Filter Array action. Note the expression item()?['User'] below:

For example, the Fitler array action will return the following dataset for user Peter: 

This action gets the user's Supervisor Email:

We just need to examine the first record (item 0 of the array) in the filter array action to get the supervisor email address.  This is the expression used:

body('Filter_array_UserRecord')?[0]?['SupervisorEmail']

For example, with user Peter we would get Rock@abc.com as the supervisor email address.

I've used a scope block to group the report actions together.

 

The expression for the Compose UniqueReports is:

union(body('Select_Report'),body('Select_Report'))

For example, the actions to select the reports without duplicates for user Peter would be:

I've used a scope block to group the company actions together:

The expression for the Compose UniqueCompany is:

union(body('Select_Company'), body('Select_Company'))

For example, the output of the actions to select companies without duplicates for user Peter would be:

Finally I add an action to simulate the sending of the email to the supervisor:

Here is what each of the emails would look like:

 

Hope this helps.

Ellis
____________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.

 

[Shalom47]

Hi @ekarim2020 , Would it be possible to send the below content in a single email to manager, if so could u please guide me how to achieve

To: Rock@abc.com

Body:

User: Peter

Company: Alpha,Beta

Report: ExpenseReport,TravelReport

User: David

Company: Alpha,Beta

Report: MealReport,SalesReport,

[Rhiassuring]

Wow, that was a very detailed and well-crafted response that @ekarim2020 made for @Shalom47 ! That must've taken time to put together, nicely done, @ekarim2020 .

[ekarim2020]

The flow will need to be modified in order to send a single email to the supervisor. The current flow is user-centric (we are looping through each user record). We need to change this to be supervisor/manager centric so we can loop through each supervisor/manager, selecting users who report to the supervisor/manager, and along with the user records.

 

item()?['SupervisorEmail']
union(body('Select_Supervisors'),body('Select_Supervisors'))

union(body('Select_Users'),body('Select_Users'))

 

union(body('Select_Report'), body('Select_Report'))

union(body('Select_Company'), body('Select_Company'))

Here is the text compose SendMail action from the runtime output:

Supervisor : Rock@abc.com
User: Peter
Company: Alpha,Beta
Report: ExpenseReport,TravelReport
-----------------------------------------------------------------
User: David
Company: Alpha,Beta
Report: MealReport,SalesReport
-----------------------------------------------------------------

and

Supervisor : John@abc.com
User: Richard
Company: Alpha,Beta
Report: ExpenseReport,TravelReport
-----------------------------------------------------------------
User: Ram
Company: Alpha,Beta
Report: MealReport,SalesReport
-----------------------------------------------------------------

Hope this helps.

Ellis

[ekarim2020]

@Rhiassuring Thanks for the compliment! The community forum also helps me learn and practice other skills, such as trying different ways to help explain and clarify Power Automate, using different screen capture and editing tools, and developing some training slides for when I'm working with Citizen developers at work. 

Ellis

[Shalom47]

Thank you so much @ekarim2020 😊 . You are amazing!