Notification by mail using calculate date

[deCastroTony]

Dear all,

Today i'm stuck trying to set up an alert (by email) based on calculated date.

All have been done into sharepoint side :

- Date of the creation of the document (Date 1)

- Date of review of this document (Date 2) 180 days after the creation of the document.

As i can't set an alert on calculate date i don't know how make this things working.

I was using the template created from sharepoint to configure an alert on classical date (date 1).

But not so easy to change this template for my needs.

My actual flow layout is like that :

Recurrence

Get items

Apply to each

Send an email

Where can i add this news calculate date ? and how ?

Any help will be appreciate ;D

Many Thanks.

[schwibach]

You'll want to do two things:

Add a column 'StatusReviewed' or something like that.

You can then filter the results of the Get Items action in SharePoint to the items that have not been reviewed.

Within the Apply_to_each you add a condition step that will check if adddays(utcnow(),-180) is greater than the first date. And then you change the field StatusReviewed to yes and do everything you need to do with the entry. 

Make sense?

[grantjenkins]

I've built a flow how I would likely achieve this.

Assumptions:

• The review date is 180 days after document creation for ALL documents.

Steps:

1. Trigger is scheduled daily
2. Get date 180 days ago (Past Date)
3. Convert Past Date to appropriate time zone and format with yyyy-MM-dd
4. Get files with a Filter Query where Created is >= Past Date AND < (Past Date + 1)
5. Loop through each of the files (will only contain files due for review)
   1. Send reminder email

Below is the full flow. I'll go into each of the actions.

Trigger is Recurrence set to run daily at certain time.

Get past time will return the date from 180 days ago.

Convert time zone will convert the past time into my time zone and format it as yyyy-MM-dd.

Get files (properties only) used Filter Query to return only files where the Created date is greater than or equal to the past date and less than the past date + 1 (tomorrow). So, it will only return files that are due for review.

The expression used to add 1 day is:

addDays(body('Convert_time_zone'), 1, 'yyyy-MM-dd')

Also, note that you'll need to enclose both the dates in your filter with single quotes.

You can then use an Apply to each to loop over each of the files and send the email reminder. The key thing to note here is that the files returned from Get files are only the ones that are due for review, so no need to have any conditions, etc.

[deCastroTony · Accepted solution]

Thanks @grantjenkins,

First of all if i understood well your steps i have to configure the date from utc to local time.

I think my sharepoint is allready setup in "french format".

Also i got an an error :

L'expression « Created ge '2022-05-06' and Created It '2022-05-07' » n'est pas valide. Creating query failed.

Not sure why, maybe due to french translation of the formula ? 

also do you know if it's possible to query a sharepoint list and do this trick ?

As you can see below, i have a column call 'Update' 11/10/2022 (means the last update on this docs) and sharepoint calculated a revision date. (column revision 09/04/2023 )

I wanted a notification by mail on 09 april 2023.

In all case thanks for your help.

[deCastroTony]

I finaly succeded with your help.

Get items

and full name without space of the sharepoint colums name.

In my case "mise à jour le" -> Mise_x0020__x00e0__x0020_jour_x0