Flow to send email at scheduled time when date condition is met

[SteveT]

Hi Guys, 

I'm looking to build some reminder email flows, which will send an email at a specfic time of day when a date is met. I would like the reminder logic to not include\ count weekends and I am also based in the UK so will need to use our local time format. 

This is my scenario, I have a SharePoint list which has a date\time column called "PaymentDate". I have another column called "PaymentDateCountDown" which is a calulated column which looks at the "PaymentDate" column and subtracts 2 days. The calculated column has the following formula:

=[PaymentDate]-2 

Here is how my flow looks currently: 

Can anyone give me some steer on how best I can achieve this, sounds pretty straight forward but I'm new to flow and finding this challenging 🙂 

Many thanks 

SteveT

[RobElliott]

Hi @SteveT , I would build the flow differently and in fact have 2 flows. The first flow would just be for the actions when the new item is created in the list if indeed you need anything to happen at that stage. If there is a review date then I would add that as a calculated column in the SharePoint list rather than use a flow to set it.

The second flow would be a recurrence flow which would be set to run once a day. Then..

• add a Compose control expression to get the current day of the week:
  

  dayOfWeek(utcNow())

• add a Get items from the list
• add a Condition control for the Output of the Compose  where output is greater than or equal to 6 (i.e. Saturday)
• If Yes, do nothing
• If No, add an Apply to each and select value from dynamic content
• In the If No box add another Condition with an expression in the left box for utcNow() is greater than or equal to NextReview
• If Yes, add a Convert time zone action to set the format string to dd/MM/yyyy and send the email.
• If No, do nothing.

Rob
Los Gallardos

1 - Recurrence and Compose2 Get Items3 - Condition and Apply to each4 - Convert time zone and email5 - Result

[Willemijn]

Hi!

I would use the recurrence trigger here, to check every day at  a specific time if there are items which have a paydate less then today +2 days (in that way you don't need your calculated column) it would look something like this: 

[SteveT]

@RobElliott @Willemijn Thanks for the steer guys really appreciate the help on this! 

I'll report back with my findings once I've had chance to develop and test. 

Have a great weekend. 

SteveT 🙂 

[SteveT]

Hi @Willemijn, 

I've tried your solution however I'm running in to one issue, when tyring to configure the "Get Items" step,  the Filter Query isnt working. It keeps complaining that "No Dynamic Content Available". I've tried writing the expression manually (see below) however it complains and won't save my  changes? 

Payment Date addDays(utcNow().2

Payment_x0020_Date addDays(utcNow().2

Can you advise what I'm doing wrong here and how to resolve this issue? 

Many thanks 

SteveT

[Willemijn]

Hi Steve,

I'm sorry for the confusion, my mistake (something with keyboard English/Dutch 😉 )

You're . should be a , in the expression so it should be: 

Payment_x0020_Date addDays(utcNow(),2

Another thing what I see is that your 'Paymentdate' is put between '' this is not correct.

It should be: Payment_x0020_Date lt 'addDays(utcNow(),2'

The '' should be around your 'pink' block of the addDays

Hope this helps!

[SteveT]

@Willemijn 

Thanks for the swift reply 🙂 

I've tried that but I still wont save my changes - am I missing something here? 

[Willemijn]

You should type Payment_x0020_Date lt in the field, and then add an expression addDays(utcNow(),2 in the expression box. Like this: 

[SteveT]

@Willemijn 

Thanks for the tutorial - that is very helpful - I can see where i was going wrong now 🙂 

I have tested however when the flow runs I get an error on the 'Get Items' action: 

Here is how the flow is now looking also: 

Thanks 

SteveT

[Willemijn · Accepted solution]

Hi Steve,

There is missing a lt in the expression. It stands for less then, and you need to have a eq(equals) lt (less then) gt (greater then) in order to be able to compare to values. Now you just say payment date today-2 but Flow doesn't know whether it should be equal, less or greater then. 

[SteveT]

@Willemijn it's working 🙂 

Many thanks for all your help on this! Definitley owe you a few beers next time I'm in the Netherlands 😉 

Have a great Monday. 

SteveT

[Willemijn]

Haha, will hold you to that 

Great to hear its working now!