cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Prodigy
Post Prodigy

Sending an email alert when a date is approaches

Hi

 

I'm trying to set up a Flow which sends an email alert when a date in a list is approaching. The field name is called 'Document Expiry Date'. 

 

I have a Recurrence Flow to run once a day. Then a Get items action for the list. This leads to a condition within an Apply to each loop. I was following this post https://powerusers.microsoft.com/t5/Building-Flows/FLOW-TO-SEND-EMAIL-WHEN-DATE-APPROACHES/m-p/60452... but I've read that the 'Advanced Editor' option on the Condition action has been retired. 

This is how I've got the Condition set up but it skips the email. Any ideas how I'd need to configure it?

 

 

 

Condition.png

17 REPLIES 17
Highlighted
Super User
Super User

Re: Sending an email alert when a date is approaches

Hi @darrenfloyd666 

The condition appears correct so can you please post a screenshot of the flow run showing the entire flow process including the email action that is skipped?

 


Did I answer your question? Mark my post as a solution!

Proud to be a Flownaut!


Follow me on Twitter at @QG_LeeJHarris

Or on LinkedIn at in/leejharris

Highlighted
Post Prodigy
Post Prodigy

Re: Sending an email alert when a date is approaches

Hi @LeeHarris 

 

I set the condition to 'is less than' and it sent out the alert. 

 

What expression would I need to use if I wanted an alert to be sent out 15 days before the 'Document Expiry Date'? 

 

Highlighted
Super User
Super User

Re: Sending an email alert when a date is approaches

Hi @darrenfloyd666 

 

You would need an expression like addDays(DocumentExpiryDate,-15) where DocumentExpiryDate is the Dynamic Content. This will give you a value 15 days before the Document Expiry Date and can then be compared with utcNow() in your condition.

 

Hope that helps

 


Did I answer your question? Mark my post as a solution!

Proud to be a Flownaut!


Follow me on Twitter at @QG_LeeJHarris

Or on LinkedIn at in/leejharris

Highlighted
Post Prodigy
Post Prodigy

Re: Sending an email alert when a date is approaches

@LeeHarrisThanks Lee. How would I compare those two expressions? Would it be in the Condition field?

 

ConditionCompare.png

Highlighted
Super User
Super User

Re: Sending an email alert when a date is approaches

Hi @darrenfloyd666 

Yes, in the left side of your condition you need to add the expression. You can add the Dynamic Content for the Document Expiry Date by clicking the Dynamic Content tab after you have started typing the addDays expression.

The right side of the condition can be utcNow() as you already have it. 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Flownaut!


Follow me on Twitter at @QG_LeeJHarris

Or on LinkedIn at in/leejharris

Highlighted
Post Prodigy
Post Prodigy

Re: Sending an email alert when a date is approaches

Thanks @LeeHarris That doesn't seem to work.

 

When I type adddays into the formula field it wants a date stamp and the dynamic content choice of DocumentExpiryDate isn't an option. timestamp.png

 

If I type the formula out in full: addDays(DocumentExpiryDate,-15) utcNow() I'm getting 'This site says The expression is invalid. Any ideas? 

 

Expression.png

Highlighted
Super User
Super User

Re: Sending an email alert when a date is approaches

Hi @darrenfloyd666 

If the Dynamic Content is not displaying for you in the tab when creating the expression you can manually enter it. In order to see what the value is that you need to enter, simply hover over the Dynamic Content in another action as shown in my sample action below (you could add it to the Condition as you had before and hover over it to get the value, then remove it from the condition again).

 

Hover to see text of Dynamic ContentHover to see text of Dynamic Content

 

Type the value you see in the tooltip into the expression below.

addDays( TypeTheValueHere ,-15)

 

e.g. In my example shown in the gif above, the expression would be something like addDays(triggerBody()['key-button-date'],-15)

 

Your addDays expression goes in the left of the condition. The right side of the condition should contain utcNow() as a seperate expression.

 

Condition sampleCondition sample

 


Did I answer your question? Mark my post as a solution!

Proud to be a Flownaut!


Follow me on Twitter at @QG_LeeJHarris

Or on LinkedIn at in/leejharris

Highlighted
Post Prodigy
Post Prodigy

Re: Sending an email alert when a date is approaches

Thanks again @Lee tried that so I've got in the right

  

addDays(items('Apply_to_each')?['DocumentExpiraryDate'],-15)

 

and utcNow() in the left and I'm getting the following error when I test:

