Holiday Reminder Email

[dmattia]

Hello, 

I am trying to create a Holiday Reminder Email to be sent 30 days before a holiday. I created a SharePoint list with the holiday name and date, but cannot seem to trigger the email. I tried testing with multiple dates 30 days before. 

[abm]

Hi @dmattia 

You need to use -30 (minus 30)

[dmattia]

@abm I changed it to -30 and I added testing dates of 7/30 and 7/31. It did not work. 

[abm]

Hi @dmattia 

This is because Date is stored with Date and Time.

You don't need to add the IF condition. Use the ODATA Filter query to retrieve the dates.

See below

Expression is as follows:

(Date ge '@{adddays(utcNow(),-30, 'yyyy-MM-ddT00:00:00Z')}') and (Date le '@{adddays(utcNow(),-30, 'yyyy-MM-ddT23:59:00Z')}')

[dmattia]

I'm not sure what happened, because my column is labeled as "Date"... 

[abm]

Hi @dmattia 

Based on your previous screenshot I have given the column name as Date.

Replace the ODATA Filter query with the right column schema name.

Thanks

[dmattia]

I guess I'm not sure what I am doing wrong. The column from my SharePoint list is titled "Date", which is exactly how the filter query is labeled. What am I missing? 

[abm]

Hi @dmattia 

That means its been renamed after. So to find out the schema name follow the below steps:

1. Click List Settings

2. Click Date Column

3. Check the url of the browser

4. End of the browser it says field=xxxx. Here xxxx is the actual schema name you need to use.

Thanks

[dmattia]

I was able to locate the schema name - thank you! When I run the test though, it does not work... I'm still not sure what is happening. I appreciate your help, I'm new to this. 

[abm]

Hi @dmattia 

So you looking for Todays date - 30 days

That is 2023-05-31

Have you got record matching the above date?

Thanks

[dmattia]

No, I want the email to go out 30 day's BEFORE the holiday, excluding dates that have already passed, even if they are within the same month.

[abm]

Hi @dmattia 

30 days before the date and excluding the dates thats already passed means it should match -30 days isn't and matching the exact date. If not tell me in an example.

Thanks

[dmattia]

I really need some help with this. I'm not sure what I am doing wrong, but the automation that I created continues to fail daily with this error "The expression "Date ge '2023-06-20T00:00:00Z') and (Date le '2023-06-20T23:59:00Z'" is not valid. Creating query failed."

I have the holiday dates in a sharepoint list. I want an automatic email to go out 30 days BEFORE the holiday as a reminder - not before.

Filter Qurey: Date ge '@{adddays(utcNow(),-30, 'yyyy-MM-ddT00:00:00Z')}') and (Date le '@{adddays(utcNow(),-30, 'yyyy-MM-ddT23:59:00Z')}'

LIST: 

[abm]

Hi @dmattia 

Here you missing a left bracket at the beginning and a closing bracket at the end of the query.

[creativeopinion]

Your expression has a typo I think. adddays should be addDays() I also don't think the brackets are necessary though not sure if they are also causing an issue with your Filter Query. But the D in days should be capitalized.

[abm]

Hi @creativeopinion 

Expressions are not case sensitive. Here it is.

Run history

[dmattia]

Friends, 

This continues not to work. I'm not sure what is happening, but it is not triggering my test dates. Could it be something to do with the start time? 

[abm]

Hi @dmattia 

Please have a look at this video which explains about recurrence.

https://www.youtube.com/watch?v=6sr8OSn5XZQ

Thanks

[dmattia]

Thanks, So it's what I want - Daily. Just wasn't sure if my lack of knowledge was causing the flow not to run; however, still have a broken flow that seems simple. 🙂

[creativeopinion]

Does your date column store time and date or just the date? If it's just the date then this filter query should work. I tried your query on my list which doesn't include a time and it wasn't pulling anything.

Date eq 'addDays(utcNow(),30,'yyyy-MM-dd')'

Hope this helps!