Email Reminder Flow - help!

[remyrat]

Hello,

I'm building a flow that prompts employees via email to fill out a form and schedule a review meeting. This meeting is something that needs to happen once a year per employee. I'm trying to build a flow that will send an email 30 days before the year mark to allow time for the employee to schedule with other team members. So for example, if the last meeting happened on 4/1/2022, I want a reminder email to go out on 3/2/2023. 

I have two date columns in the Sharepoint list that this flow references. One is the date of 'Last Review'. The other is a calculated column that adds 12 months to the 'Last Review' date and produces the 'Next Review' date.

So far, I have tried Recurrence > Get Items > Apply to Each > Send Email, seen below:

When I run this flow, I get this error due to the Calculated data type of the Next Review column

I am a beginner and not good at writing expressions. Is there a way to write a Flow that can used the 'Date' type of the 'Last Review' column, add 11 months to that, and send an email at that time?

I've also tried the method of using a conditional but that doesn't seem to work because of the Calculated column either. 

Please help! Thank you!

[tom_riha · Accepted solution]

Hello @remyrat ,

you can't use a calculated column in a filter, you must base the filter on the column with date and move the whole calculation in the filter.

If it should go 30 days before the next yearly review it'll be like:

LastReview eq 'addDays(addToTime(utcNow(),1,'Year'), -30, ,'yyyy-MM-dd')'

where the whole addDays(...) part is an expression.

[remyrat]

Hi Tom, 

I replaced the filter query with the expression you provided (thank you). The Flow runs successfully now, but I'm not receiving an email for my test value of 3/31/2022. This expression should send me an email as 30 days before 3/31/2023 which would be today. I didn't change anything else about the Flow. Any ideas? Thank you!

[tom_riha]

Hello @remyrat ,

when you check the flow run history and expand the 'Apply to each', are the inside actions grey or can you see a number of loops?

I also noticed you're using the 'Send an email notification' action - that one has a limitation on 100 emails per 24 hours which could be also a problem if you already reached the limit during testing, it's better to use the Office 365 Outlook action 'Send an email (V2)'.

[remyrat]

Hello,

I updated the email action to 'Send an email (V2). The inside actions of the 'Apply to each' are greyed out, and I don't see any loops. I thought maybe it had to do with the formatting of the date, because I am in the US, but even when changing the date format to mm/dd/yyyy it did not work. It still says it's running successfully, but no email is received. Thank you! 

[remyrat]

I took another look at the Flow history and it seems like the expression is not producing the date I need and that is why the email is not being sent.

Today, 3/2/23, the query is looking for 2/01/2024 in the 'Last Review' column, which is obviously not going to be there. 

I am not sure how to rewrite this expression to do what I need. 

For example, if the last review date was 4/2/2022, I would want the reminder to go out today, 3/2/23 (11 months after the last review). I'm not sure how to make this work if the query is looking for a match in the LastReview column. Any ideas?

[remyrat]

Solved! I simply had to make an adjustment to the formula:

addDays(addtoTime(utcNow(),-1,'Year'), 30, 'MM/dd/yyyy')

Today's date - 1 year + 30 days = this produces a result that can be found in the LastReview column!

3/2/23 - 1 year = 3/2/22

3/2/22 + 30 days = 4/2/22

My email sent successfully. Thank you!