cancel
Showing results for 
Search instead for 
Did you mean: 
ScottShearer

Creating a List Reminder Flow

 

I have many requirements over the years to send email reminders based on date fields in SharePoint lists.  One way of accomplishing this task when using a SharePoint Designer workflow was to use a series of "wait until" actions.  This technique is not well suited for Flow - a Flow will not "run" for more than 30 days.  Any Flow that is still running after 30 days will be cancelled.  In many cases that I have encountered, the reminder will need to be sent more than 30 days after an item has been created or modified.

 

If you have been creating SharePoint Designer workflows and are now using Flow, you'll find that Flow often requires you to think differently about how you approach issues - this is no exception.  On way of attacking this issue in Flow is by running a Flow on a schedule which looks for items in your list that meet the conditions for sending a reminder email.  When it finds them, send the appropriate email.

 

Here is my scenario - you have a SharePoint list where your company keeps track of service contracts on equipment used in the business.  At 30 days before the contract expiration, you want to send an email to the POC in your company reminding them that the contract is expiring.  One week before expiration, you want to send a second more urgent email to the POC.

 

My SharePoint list contains the following columns:

  • Title
  • POC (Person or Group column)
  • Expiration (Date Only)

Now for the Flow...

 

My Flow starts with a Recurrence trigger that runs once a day.   The Flow starts and stops once a day and is not subject to the 30 day limitation on Flow durations. 

 

Next, I retrieve items from my Contracts list but I don't want to retrieve all the items - just the ones that meet my criteria.  Technically, I could retrieve all items in the list and use a condition to see if an email should be sent.  One big reason not to do this is that if you have a large list, Get Items may not return all of your list items.

 

To filter what I that I get back from Get Items, I used an OData filter.  More information on OData filters can be found here.

 

My Odata filter looks like this:

RemBlog1.jpg

 

 

Translated, it means that I only want items where the expiration date is 30 days from today.  Also, please note the date formatting (the last paramter).  The reason for the formating is so that the dates I am using are in the same format.  Unless they are formatted in the same manner, my filter condition will never be true.  The format I am using matches the SharePoint date format.

 

Once I retrive the items, I loop through them sending each a reminder email.

 

I finish out my Flow with another Get items action and another send email action.  The primary difference between the two Get Items actionsis that I now only want items that expire 7 days out.

 

RemBlog4.jpg

 

 

Here is a screen shot of my Flow - start to finish:

 

 

RemBlog2.jpg

RemBlog3.jpg 

 

Thats it!

 

 

 

 

 

 

 

Comments

Hi Scott,

 

Thanks for this post! I have a question: will this Flow always send a reminder?

Let's suppose that the Expiration Date column has been changed (extended) in the meantime, will it still send the reminder? Or will it stop the workflow?

 

I am looking for something similar. But I want the e-mail only to be sent if the value of the column hasn't been changed in the meantime?

 

Thanks again 🙂

Anonymous

Thank you for your post, it's very helpful, hve you had any success with creating an email with a consolidated list of items from a SharePoint list based on the filter? For example, I need to send one email with all the items that are ocming due for an owner, I tried the create HTML table action but it did not give me the desired result so I was looking for a solution, thank you!

@Anonymous
You ask a very good question - creating a Flow that sends a single email to an individual with all items that pertain the them is not as simple as it might seem.
The steps are:
1) Use Get Items and an OData filter to retrieve a filtered list of items
2) Generate an array of unique email addresses to which emails will be sent
3) Loop through the array and filter the list items so that only the items for an individual are left
4) Inside the loop, create an HTML table and insert it in an email

The syntax required for some of the above is not intuitive.    I did create a working Flow that meets your requirements.  I decided to create a blog post rather than provide it here because of the level of detail required.

Scott
 
 
 

@Anonymous

 

Here is a link to a post that does what I believe you are looking for.

 

I have a similar solution that is not quite as complicated which I will be posting soon as a blog.

 

