How to send approval reminder email only on work days, X number of times, before single, final escalation email?

[Agnes1]

Hello,

For background: I have a Sharepoint library with a number of files (processes). Each file needs to be approved from time to time, which is triggered by a change of 'Status' value in a custom column to 'Pending'. That kicks off a separate Approval Flow (not pictured), which should result in one of two statuses: 'Approved' or 'Rejected'.

I now want to make a separate Flow, which will chase each Process Owner who doesn't react to approval request. In each instance an email and flow bot message will be sent. Specifically:

• 1 week after initial approval request - send chaser 1
• 2 weeks after initial approval request - send chaser 2
• After that send chasers daily, only on work days (Mon-Fri), 10 times = for further 2 weeks
• After that send a different escalation email to my manager telling them that a process has been outstanding for 1 month
• Obviously, stop if process is approved/rejected in the meantime.

  I managed to get as far as chaser 1 and 2, but after this I'm stumped, because I need the remaining chasers to only be sent on work days, only 10 times and then send a different email.

  Here's my flow so far.

Any help would be greatly appreciated.

Thanks,

Agnes

[Pstork1]

In order to send the reminders only on weekdays I think you just add a select just prior to sending the reminder that checks the DayOfWeek() for today in your local timezone.  If Day is equal to Saturday then delay 2 days, if Day is equal to Sunday delay 1 day, default do nothing.  The next step will be to send the reminder.

[Agnes1]

@Pstork1  

Thank you so much for taking the time to try and help me!

It sounds like it makes perfect sense, but I've never used 'Select' action before - presumably I'd need to initlialise some variables before I can use it?

Could you please show me how you'd use the Select action in this instance?

Thanks,

Agnes

[Pstork1]

Sorry, that should have been 'Switch' not Select.  Here's a screen shot of what I mean.  In the switch use DayOfWeek to get the day number for today.  Saturday is 6 and Sunday is 7.  So if its 6 delay 48 hour if its 7 delay 24 hours.

[Agnes1]

@Pstork1 

Thank you very much. 🙂

Okay, I'm not entirely sure I know what I'm doing, but I have progressed. Here's the next bit of my flow.

1. Does this look correct to you for what I'm trying to achieve?
2. Not sure if I identified Default correctly as Mon-Fri, or if I need a separate case for each day?
3. Now how do I get it to do this only 10 times, i.e. daily chasers for 2 weeks
4. Perhaps it should actually be 9 times and on the 10th working day send an escalation email. We are now on 1 weeek delay + 1 week delay + 1 workig day x9 = so very close to flow timeout after 30 days, aren't we?

[Pstork1]

1. Yes, but I don't think you need to duplicate everything from the condition down in each switch.  The point of the switch is to wait either 24 or 48 hours if its Sunday or Saturday.  Then just do what you normally would do.

2. Default will pick up anything other than Sat and Sun.  No need to add additional switches unless you want to do different things on different days

3. Put another field in the list/file metadata and use a counter.  Increment each time in runs.  Then create a trigger condition in the settings of the trigger to not run if the counter is greater than or equal to 10

4. Same as #3 but check the counter each time and do something different if its 10

[Agnes1]

@Pstork1 

Thank you!

1. Corrected - I get it
2. Good to know I was right
3. Okay, let's see if I get this right:

a) Let's say I create an extra column in the Sharepoint Library called 'Reminders'. This will be a number field, by default 0.

b) Next, we add an action after each round of email/flow bot chasers to Process Owners to update file property 'Reminders' by increasing the value by 1 (how do I make it 'increase value by 1'?).

c) After 1st week it gets value 1. After Week 2, it gets value 2. After Week 2+1 day, it gets value 3. Etc.

d) Let's say we get to value 5. At that point the process (file) is approved/rejected. As a part of my Approval Request Flow I will need to update file property 'Reminders' back to 0.

e) I actually think it would be 12 reminders in total, not 10, wouldn't it? 2x weekly + 10x daily. Stop after 12.

f) With this next bit I get a little lost: I found trigger conditions, but I have no idea how to set it to not run if the counter is greater than or equal to 12.

g) Plus at that point the Flow will be about to time itself out anyway, right? So it's not so much that I need it to stop after 12 reminders, I just need it to NOT time out before this issue is escalated (i.e. An email to me that says: 'Hey! This Process Owner hasn't approved/rejected their outstanding process for 30 days now! Do something!')

4. Could I perhaps try and tackle this differently? As in: create ANOTHER flow, similar to this one, that just goes: 'When file properties are modified and Status is changed to 'Pending' -> Wait 29 days -> Is Status still Pending? ->If yes, send escalation email to me / If no, do nothing

[UshaJyothi20]

Hi @Pstork1 

Thanks, it helped me

I have a concern if it is Friday then what needs to be done? please 

guide me.

Thanks in advance,

Usha

[Pstork1]

If its Friday the default case will run, since Friday is a work day.  If you want to do something different on Fridays just add another case that targets that day.

[UshaJyothi20]

Hi @Pstork1 

Thanks for the response 

If it is Friday I need to put the timeout duration as 5 and satuday and Sunday should not be included.

Friday then Monday Tuesday Wednesday Thursday

I need to skip Saturday and Sunday 

How can't I do that ?

In the same way for Thursday,Wednesday.. like that

I need to calculate 5 working days and I need to put the timeout for the approval 

Is it possible?????

[Pstork1]

The answer is still the same approach.  Use a function to get the day of the week and then a Switch statement to adjust whatever needs to be adjusted in the approval for that specific day. If some days do the same thing you can just not include a case for them and use the default case to catch them. As far as the timeout is concerned, there is no way to set it to ignore certain days.  But for example, if its Friday and the timeout should be 5 business days then set the timeout for 7 days.