New to MS Flow : Having Trouble With a Simple Automation Task

[Latisha Williamson]

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:

1. Recurrence: Interval = 1 / Frequency = Day
2. Get Items: Site Address = List URL / List Name = Items List
3. Apply To Each: Select an output from previous steps = Value
   1. Condition 1: Plan Date Is Not Equal To Null
   2. Condition 2: Plan Date Is Equal To Today's Date + 30 Days (see code below)
4. If Yes: Send an email to the Item Owner (using email field).
5. If No: Do nothing

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!

[#2]

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.

[#3]

Hi Stephen,
The SP list is owned by another team, I have access to it, but don't think I have rights to modify it... which means there could be a delay in getting the changes implemented.
I'll give it a day or two to see if someone recommends a solution that doesn't require modifying the list; if not, I'll give your recommendation a try and let you know how it works out. Thanks for your feedback!

[#4]

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