Create a Flow to notify a person to review a Document one year after the Published Date

[DS2]

I have a Date Field in a SharePoint library called "Published Date." We'd like a message to be sent via Flow 1 year after this Published Date to (so if the Published Date was February 11, 2018, the message to review would be sent on February 11, 2019) a person to review the document. How best would we start?

[ScottShearer · Accepted solution]

@DS2:

The best way to approach this issue would be to start with a recurrence trigger - run the Flow once a day.

I would then use a time action to find the date 12 months in the past.

Use an Get Files - Properties Only action with an OData filter so that only the files you want are returned.

The expression in my OData filter looks like this:

formatDateTime(body('Get_past_time'),'yyyy-MM-dd')

Finally loop through then and send the email.

Below is an example that runs successfully.

Please note that the OData filter uses a columns internal name.  If you have a space in the Published Date column, it will probably look just like my example.  Also note the single quotes in the OData filter.

If this requires more explanation, please let me know.

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

Scott

[DS2]

Thank you, this is so close! I ran into a bit of trouble with the Published column in the OData Filter, though. It does not show up in Dynamic Content (although the site and library showed with no issue). It has no spaces in it, so I did place it in as Published eq' and then the time\date expression. When I run the Flow, it shows as a "bad expression" on that OData Filter Step. 

[ScottShearer]

@DS2

Can you tell me if the Published column is a calculated column?  Is it a column that you added to the library or was it added by SharePoint?

Also, please double check that you are using the columns internal name.  Go to library settings and click on the column name.  You'll see the internal name at the end of the URL after "field=".

Scott

[DS2]

Scott, thank you for your help. 

It is a Date\Time Field and not calculated - as it is named "Publish" only, there are no special characters within. I think it was added to the library and not by SharePoint. However neither it or any other columns show in the dynamic content (although the site did and upon choosing the site, the document library came available). 

[ScottShearer]

@DS2

Sorry, one more question.....

When you are looking at Dynamic properties, do you see the word "more" by the action name?  If so, click on it and let me know if you see the column.

Scott

[DS2]

Hi,

When I click into the Filter Query, the only Dynamic content that comes up is Past Time and I don't see a more:

[ScottShearer]

@DS2

You need to put a single quote after the expression in the OData filter.   See my example.

So, there should be a space after eq then a single quote followed by your expression and ending with a single quote.  Give it a try and let me know if it works for you.

Scott

[DS2]

Fabulous, thanks so much for your help - it is working great. 

[Joao_Oliveira]

Hey, everybody!

Thank you very much for your clarifications, but I ask you for help in a flow that I want to build that is very similar to yours. 

The documents that are uploaded into a sharepoint have several editing/validation states until they reach the "Pronto a publicar no site" state:

What I want is to set up a stream that only reminds me after 6 months that the documents that have the status "Pronto a publicar no site" need to be reviewed.

I built the flow according to your explanations but you are returning an error in the ODATA filter: 

The expression of the date is this: formatDateTime (body ('Obter_tempo_passado'), 'aaaa-MM-dd')

@ScottShearer 

[ScottShearer]

@Joao_Oliveira 

I believe that you are seeing the error because you need to put single quotes around the formatDateTime  expression.  Unless the parameter is a number or a constant like null, true, etc., you need the single quotes.

[Joao_Oliveira]

@ScottShearer 

Thank you for your answer.

However, it still gives error even with the single quotes in formatdate.

What is happening is that I don't know how to write the expression. I want only documents that are in the RevisionValidation column with the state "Pronto a publicar no site" to be reviewed after 6 months.

How do I write this expression?

[Star_D]

@Joao_Oliveira , did you ever find a solution?
@ScottShearer , I used single quotes, and in peek code it does show my expression as formatDateTime(body('Get_past_time'),'yyyy-MM-dd')

but I still get an error in Flow Checker telling me to "Correct to include a valid reference to 'Get_past_time' for the input parameter(s) of action 'Get_files_(properties_only)'. "