cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

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).

2018-11-28_16h29_52.png

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?

2.png

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

 

Thanks in advance.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

Re: Sharepoint reminder alert based on columns date and days

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.

2018-12-10_18h01_41.png

Here's my flow:

1. Every day at 18h, the flow runs.

2018-12-10_18h03_37.png

2. Get files from the document library.

2018-12-10_18h05_05.png

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.

2018-12-10_18h06_34.png

 

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.

2018-12-10_18h10_09.png

 

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#s...

It took me several days, but I'm glad I got it.

Hope this help someone.

Thanks for all the help.

 

 

View solution in original post

4 REPLIES 4
Highlighted
Community Support
Community Support

Re: Sharepoint reminder alert based on columns date and days

Hi @PedroRibeiro,

 

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:

1.PNG

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

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

 

Best regards,

Mabel

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

Re: Sharepoint reminder alert based on columns date and days

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.'.

 2018-11-29_10h00_48.png

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.

Highlighted
Frequent Visitor

Re: Sharepoint reminder alert based on columns date and days

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?
Highlighted
Frequent Visitor

Re: Sharepoint reminder alert based on columns date and days

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.

2018-12-10_18h01_41.png

Here's my flow:

1. Every day at 18h, the flow runs.

2018-12-10_18h03_37.png

2. Get files from the document library.

2018-12-10_18h05_05.png

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.

2018-12-10_18h06_34.png

 

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.

2018-12-10_18h10_09.png

 

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#s...

It took me several days, but I'm glad I got it.

Hope this help someone.

Thanks for all the help.

 

 

View solution in original post

Helpful resources

Announcements
firstImage

Super User Program Update

Three Super User rank tiers have been launched!

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Users online (5,545)