Flow to Automatically Send a Reminder Email 90 Days Before Expiration Date in SharePoint

[seanthomasadams]

Friends,

I am stuck and would love your assistance.  I have a SharePoint list with approximately 2200 customer records.  I would like to automatically send a reminder email 90 days prior to the expiration date.  Thank you in advance of your response.

Context:

• I have a calculated column in the SharePoint list called "Countdown" that demonstrates the number of days remaining in the current contract.
• I have a Yes/No field called "Turn Auto-Emails OFF" to determine whether or not to send auto-emails.
• I have another Yes/No field called "Sent 90 Day Letter?" to indicate if the 90 day letter was sent.

General Process (conditions):

1. (Recurrance) Once a day I would like the flow to examine my SharePoint list (2187 records)
2. (Condition 1) If an item's "Countdown" field is equal to 90
3. (Condition 2) And if the item's "Turn Auto-Emails OFF" field is false (default)
4. (Condition 3) And if the item's "Sent 90 Day Letter?" field is false (default)
5. Send an email to the customer
6. Update that item's SharePoint record "Sent 90 Day Letter?" to true (Yes)

Testing:

1. Added Recurrance of 1 day
2. Added a Get Items and pointed it to the SharePoint list, turned pagination on to 5000, added a Timeout duration to P1D (which I understand allows up to 1 day before timing out) 
3. Added an Apply to Each, pointed to the output to "Value"
4. Added a simple test condition to determine if the previous steps were working
5. Added an Send Email step to test

The results are that I keep timing out after 8 minutes on the Apply to Each step.  No emails are sent, although more than a handful of the records should have qualified.  Any help is appreciated! 

Gratefully,

Sean

[ScottShearer]

@seanthomasadams:

SharePoint calculated columns are only updated when a record is edited - they are not dynamically changed.  As a result, you can't use the calculated "countdown" column to reflect the number of days.  The timeout should not be necessary.

My suggestion is to use an OData filter on your get items action - filter the results so that only those records that meet your criteria are returned.  You'll need to do a date calculation for this to happen - I usually use and initialize a variable for this purpose in order to make my Flows easier to read.

If you need an example, please let me know.

Scott

[seanthomasadams]

Scott,

Thank you for your prompt reply.  I would love to see an example, as I have not used OData filters before. 

Sean

[ScottShearer · Accepted solution]

@seanthomasadams:

Here is an example of a Flow that runs successfully.

Please note that I purposely put spaces in my column names (not a best practice) to make a point.  Flow references the SharePoint internal column names which may be different that the display name (the name you see in the form).  If there is a space in the name, the column name has changed or a variety of different circumstances, the display name will be different than the internal name.  To find the internal column name, go to the list settings and click on a column.  In the URL, you see something like this at the end:

&Field=Turn_x0020_Auto_x002d_Emails_x00

Everything after Field= is the internal column name - you'll use this in the OData filter.  My example was for the "Turn Auto-Emails OFF" in my list.  The internal name in your list may be different.

Here is the expression that I used when initializing my variable:

formatDateTime(addDays(utcNow(),90),'yyyy-MM-dd')

This expression adds 90 days to today and formats the date in a way that matches the way SharePoint stores the date.

Also, I am sending the email to the person who created the item.  If you have another person or group column in your list, you could reference it for the email address.

Finally, please note the single quotes in my OData filter around the values I am looking for - i.e. 'false' and around the variable.

If this addresses your issue, please mark your post as solved.

Scott

[seanthomasadams]

Scott,

You are my hero.  Thank you.

Sean

[ScottShearer]

@seanthomasadams:

I believe that we had the opportunity to discuss this in person and you asked about setting permissions on a folder.  I mentioned that you would have to make a web service call to accomplish that goal.  I should have mentioned when we spoke that there is a premium (i.e. you have to pay) connector from Plumsail that allows you to do do a lot with permissions in SharePoint.  Take a look at the Plumsail web site before using this connector.  Please send me a private message should you have additional questions.

Scott

[Anonymous]

Thank you for sharing. I was looking for a way to filter a list with ODATA to send a reminder 3 days after the item's Request Status date and with a Status of Pending. Worked great!  

[Anonymous]

@ScottShearerI am trying to replicate the flow but I am getting a random error message relating to the filter query.

Does this look like it should be working - 'varDate×' and Reminderdate eq 'false'@{variables('varDate')}' and Reminderdate eq 'false' 

The column I want to track is Reminderdate and I have entered in your varDate string but the error I get when it executes is this - which I am not sure where it is picking it up from? - 

Column 'Expiration' does not exist. It may have been deleted by another user.
clientRequestId: 09ff2e0c-6dac-4d24-b4ab-d9e5512d0fc4
serviceRequestId: 09ff2e0c-6dac-4d24-b4ab-d9e5512d0fc4

[TshepoErn]

Great. Isn't there a way to create a function that counts down the number of days remaining then embed it in the notification email?
E.g. A flow that will send you this email today, "Microsoft Power Bi Pro license expires in 90 days." . It should keep counting down until the 90 days elapse or until the license has been renewed. For instance, it should send "Microsoft Power Bi Pro license expires in 89 days." the following day.