Flow error.png

Highlighted
Super User
Super User

Re: Sending an email alert when a date is approaches

Hi @darrenfloyd666 

If you click on the next/previous arrows to move through each of the items within the Apply to Each did all of them fail?

 

image.png

 

The error suggests that the field was blank for that particular item in the loop. You may need to add another condition step before the date check to validate if the DocumentExpiryDate is equal to Null.

 


Did I answer your question? Mark my post as a solution!

Proud to be a Flownaut!


Follow me on Twitter at @QG_LeeJHarris

Or on LinkedIn at in/leejharris

Highlighted
Post Prodigy
Post Prodigy

Re: Sending an email alert when a date is approaches

Thanks @LeeHarris yes they all failed. What would that Condition look like? What would the Yes and No Branches lead to? 

 

I added a Document Expiry Date of 31 Jan and thought that would trigger the email. 

Highlighted
Post Prodigy
Post Prodigy

Re: Sending an email alert when a date is approaches

Thanks in advance btw

Highlighted
Super User
Super User

Re: Sending an email alert when a date is approaches

Hi @darrenfloyd666 

You should be able to add another condition to the existing action like below

image.png

Make sure that the null condition is first in the list. The Date on the left of the Condition would be your DocumentExpiryDate and the null (shown in pink) on the right is the expression null.

If you have added a value in SharePoint but are still seeing Null in the Flow then you will need to confirm that your values are being returned correctly from your SharePoint list action.

 


Did I answer your question? Mark my post as a solution!

Proud to be a Flownaut!


Follow me on Twitter at @QG_LeeJHarris

Or on LinkedIn at in/leejharris

Highlighted
Post Prodigy
Post Prodigy

Re: Sending an email alert when a date is approaches

Hi @LeeHarris 

 

I'm getting an error on the Condition again, but I suspect I might know the reason. 

 

This is how I've got the Condition set up

Condition.png

 

The formula on the Add Days Expression is: addDays('Apply_to_each')?['Document_x0020_Expiry_x0020_Date']   

 

The error is: ActionFailed. An action failed. No dependent actions succeeded.

 

 So I decided to find out what the date values were.

 

utcNow returns:  2020-01-21T11:45:33.0939632Z

 

while an example of the Document Expiry Date field on the list is: 2020-01-22

 

Is that likely to be the reason it's failing and how can I mitigate it? 

Highlighted
Super User
Super User

Re: Sending an email alert when a date is approaches

Hi @darrenfloyd666 

It looks like your condition will never evaluate to true which might explain the error message that you are seeing. You need to change the null check to be "is not equal to".

 

Also, you are no longer adding the days as you were in your previous post. Your expression should be addDays(('Apply_to_each')?['Document_x0020_Expiry_x0020_Date'],-15)

 

Hope that helps


Did I answer your question? Mark my post as a solution!

Proud to be a Flownaut!


Follow me on Twitter at @QG_LeeJHarris

Or on LinkedIn at in/leejharris

Highlighted
Post Prodigy
Post Prodigy

Re: Sending an email alert when a date is approaches

Thanks @LeeHarris changed the null check but when I try and put that expression in I get this:

ExpressionisInvalid.png

Highlighted
Post Prodigy
Post Prodigy

Re: Sending an email alert when a date is approaches

Hi @LeeHarris 

 

I did some testing and the Document Expiry Date : 2020-01-23T00:00:00Z

 

while the UTC Date is: 2020-01-23T16:08:16.4835661Z

 

So the time doesn't appear to be picked up from the list field. Is that possibly why it's failing and is there a way round it?

 

Thanks in advance. 

Highlighted
Post Prodigy
Post Prodigy

Re: Sending an email alert when a date is approaches

@LeeHarris I got it working

 

I changed the addDays condition to: 

addDays('2019-10-28T00:00:00Z', -15,'yyyy-MM-ddTHH:mm:ssZ') and it worked! Now just got to stop it sending it emails after the -15 days (but I've got the condition set to 'Is less than or equal to' so I just need to monkey around with that) and get the Flow to just send one email alert. Any ideas? Thanks for your help!

Helpful resources

Announcements
FirstImage

Microsoft Ignite 2020

Check out the announcement of Power Platform content at Microsoft Ignite!

thirdImage

Experience what's new for Power Automate

Join us for an in-depth look at the new Power Automate features and capabilities at the free Microsoft Business Applications Launch Event.

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Users online (7,199)