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
Solved! Go to 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.
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)
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.
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,
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 .
I answer questions on the forum for 2-3 hours every Thursday!
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
@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
User | Count |
---|---|
92 | |
45 | |
21 | |
18 | |
16 |
User | Count |
---|---|
136 | |
49 | |
42 | |
36 | |
28 |