Dec 06 2017 11:34 AM - edited Dec 06 2017 11:36 AM
Hello,
I'm fairly new to MS Flow. I've seen countless videos around the web on how easy it is to create automated tasks, and it seems to be when it doesn't involve automation against expiry dates; per the other two flows I have setup that are working fine.
However, I can not find a solution for what I'm trying to do... I have a SharePoint list that I'd like to use to send a notification to item owners when the date in the "Plan Date" field is 30 days away. I'd like to use a formula that gives me the difference between the "Plan Date" and today's date, and if it is = to 30 days, send an email to the Item Owner to remind them that they have an upcoming action to execute for the item they own. Using the example data below, an email would have gone out to the data owner on December 1st since he planned to retire the item on January 1st, 2018.
Item Owner: First Name Last Name
Item Owned: Item 1
Plans for Item Owned: Retire
Plan Date: January 1st, 2018
The steps that I have taken in Flow are the following:
Here's the code for condition #2: @equals(items('Apply_to_each')?['Plan_x0020_Date'], addDays(utcnow(), 30))
Please advise on what I'm doing wrong.
Sincerest thanks in advance!
Dec 06 2017 12:05 PM
I have a similar workflow for our contracts database. But the way I solved it was do the date calculation in SharePoint.
So I have a column, called "Expiry Date", and one called "Days before Expiry Date" and finally a calculate field call "Notification Date", which is calculated as follows:
=[Expiry Date]-[Days before Expiry Date]
Then in the flow, I do the same as what you have except replace the comparison with something along the lines of:
@equals(items('Apply_to_each')?['Notification_x0020_Date'], addDays(utcNow(), 0))
So that is compares todays date with the notification date and triggers the email.
Dec 06 2017 12:31 PM
Dec 07 2017 11:14 PM
Hi, @Latisha Williamson...
I think maybe the key is that now() has a time portion as well, so it will likely never exactly equal your "Plan Date" - if Plan Date is a SharePoint date field set to "Date Only", the time will always be midnight in UTC time.
I got this to work by using the StartOfDay function in Flow, to force both now() and the SharePoint date to be at time 00:00. I used the Initialize Variable action near the beginning of the Flow, to create a variable called 30Days with a formula of:
addDays(startOfDay(utcNow()),30)
which takes midnight today and adds 30 days.
Then within the Apply to Each section, I added a Compose action before the comparison condition, to zero the time portion of the SharePoint field (otherwise it would be midnight in UTC), and concatenate a Z to the end, so we can compare like text strings (the variable result is in the ISO 8601 format):
"@concat(StartOfDay(item()['StartDate']),'Z')"
I couldn't find a way to enter this Compose formula using the Dynamic/Expression UI, so I had to type it manually, hence the at sign and the quotes around it.
And then in my Condition, I compared the Output of the Compose, to the 30Days variable, and that worked.
I'm not sure this is the most elegant way to do it, but I hope it may help you, without needing to have your SharePoint folks modify the list.
Sandy