[SOLVED] Sharepoint reminder alert based on columns date and days

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hi,

I'm trying to use Microsoft Flow to send me alerts to review/update document files in Sharepoint.

In my sharepoint I create two new columns: Effective_Date (date) and Expiry_Days (number).

I create a flow with "Get files (properties only)" to get the files in the folders. I can get the folders and files.

How can I create a filter that only returns files if: Effective_Date = todayDate - Expiry_Days?

For two days that I'm trying to configure this filter.

Thanks in advance.

Categories:

Using flows

I have the same question (0)

All responses (5)

Answers (1)

Sort by

v-yamao-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @Anonymous,

To filter files that EffectiveDate = Today-ExpiryDays, please check the following flow for a reference.

According to your description, I created a library with the two columns, EffectiveDate (Date) and ExpiryDays (Number).

For testing, it is triggered by a Button.

Add the action Get files from the library.

Add a Condition, use the following code:

@equals(items('Apply_to_each')?['EffectiveDate'], formatDateTime(getPastTime(int(items('Apply_to_each')?['ExpiryDays']), 'Day'), 'yyyy-MM-dd'))

Under if yes branch, return the file name that matched the condition.

A screenshot for your reference:

More details about getPastTime function, please check it at here:

https://docs.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#getPastTime

Best regards,

Mabel

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi,
Thank you for your help.
With your suggestion, I'm getting this error:
InvalidTemplate. Unable to process template language expressions for action 'Condition' at line '1' and column '2509': 'The template language function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'.

Do you get this error?

I'm thinking, if this is there a better way to implement this idea?
Is possible to get a list?
My goal is to email a list with all documents to review.
Thanks.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi all, I realize why I get the error. I have folders, and the column Effective_Date in the folders is null. How can I move to the next item if null?

Was this reply helpful? Yes No
Verified answer

Community Power Pla... Microsoft Employee on at

Like (2)

Report
Copy link

Link copied!
Hi all,
I managed to find a solution to my problem. See the steps bellow.
In my sharepoint I created two new columns. The Effective_Date is the date of the last review, and the Expiry_Days is the number of days that the document expire.
Here's my flow:
1. Every day at 18h, the flow runs.
2. Get files from the document library.
3. For each value, apply condition 1.
Because I have folders, I have to check it it's a folder or file, because the columns in folders have null values and I get errors.
Condition:
@equals(items('Apply_to_each')?['{IsFolder}'], False)
If yes (file) - continue.
If no (folder) - does nothing.

4. In condition 2, I check if the Effective_date <= Today - Expiry_Days, meaning that the document is expired.
Condition:
@lessOrEquals(string(items('Apply_to_each')?['Effective_Date']), string(formatDateTime(getPastTime(int(items('Apply_to_each')?['Expiry_Days']), 'Day'), 'yyyy-MM-dd')))
If yes: send email.
If no: does nothing.

I used this site to understand all the functions used in the conditions.
https://docs.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#string-functions
It took me several days, but I'm glad I got it.
Hope this help someone.
Thanks for all the help.

Was this reply helpful? Yes No
Mark_Poyser 2 on at

Like (0)

Report
Copy link

Link copied!

Can I ask if the Effective Date and Expiry Days are manual entry items or did you manage to achieve a calculated Expiry Days value based upon a review period value stored elsewhere?

Was this reply helpful? Yes No