cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Shalom47
Helper I
Helper I

Send an email by grouping data from columns

Hi ,

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

 

I have a sql table DataTable :

UserUserEmailSupervisorEmailCompanyReport
Peterpeter@abc.comRock@abc.comAlphaExpenseReport
Peterpeter@abc.comRock@abc.comAlphaTravelReport
Peterpeter@abc.comRock@abc.comBetaExpenseReport
Peterpeter@abc.comRock@abc.comBetaTravelReport
Daviddavid@abc.comRock@abc.comAlphaMealReport
Daviddavid@abc.comRock@abc.comAlphaSalesReport
Daviddavid@abc.comRock@abc.comBetaMealReport
Daviddavid@abc.comRock@abc.comBetaSalesReport
Richardrichard@abc.comJohn@abc.comAlphaExpenseReport
Richardrichard@abc.comJohn@abc.comAlphaTravelReport
Richardrichard@abc.comJohn@abc.comBetaExpenseReport
Richardrichard@abc.comJohn@abc.comBetaTravelReport
Ramram@abc.comJohn@abc.comAlphaMealReport
Ramram@abc.comJohn@abc.comAlphaSalesReport
Ramram@abc.comJohn@abc.comBetaMealReport
Ramram@abc.comJohn@abc.comBetaSalesReport

 

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

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ekarim2020
Super User
Super User

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

ekarim2020_0-1651784107630.png

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

Snag_b51c2c8.png

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:

Snag_aee5440.png

 

Next, we will construct the body of the flow:

Snag_af37df8.png

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

Snag_af1a7a2.png

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

Snag_aff0ad1.png

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

Snag_b00682e.png

 

This action gets the user's Supervisor Email:

Snag_b01bb5b.png

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.

Snag_b1b6cde.png

Snag_b1f4221.png

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:

Snag_b154fce.png

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

Snag_b27fcfe.png

Snag_b1fe065.png

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:

Snag_b263241.png

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

Snag_b329d84.png

Snag_b32d378.png

Here is what each of the emails would look like:

Snag_b35c800.png

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.

View solution in original post

7 REPLIES 7
Pstork1
Dual Super User
Dual Super User

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)



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

Join me at 365 and PWR EduCon in Chicago
EduCon Chicago 2022

ekarim2020
Super User
Super User

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

ekarim2020_0-1651784107630.png

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

Snag_b51c2c8.png

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:

Snag_aee5440.png

 

Next, we will construct the body of the flow:

Snag_af37df8.png

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

Snag_af1a7a2.png

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

Snag_aff0ad1.png

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

Snag_b00682e.png

 

This action gets the user's Supervisor Email:

Snag_b01bb5b.png

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.

Snag_b1b6cde.png

Snag_b1f4221.png

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:

Snag_b154fce.png

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

Snag_b27fcfe.png

Snag_b1fe065.png

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:

Snag_b263241.png

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

Snag_b329d84.png

Snag_b32d378.png

Here is what each of the emails would look like:

Snag_b35c800.png

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
Helper I
Helper I

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
Super User
Super User

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 .

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.

 

Snag_147950ac.png

Snag_1445f2e4.png

Snag_14822162.png

 

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

 

Snag_1470a3c9.png

Snag_144b0fe9.png

Snag_144c44df.png

Snag_144d5fd5.png

Snag_1451c784.png

Snag_1452e400.png

 

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

 

Snag_14552fa6.png

Snag_1458fb92.png

Snag_145a066b.png

Snag_146011a8.png

 

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

 

Snag_14640726.png

Snag_146744eb.png

 

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

 

Snag_146b4313.png

Snag_147cc09d.png

Snag_146f70b7.png

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

@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
Helper I
Helper I

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

Helpful resources

Announcements
 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Users online (4,958)