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
Power Automate News & Announcements
Keep up to date with current events and community announcements in the Power Automate community.
Community Calls Conversations
A great place where you can stay up to date with community calls and interact with the speakers.
