Introduction
Once you have data inside your database, one common thing users want are reminders to do something for example when a task is due. In this post we will look how to send an email reminder when a payment is due on a order.
Setting up a single reminder
To set up reminders in Power Automate we first need to to use a scheduled trigger, this means that on a schedule Power Automate will check to see if an order has expired and an email will be sent to the user on the schedule. First of all we need to set the frequency and time the flow will trigger.
Now to get the records as we are using a SharePoint list we can use the ‘Get Items’ actions. Below is an example of the SharePoint list we are using:
In our filter query for the ‘Get Items’ we need to filter where payment has not yet been recieved, we also want to recieve a reminder email 7 days before the due date.
To do this first we need to filter out all the ‘Payments Recieved’ as we don’t want reminders for thoes where payments have been recieved. to do this we can simply filter something like PaymentRecieved ne 1 which will return all values that are ‘No’
Next we need to filter by the date, as we need to find everything that is due in 7 days time we need to use the expression ‘AddDays’. If we just use the below expression, the flow will fail, this is because we are using ‘utcNow’ which uses a Date/ Time value and our SharePoint list is using a date value.
PaymentRecieved ne 1 and PaymentDue eq '@{addDays(utcNow(),7)}'
To solve this we need to convert the date time into the correct format:
PaymentRecieved ne 1 and PaymentDue eq '@{formatdatetime(addDays(utcNow(),7),'yyyy-MM-dd')}'
After we do this we can now see the flow has returned 1 record as expected
If we put the outputs into an email we can see the end result
Reminders For Multiple Records
The problem with doing reminders in an apply to each loop, because Power Automate is looping through each item, if we have more than 1 record expiring today we would get multiple records which means the user would get multiple emails:
However we can get multiple records and alot more efficently especially if we have lots of records by using a ‘Select’ action we can return all the results that match into a HTML table
Finally because we are not using ‘Apply to Each’ anymore we need to introduce a condition to see if the flow found any records to do this we can do a count on the length of the array from SharePoint and see if the amount of items is greater than 0
length(outputs('Get_items')?['body/value'])
