sending a reminder email for three CONSECUTIVE WEEKDAYS only in Power Automate flow

[livingstonep]

hi,

I am building a flow that will send a reminder to users for three CONSECUTIVE WEEKDAYS ONLY ...

The flow will execute on a schedule, weekdays only, and a Get Items step retrieves all the records from the last 7 days with

CABType eq 'Standard' and CABStatus eq 'Submitted' .

How can I use the DayOfWeek function in a Loop / Apply to Each to send the reminder so that reminders are sent for three weekdays following the   ImplementationDateTime (which is a Date and Time field) 

I need to calculate three days after that date, but exclude weekends

So the rules are:

• if the ImplementationDateTime was a Friday, reminders would be sent Mon, Tue, Wed
• if the ImplementationDateTime was a Thursday, reminders would be sent Fri, Mon, Tue
• if the ImplementationDateTime was a Wednesday, reminders would be sent Thursday, Friday, Mon
• if the ImplementationDateTime was a Tuesday, reminders would be sent Wednesday, Thursday, Friday
• if the ImplementationDateTime was a Monday, reminders would be sent Tuesday, Wednesday, Thursday

how can I do that in the loop / apply to each please?

[Andrew_J]

@livingstonep 

Your flow will not work in the way you have it at the moment.

I am also concerned that the filter query below has to many items in it, I do not think you can have the number you have.

I do not fully understand the highlight above. 

I would suggest that you need to look a a number Parralle Branches or a SWITCH with a CASE for each day of the week, then within each day you need to have three emails with a delay of 24 hours between.

I am going to leave you to play around with your flow to see if the Paralle Branch or SWITCH works better.  Is there anyway you can add a column to your list that sets the day of the week before you do anything else. 

Sorry the above may seem a little jumbled up, it would be good to see the whole flow to fully understand what you are doing.

Regards,

Andrew

[livingstonep]

thanks for your help, 

The Get_Items step works fine likw this:

so I will leave that.

The remainder of the flow will just determine whether an email reminder is being sent based on the current DayOfWeek (Today) compared with the DayOfWeek value of the ImplementationDateTime  field

So a Switch may be the way to go - but do you have any advioce on the best way to implement it?

[Andrew_J]

@livingstonep 

I toughout I would educate myself and would suggest the below:

The expressions are:

dayOfWeek(triggerOutputs()?['body/Date'])

int(outputs('Update_item')?['body/Day'])

A test on 22/04/2024

If the day of the week equals 2, 3, 4, or, 5 the each case should fire correctly.

I have not filtererd any data as I do not have any to filter.

Regards,

Andrew

[livingstonep]

thanks.

The way we decided to do it is Switch on DayOfWeek (1-5) of the field we want to compare with in the SharePoint list

inside each Switch Case, we set a boolean variable with formula like this, changing for each day:

@{
	or(
		equals(DayofWeek(utcNow()), 1)
		, equals(DayofWeek(utcNow()), 2)
		, equals(DayofWeek(utcNow()), 3)
		)
	}

of course, that's for Monday - the key is that we return true/false for each day, giving us 3 working days following the CompletionDateTime value of the current item in the loop

[livingstonep · Accepted solution]

it can also be done like this, without a switch, setting a boolean variable controlling whether the email is sent:

@{or(
	and(
		equals(DayofWeek(variables('dteCompletionDate'), 1))
		, or(
			equals(DayofWeek(utcNow()), 2)
			, equals(DayofWeek(utcNow()), 3)
			, equals(DayofWeek(utcNow()), 4)
			)
		)
	, and(
		equals(DayofWeek(variables('dteCompletionDate'), 2))
		, or(
			equals(DayofWeek(utcNow()), 3)
			, equals(DayofWeek(utcNow()), 4)
			, equals(DayofWeek(utcNow()), 5)
			)
		)
	, and(
		equals(DayofWeek(variables('dteCompletionDate'), 3))
		, or(
			equals(DayofWeek(utcNow()), 4)
			, equals(DayofWeek(utcNow()), 5)
			, equals(DayofWeek(utcNow()), 1)
			)
		)
	, and(
		equals(DayofWeek(variables('dteCompletionDate'), 4))
		, or(
			equals(DayofWeek(utcNow()), 5)
			, equals(DayofWeek(utcNow()), 1)
			, equals(DayofWeek(utcNow()), 2)
			)
		)
	, and(
		equals(DayofWeek(variables('dteCompletionDate'), 5))
		, or(
			equals(DayofWeek(utcNow()), 1)
			, equals(DayofWeek(utcNow()), 2)
			, equals(DayofWeek(utcNow()), 3)
			)
		)
	, and(
		equals(DayofWeek(variables('dteCompletionDate'), 6))
		, or(
			equals(DayofWeek(utcNow()), 1)
			, equals(DayofWeek(utcNow()), 2)
			, equals(DayofWeek(utcNow()), 3)
			)
		)
	, and(
		equals(DayofWeek(variables('dteCompletionDate'), 0))
		, or(
			equals(DayofWeek(utcNow()), 1)
			, equals(DayofWeek(utcNow()), 2)
			, equals(DayofWeek(utcNow()), 3)
			)
		)
	)
}