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?
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?
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'?
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
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.
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.
If I type the formula out in full: addDays(DocumentExpiryDate,-15) utcNow() I'm getting 'This site says The expression is invalid. Any ideas?
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).
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.
Thanks again @Lee tried that so I've got in the right
and utcNow() in the left and I'm getting the following error when I test:
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?
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.
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.
You should be able to add another condition to the existing action like below
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.
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
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?
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
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.
@LeeHarris I got it working
I changed the addDays condition to:
The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Automate Community!
We are excited to announce the launch of Power Virtual Agents Community. Check it out now!
We've updated and improved the layout and uploading format of the Power Automate Cookbook!
Fill out a quick form to claim your user group badge now!
The top training and networking event across the globe for Microsoft Business Applications