Unanswered

Build a flow that sends an email 30 days before a contract expires in a Document Library and checks for early termination dates

(0) Share

Report

Posted on by Denisev

I'm using a workflow in Sharepoint Designer 2013 to send alerts when contracts housed in a Document Library are expiring or considered for early termination (indicated by a checkbox called "evergreening"), I need to recreate this workflow in MS Flow if possible. Please assist, I've reviewed @Pieter_Veenstra and @v-xida-msft comments and would like to know if you can assist. Both your comments seem to be going in the direction considering. Unless you have a better option. 🙂

Scenario:

Sharepoint 2016 Document library with metadata that identifies the document's terminiation date and a check box that identifes the contract as evergreening. I am trying to use Microsoft Flow to send alerts in 2 cases.

Alert 1 send an email 120 days before the document termination date, only if the contact is flagged as evergreening.
Alert 2 send an email 30 days before document termination date.

To consider:

The document termination date may change at times, (contracts always being reviewed), so need a reaccurring flow that will check the dates daily.
Some contracts do not have document termination date as yet. (blank field)
need to filter out contracts that are Archived or Expired

Please help. Thanks in advance.

Categories:

Building flows

I have the same question (0)

All responses (8)

Answers (0)

ScottShearer 25,270 Most Valuable Professional on at

Like (0)

Report

@Denisev :

It won't be difficult to build a Flow that meets your requirements. I would use 2 Get Files Properties Only actions with OData Filters so that only documents that meet your requirements are returned along with a recurrence trigger that fires once a day.

When you say to ignore contracts that are expired or archived, are these statuses in a Choice column? Check boxes? If a choice column, what are the other choices.

I can provide an example if you answer the questions above.

Scott

Was this reply helpful? Yes No
Denisev 8 on at

Like (0)

Report

Thanks for th quick response. I a bit new to Flow, so yes I would like snippets and guidance. Yes, I have checkboxes for both archived and expired contracts.

Was this reply helpful? Yes No
ScottShearer 25,270 Most Valuable Professional on at

Like (0)

Report
@Denisev :

Here is an example.

I have a document library with boolean/checkbox columns for Evergreening, Archived and Expired

I have a date column called Expiration.

Please not the following:

I have a Get Files Properties Only action for both pf your scenarios

I am using a recurrence trigger - configure it to fire once a day

There are single quotes around the expression in my OData Filters. Single quotes are required around anything that is not a number being used as a parameter

You need to use a columns internal name in OData filters. If you column has spaces, the internal name wil not be the same as the display name

You can send your email inside of the for each loops

If you have questions, post here.

If you like my solution, please give it a thumbs up.

If my solution addresses your issue, please mark your post as Solved.

Scott

Was this reply helpful? Yes No
Denisev 8 on at

Like (0)

Report

Thank you. I will try to implement on my test library. Will let you know by tomorrow.

Was this reply helpful? Yes No
Denisev 8 on at

Like (0)

Report

Hi there,
Again, thank you for your help with this. Could you explain the Filter Query with date expression for me. I created the steps as directed however the date expression errors out when testing. I've attached a screen shot of my steps. when I add addDays(utcNow(),30,'yyyy-MM-dd') or addDays(utcNow(),120,'yyyy-MM-dd') to the Get Files step I get the 400 error. Can you first explain what the Get Files Filter Query is supposed to do? thank you again.
Get Files

Get FIles 2

Was this reply helpful? Yes No
ScottShearer 25,270 Most Valuable Professional on at

Like (0)

Report

@Denisev :

You need to replace yes and no in your query with 0 for No and 1 for yes. The 0 and 1 should not have single quotes around them - see my example. I am assuming that the columns you are referencing are Yes/No or Check box columns.

Scott

Was this reply helpful? Yes No
Denisev 8 on at

Like (0)

Report

Hello @ScottShearer
Thanks for the information. I changed the values to 0 and 1 and add the expression I get this error. please advise.
expression added: addDays(utcNow(),30,'yyyy-MM-dd').

I have a termination date field used to determine when the document actually expires. how do I include this filter into this query. Currently I get this error without the termination date.

{
"status": 400,
"message": "The expression \"Move_x0020_to_x0020_Archive eq 0 or Expired_x0020_Contract eq 0 '2019-04-11'\" is not valid.\r\nclientRequestId: 85d4fe76-389b-49d3-9d74-a0aa51280a52\r\nserviceRequestId: ef4ac89e-a03d-8000-4893-57da8826ede3"
}

Was this reply helpful? Yes No
ScottShearer 25,270 Most Valuable Professional on at

Like (0)

Report

@Denisev :

I believe you have a number of issues.

First, your date field is just hanging there -

"Move_x0020_to_x0020_Archive eq 0 or Expired_x0020_Contract eq 0 '2019-04-11

You need to say something like: Termination eq 'addDays(utcNow(),30,'yyyy-MM-dd')' in your expression

In addition, I don't believe you can use parens for the order of operations - I don't think you can have two "or" statements and also an "and" in the same OData expression in Flow. I need to verify this but I am reasonably certain. I am assuming that you'll want an "and" before Termination eq 'addDays(utcNow(),30,'yyyy-MM-dd')'.

Scott

Scott

Was this reply helpful? Yes No