cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
seanthomasadams
Level: Powered On

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

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) 2.png
  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! 

1.png

 

Gratefully,

Sean

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

@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.

 

90Day1.jpg90Day2.jpg

 

 

If this addresses your issue, please mark your post as solved.
 
Scott
 
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott

View solution in original post

6 REPLIES 6
Super User
Super User

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

@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

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott
seanthomasadams
Level: Powered On

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

Scott,

 

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

 

Sean

Super User
Super User

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

@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.

 

90Day1.jpg90Day2.jpg

 

 

If this addresses your issue, please mark your post as solved.
 
Scott
 
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott

View solution in original post

seanthomasadams
Level: Powered On

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

Scott,

 

You are my hero.  Thank you.

 

Sean

Super User
Super User

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

@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

 

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott
Bakertm
Level: Powered On

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

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!  

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 174 members 4,994 guests
Please welcome our newest community members: