Introduction
Do you want to send email notifications based on a user column? Does the flow send multiple emails to one person because they exist more than once in a SharePoint list or Dataverse Table? We will show you how to create a power automate flow that gets unique users from a table and will send an email to that user once. We will also show all details for the selected records in a single email.
The SharePoint List Set-up
In this scenario we have a SharePoint list with a list of orders, we want to send an email notification for thoes orders are assigned to an user on a reminder ever week. However a user could have 1 or 100 orders assigned to them so we don’t want users to receive 100 email notifications for each separate record they have been assigned to we only want to send 1 email to that user where the order has not been closed.
The SharePoint List
The flow will run on a schedule to find all records where Status = “Active” and will send email to each unique email address. Below in our SharePoint list you can see Joe Bloggs appears 6 time and Adam Smith appears 3 times
SharePoint Lists Data
Starting The Flow
Firstly we need to grab all the data we want to retrieve in my case I want to get all items where Status = ‘Active’
This will return all the data but I only want to retrieve the ‘Assigned To’ value. On the Outputs from the ‘Get items’ we just need to find out what the column name is:
Getting the Unique Email Address
Using a select action we can then just retrieve the Assigned To column
On the outputs of the ‘Select’ action we can see the email address from the ‘Assigned To’ column
However this is appearing multiple times we can use the expression ‘union’ to get unique values from an array
@{union(body('Select'),body('Select'))}
The outputs of the compose action now only shows Joe Bloggs and Adam Smith once:
Getting The Records For The Unique User
Now that we have the list of users that we want to send emails to we have to use the get items again to only get the items this time where the ‘Assigned To’ matches the ‘User’ from our ‘Compose’. Since we are getting multiple users we also want to make sure this is inside an ‘Apply to each’ using the ‘union’ expression we created earlier
Now we want to show all the records where AssignedTo = User in a table. One way to do this is with a Append to string variable, however if we have lots of records this can take a lot longer as it needs to loop through each record. A quicker way to do this is with another ‘Select’ action.
The HTML Table
