Power Automate Send Email Reminders after 30 days and close on the 60th day

[RLSid17]

Hi,

I have a complex use case for when the item in SP is pending. The status would be pending with the requestor and after 30 days it would send 1st email reminder and then it will send 2 reminders, 2 weeks, and 1 week before the 60th day where it will be auto closed. And then if the requestor responded, the status will be changed to Active.

Is there a simple way to create a flow like this? I'm also struggling where it keeps triggering whenever I made modifications if the status is in pending.

[Ankesh_49]

@RLSid17 

You may not get a straightforward flow for your requirement. However, this solution here is similar to what your are looking for and will help you create your own logic to get the desired flow.

https://powerusers.microsoft.com/t5/Building-Flows/Send-reminders-at-30-days-60-days-and-90ty-days/t...

Hope it helps!!

Cheers,
Ankesh
--------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

[RLSid17]

I have tried something like this but what I would like is when the status is pending, there will be a date that is pending with user then it would automatically add 30 days where it should be the due date. I have tried that and it triggers every time there are modifications made while the status is pending. Basically what I'm trying to do is 3 Strike Rule process for tickets. If user replied then it will change to Active.

[ScottShearer]

@RLSid17 

You'll want to start with a recurrence trigger that fires once a day.

You'll follow that with 3 Get Items actions each with a filter query.

The filter queries will look something like this for the first email:

Status eq 'Pending' and Created eq 'addDays(utcNow(), -30,'yyyy-MM-dd')'

For the second email:

Status eq 'Pending' and Created eq 'addDays(utcNow(), -42,'yyyy-MM-dd')'

Status is the internal name of your Status column.

Loop through the results of each Get Items action and send your email.

Here is a link for more info on SharePoint filter queries.

You say "when a user replies, the status will change to active" - how do you plan to make that happen?

[RLSid17]

I have tried your solution but it came up as:

"The expression "Status eq 'Returned to Requestor' AND DateReturnedtoRequestor '2022-08-19'" is not valid. Creating query failed.
clientRequestId: 368fbbbd-8f16-4d91-8a3f-f27c1d0b4ebb
serviceRequestId: 368fbbbd-8f16-4d91-8a3f-f27c1d0b4ebb"

I'm not sure why I need to subtract 30 days but I also tried that it didn't work.

[ScottShearer]

@RLSid17 

You don't have the eq operator after DateReturnedToRequestor

Is DateReturnedToRequestor a calculated column?

[RLSid17]

Oh my bad. I have input the eq operator after DateReturnedtoRequestor but it is still showing as not valid.

The expression "Status eq 'Returned to Requestor' AND DateReturnedtoRequestor eq '2022-06-20'" is not valid. Creating query failed.
clientRequestId: 7e50d28d-bc18-4803-86f0-38239ded0736
serviceRequestId: 7e50d28d-bc18-4803-86f0-38239ded0736

[RLSid17]

Ok for some reason it worked but it is showing the date from last month, not next month which would be 30 days from now Aug. 19.

Apologies for your earlier query when a user replies, the status should change to Active and the dates should be empty. It really is a complex case and I'm wondering if this is possible. I'm trying to make it at automated as possible with less interaction from the editor of the SP list.

[ScottShearer]

@RLSid17 

My error - use 30 in the expression rather than -30.

Can you explain how the user will respond?

[RLSid17]

Yes it successfully ran. So basically the filter would be if the Status is Return to Requestor then the Date Returned to Requestor is the date today plus 30 days then that would trigger?

The use would respond via email to a shared mailbox. But I forgot if that is the requirement but let's assume that they will respond via email.

[ScottShearer]

So the Flow should run every day - the recurrence trigger should be configured to run once a day.

Every day, the Get items action will return items where the Status is return to Requestor and Date Returned is today plus 30 days.

You can add additional Get items actions that will return items for the other two emails so that all you'll need is one Flow.

It is going to be hard to use an email to a shared mailbox to start a Flow that changes the status and clears out the dates.  That will require that you parse the text in the email and that usually does not work out well....

Rather, I suggest including a link in the email that go to the list item - the individual can check a box of something like that in your list.  Then, you can have another Flow that clears the dates and sets the status.

[RLSid17]

Thank you for quick responses.  Much appreciated! 😊

Yes I would think that would work with the checkbox. Thanks for your suggestion!

As for the flow I have added 30 days, 44 days and 53 days. It sends 3 emails at the same time:

I have set the Date Returned to Requestor to July 20 which is today.

[RLSid17]

@ScottShearer shouldn't the flow only send 1 email? this flow is sending 3 emails at the same time.

[ScottShearer]

@RLSid17 

Are you saying that all 3 emails are going out to the same person at the same time?  If so, then your filter queries must be changed.  Please post screen shots of all 3 Get items actions showing the filter queries for each one.

[RLSid17]

The filter query for each Get items is the same with the difference being the number of days.

I apologize if I'm not making sense of this as this has been troubling for me. 😄

Just to reiterate, when the Status is Returned to Requestor, the user will have 30 days before they are able to reply so once it reached the 30th day with no reply, the flow will send an email and then a 2nd one and a 3rd one if there are still no replies. I got the logic/solution that you have provided for the Due Date but the next issue is how do I automatically add 30 Days for when the status becomes Returned to Requestor? Usually they put in the date of when the ticket is returned to requestor. I was thinking to add 30 days when they have inputted that date but I tried the trigger for when an item is created and modified and update item it caused an infinite loop. 

Also they really want for it to be less interactive meaning no checkboxes if possible.

[ScottShearer · Accepted solution]

@RLSid17 

I may be misunderstanding, but my understanding is that the emails are based off of the DateReturnedToRequestor column.  Do you want is for the DateReturnedToRequested to be updated to the current date when the status is changed to Returned to Requestor?   No need to add 30 as the filter queries take care of that. 

See my example below - you'll need a second Flow.

[RLSid17]

Wow! That actually works thank you so much Scott! It didn't trigger again after few modifications when status is Returned to Requestor. Little by little I got the idea of conditioning based on the columns. 🙂 

For the Date Returned to Requestor I want it to add 30 days but I think that would fall in the column Due Date. So I guess that would be the second flow for the email reminders?

[RLSid17]

Hi @ScottShearer 

Going back to the get items for the due date this is what I've done:

So I've created 2 due date columns for the reminder to be sent but it is sending emails at the same time instead of only 1 based on the date.

I've added 30 and 44 days based on the date returned to requestor and when I ran the flow it sends 2 emails. Please let me know if there are other details needed from me. 

[RLSid17]

Please disregard my previous reply, I'm able to resolve the flow for the email reminder. Thank you so much @ScottShearer! I had to change the filtering of the Due Date equals to Today to check each items.