Scott

Anonymous

Thank you so much @ScottShearer for being so helpful and answering my question, I absolutely look forward to the blog but I was able to resolve one of the issues I had by using the filter query you suggested! Thank you again!

@ScottShearer thanks for your guidance on this also.  I've followed your instructions and I am getting the message "the expression 12-13-2019 is not valid...".  Any idea why I'm getting that message.  The formatting is the same as on my SharePoint list though I am working with a PowerApps form that works fine. 

Thanks so much!  

 

Issue with Date.jpgMessage Bad Date.jpgSharePoint List Date Format.JPG

@CindyZ 

It looks like you forgot to add the rest of the filter query - you just have the expression.  The query should look something like the screen shot below.  Be sure to include the single quotes around the expression.

Filter.jpg

Thanks so much, Scott!  You are right -- that was exactly the problem! 

HI, Thank you for this blog post. I've completed all the steps, but I'm still getting an error. The SOW End Date column in my list is a date selection in the SharePoint list. I'm not sure why it keeps saying the expression is not vaild

CK_MS_0-1597854268412.png

CK_MS_1-1597854321488.png

 

 

Hey, I know this is a shot in the dark, as this is an older blog post, but this is exactly what I am looking to do with a flow, but I keep getting a fail when I test run it.

 

I am thinking it has to do with the Get Items portion.  I am very new with this, so just tried copying your formula and adjusting where needed, but I am still having no luck. 

 

Nick

About the Author
  • Experienced Consultant with a demonstrated history of working in the information technology and services industry. Skilled in Office 365, Azure, SharePoint Online, PowerShell, Nintex, K2, SharePoint Designer workflow automation, PowerApps, Microsoft Flow, PowerShell, Active Directory, Operating Systems, Networking, and JavaScript. Strong consulting professional with a Bachelor of Engineering (B.E.) focused in Information Technology from Mumbai University.
  • I am a Microsoft Business Applications MVP and a Senior Manager at EY. I am a technology enthusiast and problem solver. I work/speak/blog/Vlog on Microsoft technology, including Office 365, Power Apps, Power Automate, SharePoint, and Teams Etc. I am helping global clients on Power Platform adoption and empowering them with Power Platform possibilities, capabilities, and easiness. I am a leader of the Houston Power Platform User Group and Power Automate community superuser. I love traveling , exploring new places, and meeting people from different cultures.
  • Read more about me and my achievements at: https://ganeshsanapblogs.wordpress.com/about MCT | SharePoint, Microsoft 365 and Power Platform Consultant | Contributor on SharePoint StackExchange, MSFT Techcommunity
  • Encodian Owner / Founder - Ex Microsoft Consulting Services - Architect / Developer - 20 years in SharePoint - PowerPlatform Fan
  • Founder of SKILLFUL SARDINE, a company focused on productivity and the Power Platform. You can find me on LinkedIn: https://linkedin.com/in/manueltgomes and twitter http://twitter.com/manueltgomes. I also write at https://www.manueltgomes.com, so if you want some Power Automate, SharePoint or Power Apps content I'm your guy 🙂
  • I am the Owner/Principal Architect at Don't Pa..Panic Consulting. I've been working in the information technology industry for over 30 years, and have played key roles in several enterprise SharePoint architectural design review, Intranet deployment, application development, and migration projects. I've been a Microsoft Most Valuable Professional (MVP) 15 consecutive years and am also a Microsoft Certified SharePoint Masters (MCSM) since 2013.
  • Big fan of Power Platform technologies and implemented many solutions.
  • Passionate #Programmer #SharePoint #SPFx #M365 #Power Platform| Microsoft MVP | SharePoint StackOverflow, Github, PnP contributor
  • Web site – https://kamdaryash.wordpress.com Youtube channel - https://www.youtube.com/channel/UCM149rFkLNgerSvgDVeYTZQ/