I’m often asked about reminder functionality for things like controlled documents and contract management. Microsoft added basic reminder functionality by providing a template solution for Power Automate. What if you need something a little more advanced? The answer is to build a custom reminder Flow.
In this example I provide tips on how to build a custom workflow and show an example of the reminder workflow in action. The Flow runs on a schedule, checking a document library for documents that meet a criteria specified in a Filter, that is dynamically driven of a calculated date.
As I mention in the video, there are two common use cases where this Flow can be used:
- Sending notifications a few days before the a review or expiry date. This is often a requirement in both controlled document systems and contract management.
- Monitoring a process workflow to remind people when something needs action and is waiting. e.g. I document is submitted for review but the review hasn’t occurred in x days
The flow runs as a Scheduled Flow. You can specify the frequency from every few minutes if you like, but I would recommend once a day to avoid hitting limits of the numbers of Flow runs per month!
In the video I use an AddDays() function, to subtract 3 days from the current date using the New Zealand timezone. UtcNow() returns the current UTC date and time. Here is the expression code:
addDays(convertFromUtc(utcNow(),'New Zealand Standard Time'),-3)Next, I create a filter value using the output of the ‘GetDateToCheck’ expression with the date code above. In the example code below I am using two operators. Here are some useful ones:
- eq = equals
- ne = not equals
- ge = great than or equal
- le = less than or equal
(Priority eq 'High') and (Created ge datetime'@{outputs('GetDateToCheck')}')Here is the Flow from the video:
You can extend this functionality further, to meet your specific needs, but I hope this has given you a good idea of the capabilities. of Power Automate scheduled Flows for reminders.
SharePoint lightbulb moments 
Hi Steve, this is really useful but you have renamed the steps so not to sure what to add – can you help please?
Hi Mike, GetDateToCheck and FitlerValue are both Compose actions. I hope this helps 🙂
Hi. Thanks for this which is really helpful. Only issue I’m having is my date to compare for renewal is End Date, which I can’t reference in the filter. I’ve looked in library settings and got the internal name which is _EndDate and used that in the filter value but it always errors. I’ve used the Created date successfully but that’s not what I need in this case. I’ve tried _x0020_ also to no avail. Any help greatly appreciated! Thanks.
Sorry, managed to fix it! Typical after submitting a question lol! Thanks again.
Glad to hear you fixed it 🙂
Hi Steve,
I have a similiar flow set up but when it comes to the Send an Email action – if I use Created By or Modified By in the “To” field the flow works fine. But if I choose the Document Owner Email in the “To” field the Send to email action gets nested in a second Apply to each and the flow won’t work.
I cannot figure out how to get around this.
Please help a fellow kiwi!
Thanks
Hi Della, the Apply to Each occurs if the ‘Document Owner Email’ field allows multiple values. Check the field settings on the List in SharePoint, check to see if ‘Allow Multiple Values’ is enabled.
Give that a try and let me know how you get on 🙂
Hey Steve,
I get this error when trying to filter based on two columns;
The expression “(Approval Status eq ‘Approved’) and (Review Date ge datetime’2022-10-07T21:16:22.9487828Z’)” is not valid. Creating query failed.
clientRequestId: 917c4277-21c2-4bb9-b488-843a96f3c694
serviceRequestId: 917c4277-21c2-4bb9-b488-843a96f3c694
Any help would be appreciated :I
best,
rob
Hi Rob, I notice a space in the expression for Approval Status, try using the internal field name. You can get this by going into List Settings and then clicking the column name and checking the URL (last parameter)
Hi Steve,
I really liked the idea of this method vs. what I was originally planning, but I’m stuck. I’m not sure if it is because I am using Get Items (I have a sharepoint list as opposed to a document library) instead of Get Files, or if it has to do with the query in my first function. Instead of your column with Priority, I have a column for the specific team. And then a column in the list called review date that generates a date 1 year in the future from its active date. I copied and pasted your exact formula with the new Zealand date because the time it sends doesn’t really matter…but could that be the issue?
The expression “(Trupanion Team eq ‘Market’) and (Review Date ge datetime’ addDays(convertFromUtc(utcNow(),’ New Zealand Standard Time ‘),-3)’)” is not valid. Creating query failed.
clientRequestId: fea5a9fb-04f2-4ef8-9082-7434a8accda7
serviceRequestId: fea5a9fb-04f2-4ef8-9082-7434a8accda7
Hi Steve,
I really liked the idea of this method vs. what I was originally planning, but I’m stuck. I’m not sure if it is because I am using Get Items (I have a sharepoint list as opposed to a document library) instead of Get Files, or if it has to do with the query in my first function. Instead of your column with Priority, I have a column for the specific team. And then a column in the list called review date that generates a date 1 year in the future from its active date.
The expression “(Trupanion Team eq ‘Market’) and (Review Date ge datetime’2022-10-28T20:34:29.9860973Z’)” is not valid. Creating query failed.
clientRequestId: 8c21930c-6494-4e88-bb4e-73099d09633d
serviceRequestId: 8c21930c-6494-4e88-bb4e-73099d09633d
Best,
Christina
Hi Christina, I noticed the is a space in the column name. Try putting it in quotes like this ‘Turpanion Team’
I did give that a try and it did not work. Got the reply below:
The expression “(‘Trupanion Team’ eq ‘Market’) and (Review Date ge datetime’2022-10-30T19:19:38.1942591Z’)” is not valid. Creating query failed.
clientRequestId: 9b724795-2132-4d8c-a52a-f0ec05a51980
serviceRequestId: 9b724795-2132-4d8c-a52a-f0ec05a51980
Two more things to check. It could be the Internal Field name is different to the display name. This link explains how to find the internal name https://sharepointstuff.com/2022/02/09/find-internal-column-name/
It could also be due to the date format. Is the timezone US?
OK! I think you were spot on with the Internal field name. After a few edits and trying out things I did get a NEW error message…so it feels like progress. The new error message has also taught me something new. I was using a calculated field to generate a review date 365 days in the future from the Active date. The new Error details are below:
The field ‘Review_x0020_Date’ of type ‘Calculated’ cannot be used in the query filter expression.
clientRequestId: 95452891-b725-4446-afe0-4ebc4328ae2a
serviceRequestId: 95452891-b725-4446-afe0-4ebc4328ae2a
I’m wondering if I can do away with review date field all together from my list and through power automate generate a reminder based off of my “Active Date” field 365 days in the future. The active date is not a calculated field but is an actual Date and Time field. Also, yes I am in the US so I know that may matter. I’ve tested with your formula exactly as you wrote it and tried making edits for my time zone, but never made it past the edit with the internal field.
Hi Steve. After your last message I was able to figure it out. Thank you so much for your help!
Well done Christina, glad you solved the problem!
Hi Steve, I got your code to work, but if I change the To: to “Owner Email” (Where Owner is a custom column for people selection.) instead of “Created by Email” it fails. An additional “Select an output from previous steps” appears and is pre-filled with “Current Item”.
Thanks MC
Any idea how to get it to work with the modification I made?