Review dates on documents

[Jonathan-101]

Hello,

I am new to flow and looking to set up a flow to undertake a daily check on documents stored within specific folder on sharepoint, to email any of the documents that are due to be reviewed (i have already set up the review dates on each document. The notification to be 7 days before the document is to be reviewed. Once the document has been reviewed and the review date updated, to then send an email notification the following year.

Im very new to flow and confussed, any step by step guide would be greatly appreciated... 😁

[v-yiwenxie-msft]

Hi @Jonathan-101 

In my understanding, you want flow to check every day for documents in a specific folder in sharepoint and to send email notification 7 days before the review date you set to them.

If I’m not misunderstanding your need, you can try the flow below:

The condition is used to check if the column ‘review date’ is equal to the day which is 7 days after today.

Because the flow use UTC, so you need to convert the UTC to your time zone. Here I use the ‘Pacific Standard Time’ as an example.

The function used above:

formatDateTime(convertFromUtc(addDays(utcNow(),7),'Pacific Standard Time'),'yyyy-MM-dd')

For more info about formatDateTime(), convertFromUtc(), addDays(), utcNow(), please go to the link below:

Reference guide for functions in expressions - Azure Logic Apps | Microsoft Docs

Reference guide for functions in expressions - Azure Logic Apps | Microsoft Docs

Reference guide for functions in expressions - Azure Logic Apps | Microsoft Docs

Reference guide for functions in expressions - Azure Logic Apps | Microsoft Docs

Best Regards,
Community Support Team _ Kira Xie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jonathan-101]

Hello,

thank you for the quick response.

The flow appears to be running, but is not capturing any information from the document library?

I have the site address and Library name correct and have set document review dates too?

[v-yiwenxie-msft]

Hi @Jonathan-101 

Have you set the date and time column 'review dates' in the library?

If you don't set the date and time column 'review dates', then the comparison of the column value with the function won't work.

Could you show me the screenshot of your flow?

Best Regards,
Community Support Team _ Kira Xie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jonathan-101]

Hi yes I have set all the document review dates.

please see attached flow

[v-yiwenxie-msft]

Hi @Jonathan-101 

Have you used the function as below?

formatDateTime(convertFromUtc(addDays(utcNow(),7),'Pacific Standard Time'),'yyyy-MM-dd')

But, if you just change the sequence of these nested functions, that's okay.

Could you tell me how do you know that the flow is not capturing any information from the document library?

And could you take a screenshot of the outputs of your flow?

Best Regards,
Community Support Team _ Kira Xie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jonathan-101]

Hello,

yes i have used the function, changed the PST to Greenwich Mean Time.

I have amended some document review dates to today and I am not receiving any email alerts when run.

please see output.

[Jonathan-101]

In the document library I have the review date to include time - should this be off? do i need a formula in the column options?

[Jonathan-101]

Column settings

[v-yiwenxie-msft]

Hi @Jonathan-101 

This flow will only send email notification when the review date is exactly 7 days after the current time.

Because the condition is 'review date' is equal to ...

If you want the flow to send email notification not exactly 7 days after the current time. Then duplicate email notifications will be sent.

Yes. Could you please disable the column to include time?

Or, if you don't want to disable it, then please use the function formatDateTime() for 'review date' as:

formatDateTime(...,'yyyy-MM-dd')

Best Regards,
Community Support Team _ Kira Xie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jonathan-101]

Hello!

I ahve changed the folder review date to remove time and updated the condition to 'is less than or equal to'.

My documents within the folders now only show a date and I have set the dates to 7 days or less - to run and test the flow - there is still no email notification received. I am now receiving the following error;

Unable to process template language expressions for action 'Condition' at line '1' and column '15037': 'The template language function 'lessOrEquals' expects two parameter of matching types. The function was invoked with values of type 'Null' and 'String' that do not match.'.

[v-yiwenxie-msft]

Hi @Jonathan-101 

The error indicates that there is no value in the column 'review dates' or the function returns nothing. While the function will return string, so I guess the column value is empty. Could you check if their is an empty column value?

Best Regards,
Community Support Team _ Kira Xie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jonathan-101]

Sorry I do not understand?

Within the documents folders, there are some documents without set review dates, not all documents are to be reviewed. Does this require all the documents to have a review date?

[v-yiwenxie-msft]

Hi @Jonathan-101 

The 'condition' only supports integer or string. 

If some documents do not content review dates, then please add one more condition as:

empty(...) is equal to false

Best Regards,
Community Support Team _ Kira Xie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jonathan-101]

Sorry, could you help with the coding - does that all go in the first 'empty' box? and what are all the *****

[v-yiwenxie-msft]

Hi @Jonathan-101 

Here are the two functions:

formatDateTime(convertFromUtc(addDays(utcNow(),7),'Pacific Standard Time'),'yyyy-MM-dd')

empty(items('Apply_to_each')?['reviewdate'])

Sorry, please just ignore all the *****. I just want it to separate the two functions.

Best Regards,
Community Support Team _ Kira Xie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jonathan-101]

Hello,

sorry - another issues flagging up

Unable to process template language expressions for action 'Condition' at line '1' and column '15246': 'The template language function 'lessOrEquals' expects two parameter of matching types. The function was invoked with values of type 'Null' and 'String' that do not match.'.

[v-yiwenxie-msft]

Hi @Jonathan-101 

As I have said before,

the error indicates that there is no value in the column 'review dates' or the function returns nothing. While the function will return string, so I guess the column value is empty. Because a value of nothing cannot be compared with a string type value, so your flow gets error.

The 'condition' only supports integer or string. 

If some documents do not contain review dates, then please add one more condition as:

empty(...) is equal to false

The two functions in this condition are:

formatDateTime(convertFromUtc(addDays(utcNow(),7),'Pacific Standard Time'),'yyyy-MM-dd')

empty(items('Apply_to_each')?['reviewdate'])

Best Regards,
Community Support Team _ Kira Xie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jonathan-101]

Hello,

i understand that, thank you but the error below still flags up when ran

Unable to process template language expressions for action 'Condition' at line '1' and column '15245': 'In the template language function 'convertFromUtc', the value provided for the time zone id 'Greenwich Mean Time' was not valid. '.

[Jonathan-101]

Hello,

I have changed the data input to the same as your suggestion, without changing the time zone and still get the following error.

Unable to process template language expressions for action 'Condition' at line '1' and column '15246': 'The template language function 'lessOrEquals' expects two parameter of matching types. The function was invoked with values of type 'Null' and 'String' that do not match.'.