cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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
Willemijn Resolver III
Resolver III

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 @Anonymous , 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 Resolver III
Resolver III

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

Anonymous
Not applicable

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 🙂 

Anonymous
Not applicable

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 Resolver III
Resolver III

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!

Anonymous
Not applicable

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

Highlighted
Willemijn Resolver III
Resolver III

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

Anonymous
Not applicable

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

Willemijn Resolver III
Resolver III

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

Anonymous
Not applicable

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 Resolver III
Resolver III

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
MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Automate Community!

firstImage

Now Live: Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

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

sixthImage

Community Summit North America

The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Users online (2,227)