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!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.