1

I have a SharePoint list that is populated with ticket data. There are two primary columns "Open Date" and "Calculated Date" I'm trying to figure out how to set the calculated date 2 weeks out from the open date and every two weeks thereafter.

Essentially, I'm using Microsoft flow to automate a process. The flow will check the ticket status if it reads open it will look at the open date and kick an email off two weeks after the open date. For example say ticket 1 is opened as of today 4/16/20, the flow will check the ticket status if it is open it will look at the open date and there will be conditions set to send an email 2 weeks after the open date. However, I will also need it to send an email every two weeks after until its closed. 4/16/20 open date > email 4/30/20 > email 5/14/20 and so on until it's closed.

Essentially, I'm looking for a way to have imported dynamic date data and a calculated date column that always reads two weeks from the imported dynamic date data. For example the imported data would be "last ticket update" ex. it's entered into SharePoint as 4/16/20 I would like another column "calculated date" to be able to change dynamically 2 weeks out from the "last ticket update." A ticket will be entered into SP with a date and status if the status remains open I need the calculated date column to be every two weeks to send a reminder. I hope that helps explain what i'm trying to do.

enter image description here

enter image description hereenter image description here

enter image description here

enter image description here

0
1

You can try setting up your flow like this:

enter image description here

After a new item is added, initialize two variables (ReminderDate and NextReminder) that holds the initial value addDays(triggerBody()?['Created'],14).

Then, add a Control Action of type Do until with a test on your status column ("Balance equal to 200" in my example). Note that this field should be from the later "Get updated item" action, not the original trigger!

Inside this, insert the Delay until action with the ReminderDate variable as the timestamp.
Insert a step to get the item (by the ID from the original trigger) so we can know if the status field is changed.

Next, add your notification email, then update the NextReminder variable to be addDays(variables('ReminderDate'),14), finally set the variable ReminderDate to the value of NextReminder.

This loop should continue to send an email every two weeks until the ticket status is closed.

Here is my inbox with this flow running with 2 minutes instead of 2 weeks: enter image description here


More detailed screenshots:

enter image description here The formula used in the first variable initialization is: addMinutes(triggerBody()?['Created'],2)

In the second variable initialization, it is just set to the value of ReminderDate as it doesn't matter what it is yet.

enter image description here

In the "Do until" step, here I am using a field called "Balance". This is from the "Check the item" step, NOT from the original "When an item is created". This is important.

enter image description here

Final formulas: addMinutes(variables('ReminderDate'),2)
The last variable is set to the value of NextReminder.

15
  • Rune, thank you I'll give this a try. I assume this would work just as well if I set the trigger to "when a new item is created or modified." The only thing I worry about with using this method is time out. I did have a flow set up similar to this, except for some reason it was pulling all the old SharePoint data as well. For example, say I wanted an email sent to Bob Smith on 4/17/20 (as his 2 week reminder), I would also get a notification send to Sally Smith who still has an open ticket from 4/5/20 (this would send immediately instead of the 2 week time lapse).
    – Ryan
    Apr 17 '20 at 14:07
  • If you do that you might end up with a LOT of reminders! Any update to the list will start a new instance of the flow - and thus a new loop with notifications... Apr 17 '20 at 14:09
  • If 1 list item equals 1 case, and the reminders should be sendt out based on when it was opened, I would use the trigger from my example. Otherwise there is something I'm not quite understanding on how you want this to work :-) Apr 17 '20 at 14:12
  • I see what you mean. Will what you described work with dynamic data? For example if the SharePoint list is not populated and say 3 tickets come in as open with dates of 4/15, 4/16, and 4/17 respectively. Will I get a reminder 2 weeks out for each of those dates? I'm trying to have the initial date come in on the ticket > reminder two weeks from the initial opening date > reminder two weeks out from the first reminder > reminder two weeks out from the second reminder and so on until it is marked closed. I hope that makes sense. I can set it to only when an item is created.
    – Ryan
    Apr 17 '20 at 14:15
  • Yes, each of the three tickets will start their own flow - using the created date as the starting point for each loop. Here's how you can test this: create the same, but use something like 5 minutes instead of 2 weeks to see it in action. Apr 17 '20 at 14:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.