cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SteveT
Level: Powered On

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

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: 

 

Flow1.PNG

Flow2.PNG

 

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

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Willemijn
Level 8

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

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. 

View solution in original post

11 REPLIES 11
Super User
Super User

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

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-RecurrenceCompose.png1 - Recurrence and Compose2-getItems.png2 Get Items3-ConditionApplyToEachCondition.png3 - Condition and Apply to each4-convertTimeZoneEmail.png4 - Convert time zone and email5-emailResult.png5 - Result

Willemijn
Level 8

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

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: 

 

image.png

SteveT
Level: Powered On

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

@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
Level: Powered On

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

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

 

flow3.PNG

 

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

 

Many thanks 

 

SteveT

Willemijn
Level 8

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

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
Level: Powered On

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

@Willemijn 

 

Thanks for the swift reply 🙂 

 

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

 

flow3.PNG

Willemijn
Level 8

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

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

 

Flow.gif

SteveT
Level: Powered On

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

@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: 

 

flow4.PNG

 

Here is how the flow is now looking also: 

 

flow6.PNG

 

Thanks 


SteveT

Highlighted
Willemijn
Level 8

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

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. 

View solution in original post

SteveT
Level: Powered On

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

@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
Level 8

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

Haha, will hold you to that Smiley Very Happy

 

Great to hear its working now! 

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 84 members 5,420 guests
Please welcome our newest community members: