Create a scheduler based on two distinct dates

[deathbysuitcase]

Hi there.

One of my project requirements is to create a scheduler where the user supplies two distinct dates, one for a deadline and the other for when a record in a SharePoint list is created.

The list of tasks is fed with data directly from Power Apps.

There are two columns there for storing both dates. 

The idea is to initiate a Power Automate Flow on "when a new item is created" to get those two dates, calculate the difference between the date the item in the SP list is created and its deadline, then schedule to notify the user at some fixed intervals and in the end create columns in the same list to store the dates of those intervals.

The tricky part is that the scheduler depends on the following requirement.

The notifications should be send via an email to the user in the following  schedule. If 3 months away from the deadline, send notification on creation, then 2 months before the deadline, 1 month before the deadline, 3 weeks, 2 weeks, 7 days, 6 days, 5 days, 4 days, 3 days, 2 days, 1 day, on the day of the deadline, and then every day past th deadline until some flag (reminder) in the list is set to false.

Note that a given task's deadline could be set for example only 5 days away, or 2 and a half week away.

Also the deadline is editable, so once its changed the flow needs to recalculate the whole schedule again.

I'm quite new to Power Automate and I don't even know if this kind of task can be pulled off there, but  any help is appreciated. 

[Rhiassuring · Accepted solution]

Hi there, 

Yeah, I think it's possible. Trying to think of the least annoying way to do it. 

I think your flow would be scheduled to run once a day, and filter on all items in your list where your status is not equal to Complete (or whatever your "hey this doesn't need to be checked anymore" value is.

Instead of thinking in weeks / months / days, I think we would want to simplify by thinking just in days, and Initialize an Array (arrReminderIntervals) that would contain each of your potential reminder dates ... 
So 90,60,30,21,14,7,6,5,4,3,2,1.

Then, find the difference between utcNow() and the deadline. You can do that using @Pstork1 's step-by-step post, here. This will turn into an Apply to Each.

Then add a condition - if the output of "Difference in Days" is equal to.. and here you will manually create an Apply To Each around your Condition that points to your arrReminderIntervals, and use "Current Item" for the "is equal to". That way it will cycle through and say "Is the difference between today and the deadline equal to any of these pre-set array day values? If it is, I will send an email. If it isn't, I will go through the others, or, I will go to the next entry."

I think that'll suit your need ... unless you have an extremely high number of items, then you might want to do it the other way around where you calculate the date for each potential reminder interval first (90 days from now is Sept 3rd, 60 days is August 3rd... etc), create an array of that, and then loop through your "Get Items" using the filter of Deadline looking at each date in the array.

Either way would work. 

[deathbysuitcase]

Hi @Rhiassuring 

Thank you for your message.

I'm a bit confused about the part where I add a condition and manually create an apply to each around my condition though. Do I create an apply to each inside where I choose a value my output is equal to?

Here's where I'm stuck on it atm.

[deathbysuitcase]

Following up, here's what I came up with.

1. The flow is scheduled to trigger every day at midnight.

2. Get items from the SP list (create a new view in the list so you get only the items you want)

3. Initialize an array of reminder intervals. These are set to the following values [0, 1, 2, 3, 4, 5, 6, 13, 20, 29, 59, 89]

4. Apply to each record found.

4.1. Compose the deadline date.

4.2. Compose the current date.

4.3. Compose the deadline ticks.

4.4. Compose the current date ticks.

4.5. Compose the difference in ticks.

4.6. Compose the difference in days.

4.2.1 Apply to each item found in the array of reminder intervals.

4.2.2 Condition on if item found in the array is equal to output from compose difference in days.

4.2.3 If yes send an email to the user.

Once again big thanks to @Rhiassuring for suggesting how to solve this problem. I'm accepting your solution.