cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
laz8592
Level: Power Up

Email reminder when a contract is 30 days before expiry

I don't have any experience with workflows or flows. 

 

I would like to create a specific flow that sends automatically an email reminder out to specific users when a sales client contract is due to end 30 days before and must be linked to a column in a document library 'termination date' in sharepoint. The files are scanned into pdf and uploaded one by one on Sharepoint and start and end of contract by date columns are filled in when uploaded. Enterprise metadata is enabled.

 

Is there  a way that once the flow is created and then the day after when the contract ends to move these files to an ‘expired contracts’ folder?

 

How can I easily create this flow please. 

10 REPLIES 10
Bizzo
Level 8

Re: Email reminder when a contract is 30 days before expiry

You kind of have 2 questions in here.

 

I would like to create a specific flow that sends automatically an email reminder out to specific users when a sales client contract is due to end 30 days before and must be linked to a column in a document library 'termination date' in sharepoint.

 

I think your best bet is to create a calculated column called EmailReminderDate. This link will give you a bit of info on how to do that, but I think it'll end up something like this: =[termination date]-30.

https://msdn.microsoft.com/en-us/library/office/bb862071(v=office.14).aspx

 

Create a flow that's triggered to run daily (Recurrence).

 

Create a timestamp for "today" (Data Operations - Compose). You might have to format the timestamp to play nice with the rest of the flow. This thread will give you some info and links about timestamps.

https://powerusers.microsoft.com/t5/Building-Flows/Is-there-any-way-to-do-a-timestamp/m-p/50999#M471...

 

Get items from your SharePoint list, and filter the results using EmailReminderDate equals timestamp.

 

Use the Apply to Each option and send out an email for each result (ie. every contract that expires in 30 days) that is returned.

 

Personally, I would probably add 2 other columns, EmailReminderSent and EmailReminderSent_dt, and update those columns when the flow runs successfully. That gives you a bit of extra visibility into what's happening.

 

 

Is there  a way that once the flow is created and then the day after when the contract ends to move these files to an ‘expired contracts’ folder?

 

Create a flow that's triggered to run daily (Recurrence).

 

Create a timestamp for "today" (Data Operations - Compose).

 

Get items from your SharePoint list, and filter the results using termination date equals timestamp.

 

Use the Apply to Each. It looks like you'll have to copy the file to the expired contracts folder and then delete the record in the original document library.

Laz8592L
Level: Power Up

Re: Email reminder when a contract is 30 days before expiry

Bizzo thank you for this. 

 

As I am clueless with this, is there a possibility you can send me some screenshots with the details you have stated. Have a look at the columns I currently have for the folder library in the attachment.

 

Columns in doc library.jpg 

 

 

Bizzo
Level 8

Re: Email reminder when a contract is 30 days before expiry

Really sorry. Never got an email when you replied (or I missed it). Do you still need a hand with this? Let me know and I can try and put some pieces together.

Bizzo
Level 8

Re: Email reminder when a contract is 30 days before expiry

Here's your document library with columns and some sample data.

 

document-library-expiring-contracts.PNG

 

document-library-expiring-contracts-column-definitions.PNG

 

NOTE: You cannot use a calculated column to filter results, so EmailReminderDate needs to be set. I defaulted the column to [Today]+335, so I think it still does what you need it to. ContractStartDate defaults to Today, and ContractEndDate defaults to [Today]+365.

 

Your trigger is set to run daily.

 

document-library-expiring-contracts-trigger.PNG

 

Get today's date and format it.

 

document-library-expiring-contracts-date.PNG

 

Get all the contracts that need to send the email reminder today (using the output from above as part of the filter).

 

document-library-expiring-contracts-get-files.PNG

 

Add an apply to each - so for every result returned in the Get files step above, we want to do the same thing. We check if the EmailReminderSent column is equal to "No" (we don't want to send the email more than once). If that condition is true (meaning the email has not been sent), send the email. Get a timestamp, then update the record and set EmailReminderSent to "Yes" and set EmailReminderSentDate to the timestamp.

 

document-library-expiring-contracts-apply-to-each.PNG

 

That will pretty much do it I think.

Bizzo
Level 8

Re: Email reminder when a contract is 30 days before expiry

Let me know how it goes when you use that first example I sent. You didn't reply, so I'm going to hold off on doing the second one until I know you're actually still looking for help with that.

 

Also, I left the apply to each stuff for you to do. Those should be easy enough to figure out.

chrislaz8592
Level: Power Up

Re: Email reminder when a contract is 30 days before expiry

Thank you Bizzo will try this next week and let you know. 

Bizzo
Level 8

Re: Email reminder when a contract is 30 days before expiry

Sounds good! Looks like I'm getting alerts for this now, so let me know if you have any questions. It won't take me 2 weeks to reply this time! 🙂

ediscacciati
Level: Power Up

Re: Email reminder when a contract is 30 days before expiry

Does this really do it because I tried and did not for me. I get this =  "status": 400,
  "message": "The expression \"Due Date eq '11/2/2017'\" is not valid.\r\nclientRequestId: 14c8e303-f6d6-432f-8a4b-2d393e165002",

Bizzo
Level 8

Re: Email reminder when a contract is 30 days before expiry

No. This doesn't work at all and I put all this time into purposely answering questions with incorrect information... SMH

</bitter>

 

In the output for that flow where you read the error message, you should be able to check the format for the column, Due Date. Make sure Due Date actually is returned. Compare the format of Due Date with the way we formatted the timestamp. It might be different for some reason. Start there and let me know what you find.

Bizzo
Level 8

Re: Email reminder when a contract is 30 days before expiry

Thought I would quickly touch base with you and see if you'd had a chance to look into this further. Did it all make sense? Are you still trying to do this?

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: 422 members 6,675 guests
Please welcome our newest community members: