cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jfeddes
Helper I
Helper I

Automate Email Based on Date Field in SharePoint List

Hello,

 

I would like to setup a flow to trigger when a particular date field in my SharePoint list is within 3 months of the current date and then email me.

Example:

Distribubtor | Expiration Date

Testing         | 04/01/2023

 

The flow should send me an email on 01/01/2023 alerting me that this "Testing" record has 3 months until the expiration.

 

Seems simple! But I am stuck.

 

Thanks,

Joel

1 ACCEPTED SOLUTION

Accepted Solutions
David_MA
Responsive Resident
Responsive Resident

Does your filter field look something like this or does it literally contain what you wrote above?

David_MA_0-1664468562659.png

If it contains a function value as shown above, you may need to format the expression like:

 

formatDateTime(addDays(utcNow(),90),'yyyy-MM-dd')

 

The filter query excepts the date to be in ISO 8601 format. And the expression also needs to be enclosed in single quotes. If you don't have quotes around the expression, it will treat the value as an integer and generate an error.

 

Also note how you wrote utcNow compared to my expression of utcNow() with the parentheses. The correct format is utcNow() and that could be your error as well.

View solution in original post

3 REPLIES 3
Sundeep_Malik
Community Champion
Community Champion

Hey @jfeddes 

Where are you stuck? Can you share?

 

Just create a scheduled flow that runs every 24 hours. 

Then use get items action and in filter query you can add 

Expiration date leq addDays(utcNow,90)

And after that use a compose, in that use length('Get_Items) greater than 0

If yes 

Send email

If no

Do nothing

 

Hello,

 

Thanks for the help! However, I am running into errors on the query filter.

 

This is my query filter: OpportunityEndDate eq addDays(utcNow,90)

 

The error is "query not valid". I did confirm in the SharePoint list settings that the column name is correct. I tried using your formula as well, but had no luck there either.

 

Any advice?

David_MA
Responsive Resident
Responsive Resident

Does your filter field look something like this or does it literally contain what you wrote above?

David_MA_0-1664468562659.png

If it contains a function value as shown above, you may need to format the expression like:

 

formatDateTime(addDays(utcNow(),90),'yyyy-MM-dd')

 

The filter query excepts the date to be in ISO 8601 format. And the expression also needs to be enclosed in single quotes. If you don't have quotes around the expression, it will treat the value as an integer and generate an error.

 

Also note how you wrote utcNow compared to my expression of utcNow() with the parentheses. The correct format is utcNow() and that could be your error as well.

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Users online (3,666)