Power Automate Solution: Due Date Reminders
Use Power Automate filter queries to get tasks from Dataverse, parse out distinct Assigned To values, build an HTML table of tasks for each user, and send them an email reminder
Scenario
I have been working on an application with a client which allows users to create Work Orders and track progress. A Work Order is a request to complete a task, such as fix a broken sink or order hang a picture. Users submit a Work Order request and then an administrator reviews it, provides a due date, and chooses the user who should complete it. Since we know people are regularly juggling multiple tasks and other priorities, we decided to send users and email reminder three days before any incomplete tasks are due.
Getting Work Orders with an approaching due date
Within the Dataverse Work Orders table, we first created the following fields:
- Due Date: date only field
- Assigned To: lookup field (Users)
- Assigned To Email: text field calculated column that pulls Primary Email from Assigned To (this will reduce the complexity of our Power Automate flow)
- Assignment Status: text field, updated to Pending once the request is assigned
We will use a Power Automate flow triggered on a schedule (every morning). The filter query in a List Rows will allow us to pull incomplete requests (on status) where due date is in 3 days. We will calculate 3 days from now using a formula
formatDateTime(addDays(utcNow(), 3), ‘yyyy-MM-dd’)
and then plug that into the comparison for due date. Don’t forget to use the field schema names in the filter query. My requests table lives in a solution created by a publisher with “cca” as a prefix; yours will likely be different.
Sending Reminders Logic
Now that we have the Work Orders, we need to make sure the reminders get to the right people. The most straightforward way to send the emails would be to loop through each row returned by the List Rows action, get Assigned To Primary Email from Users, and send them an email. However, in my data set I noticed some users had multiple tasks due in 3 days. Everyone already gets so many emails so I figured we could enhance this feature by sending users a single email with all upcoming tasks. Therefore, I planned on
- Getting all Assinged To Emails (returned from the values output of Get Work Orders due in 3 days)
- Finding the distinct email values, and for each getting the tasks assigned to them, generating an HTML table listing each upcoming tasks, and sending an email with the content
To save you an error handling headache, one thing I’ll point out before we dive into the flow configuration is that we will only be running this logic if there are 1 or more rows returned from Get Work Orders due in 3 days. There are several ways to do this, but my preference is to get the length of the List Rows action body and compare to 0:
length(body(‘Get_Work_Orders_due_in_3_days’)?[‘value’])
The rest of the actions should live in the Yes branch of the condition. The first thing we wanted to do was get all Assigned To emails, so that we will be able to find a unique set of emails. We’ll use the Select action to do this. The From property is the value of the List Rows action and we’ll map the Assigned To Email text field.
We can get the unique set of emails from the output of the Select action using the Union function within a Compose action. Both parameters should point to the body of the Select action.
union(body(‘Select_Assigned_To’),body(‘Select_Assigned_To’))
Now we have a list that contains one instance of each email from the List Rows action so we’re ready to filter the array for each of the distinct emails. First, we need a for each loop which takes the Outputs of the Distinct Emails Compose action. Then we’ll use a Filter Array action on the values of the List Rows action and compare Assigned To Email to the current item, which is each of the distinct emails.
The outputs of the Filter Array action will be a list of one or more tasks assigned to the current email within the current iteration of the for each loop. We could use a nested loop to iterate through each task and build an HTML table, but if you don’t have extremely specific style requirements it’s much quicker to use the Create HTML table action. The From property will be the Body of the Filter array action. You can add whatever Headers and Values make sense for your use case.
I do like to add some basic styling to the table, so I frequently reference this blog post by Ryan Maclean to copy the CSS. We can simply throw that in a compose action and then add the Output of the Create HTML table after it. This will create a combination of CSS and HTML.
Since the Send an email action supports HTML content, we can use the outputs of the compose action that contains the HTML and CSS for the body (this is the Outputs below). I also included a formula to show what the date will be in 3 days for clarity:
formatDateTime(addDays(utcNow(), 3), ‘MM-dd-yyyy’)
After saving and testing your flow, you should be able to let it run on its normal schedule and rest assured your users will get the reminder email containing their to do list. Here is a complete outline of each action we walked through. Let me know if you have any questions in the comments!
0 Comments