In lists and libraries that have date columns, there is a way to set up an out-of-box Microsoft Flow, to send reminders!
This is a list of projects. I’ve created a few different date columns in my list, such as Start Date, Scheduled Completion Date, and Actual Completion Date. I can set up a Flow to be based off of one or more of these dates. You need to make sure that your date column is showing in the default view of the list.
- In this example, I choose Scheduled Completion Date.
- This panel pops up on the right:
Click Create.
When you take a look at flow.microsoft.com, you’ll see a new Flow in your list of My Flows, called Get an email reminder. One of the first things you may want to do, is rename it to indicate which site/list it is running on.
What does it do, exactly? Let’s break it down. There’s a lot to learn in here, and some cool functionality that you might want to know about for other Flows you may build.
Note that by default, this Flow is only for you, the person who created it. To make this workflow send its reminders to people other than you, it will need some tweaking.
- Initialize a variable called TableRows
This is going to be used later, for putting together the list of items in the email that gets sent. - Composes a variable that contains the number of days that you selected in step 2 when you first created the Flow.
You can change this, the number of days before, that you want to be alerted. - Gets your user profile.
Your name is mentioned in the email that gets sent at the very end of the Flow. - Calculates Today plus the number of “Days to remind me in”.
By default, when using today, utcnow(), it actually uses UTC time. So if you want this to be relative to your own time zone, you may want to tweak it. I’m in central time, which is 6 hours behind UTC, so I’ll need to subtract 6 hours. My new formula is:
addDays(addHours(utcnow(),-6), int(outputs(‘Days_to_remind_me_in’)), ‘yyyy-MM-dd’) - This takes the date that is the output from the previous step, and adds one day to it.
With this, when the workflow runs, it can look at any date value that falls between those two date/time values. - This gets the name of the list that the Flow runs on.
When you first created this Flow, the GUID of the list was passed into the Flow, which you can see here in the Uri. - Gets the items from that specific SharePoint list, filtered by that specific date range. concat(concat(‘Scheduled_x0020_Completion_x0020′,’ ge ‘, ””,outputs(‘Today_+_x_days’)),””,’ and ‘, concat(‘Scheduled_x0020_Completion_x0020′,’ lt ‘,””, outputs(‘x_Days_+_1’),””))
My date column that I picked at the beginning of this post, was called Scheduled Completion Date. Basically this formula is a filter query, saying that the scheduled completion is greater than or equal to ‘Today + 1 day’, and the scheduled completion is less than that first date plus one. So, if today is 1/21, it will return all items that have a scheduled completion between 1/22 at 12AM, and 1/23 at 12AM.
If you want to add any other filters, like the status is not equal to completed, you would have to use this CAML query syntax and add it to the expression. - If the query above returns an empty list of stuff.
As in, there are no query results. - If it IS empty, (yes), the workflow does nothing. If it is NOT empty, than it does the following with the item or items that it returns. It appends to a string variable.
This means that it creates a pretty, formatted table row for each item, which includes the name of the item with a link to it. - This sends an email to me, the person who created the Flow. It has a bunch of HTML formatting and also includes the pretty table rows from the previous step, and the email looks like this:
If you would like this Flow to send email to more people, then don’t use the Send me an email notification action. Use one of the Outlook email actions, such as Send an email. With that, you could send it to any email address.
So, to sum it up, the main things you’ll probably want to adjust in this Flow, are #11, WHO it is sent to, #1, when it gets sent, and #8, what the filter is. Also, you may want to SHARE this Flow, so that other people can collaborate on it with you. It’s not imperative, though.