Accepted Solutions
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.
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.
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
Helpful resources
Join us for Microsoft Power Platform Conference
The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.
June User Group Leader Call
Join us on June 28 for our monthly User Group leader call!
Register for a Free Workshop
Learn to digitize and optimize business processes and connect all your applications to share data in real time.
Help make Flow Design easier
Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!
