cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PedroRibeiro
Level: Powered On

[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
PedroRibeiro
Level: Powered On

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
Community Support Team
Community Support Team

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.
PedroRibeiro
Level: Powered On

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.

PedroRibeiro
Level: Powered On

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
PedroRibeiro
Level: Powered On

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
thirdimage

Power Automate Community User Group Member Badge

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

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Users Online
Currently online: 101 members 5,366 guests
Please welcome our newest community members: