Perform Flow actions further than 30 days from now

[SandyU]

I was recently working with someone who had training events entered in a SharePoint list, and who needed to begin a series of actions when the event was imminent. The tricky bit was that the events might be planned as much as 9 months out, meaning we couldn't use the creation of the list item as a Flow trigger, since currently a Flow can only run for 30 days. Following is the solution I came up with, which works well, at least for a fairly short list of items as this scenario involved.

In this scenario, instructors would fill out a Microsoft Forms form to request an event at some future date, and then after some initial actions, the only thing that would happen for a while is that the appropriate people would receive an Excel spreadsheet each week listing upcoming events (another topic for another day). So the organizers would be preparing based on this weekly list. Then the day before a given event, things would move into high gear, with various people receiving reminder notifications the day prior, the day of, and a feedback form the day after. But all of this might take place up to 9 months after the event was initially requested.

To handle this, I first added a toggle field to the event list in SharePoint: a yes/no (Boolean) column named NearTerm. The idea of this field is that it indicates whether an event is due to occur some time within the next week (actually, 8 days, since a reminder email goes out the day prior to the event). The default value is set to No, as in this scenario it would never be the case that an event would be requested less than a week in advance. So when a request Form is submitted, an item in the SharePoint list is created and some people are notified. This is the Flow which handles that:

Flow #2 runs weekly to email the Excel report to the appropriate people, but the other thing it does is set the NearTerm toggle on items which are coming up within the next 8 days. The first part of this Flow (below) uses the Schedule - Recurrence trigger to run once a week, gets the content of the Excel file, and emails it as an attachment. Next it iterates through the event list (which as I said is not a huge list), and for each item in the list, it checks whether the EventDate is less than or equal to 8 days from now, and if so, it sets the NearTerm to Yes.

 Tip: to create the condition

@lessOrEquals(items('Apply_to_each')?['EventDate'], addDays(utcNow(), 8))

I first used the Basic mode, and then tweaked it in Advanced mode. That is, I selected EventDate from the Dynamic data fields, and created the addDays expression in the Expression Builder, but since there's no "Less than or equal to" option in the condition comparison, I had to switch to Advanced mode to change the default Equals to lessOrEquals.

And lastly, Flow #3 triggers when an item is created or modified, and checks whether NearTerm is true, which would mean the event is now imminent. It then delays until the day prior to the EventDate, and begins the required notification process. (In my Flow I just have stubbed in one email per notification date, but there was actually more to it in the scenario.)

 So essentially, the weekly Flow sets the NearTerm toggle when it actually IS near term, which triggers the final Flow for actions occurring around the time of the event. This means events can be scheduled more than 30 days away, but since the final Flow isn't triggered until closer to the event, the 30-day limitation isn't relevant.

I hope this may help you in some similar scenario!

[JonL]

This is awesome Sandy! Thanks! 

I am totally going to use this!

[SandyU]

It was brought to my attention that it's not obvious what I'm doing in the Delay Until actions to delay, for example, until 1 day prior to the event...

So, for the Delay Until one day before the event, I used:

addDays(triggerBody()?['EventDate'],-1)

(EventDate is my dynamic content date field from my SharePoint list)

The trick there is to use the addDays function in Expressions, but "add" minus 1 to get the day BEFORE 🙂

AND the other trick is that I added a Z after the expression, because the Delay Until input must be in timestamp format. So it looks like this in the Flow:

Note that I like to add a comment to actions that contain expressions, so I can read them at a glance rather than needing to open up the expression - just a way of documenting for my future self 🙂

Then the action that delays until the event date,just uses the EventDate field, with a Z after it, and the one that delays until 7 days after, is similar to above, just adding 7 days instead of -1.

Hope this helps!

[mbrocchi1]

Could you please explain why there is the option to use 'Months' as the unit of time when setting a Delay action if Flows will only execute for a maximum of 30 days?

[avunjian]

Could you please explain why there is the option to use 'Months' as the unit of time when setting a Delay action if Flows will only execute for a maximum of 30 days?

THIS. Why is months an option? I just tested a flow with "minutes" set, set it to months once it was working properly, sent out an email to a bunch of contacts to initiate the flow, only to find out "months" doesn't actually work!

So disappointed. Why do I feel like there is always a major limitation and user-experience flaw with any O365 tool?