Microsoft Flows - Send e-mail with items that need to be reviewed

Summary of steps

Description

Recurrence - Only for testing

varApprovalRequiredItems - items that required approval and that will be sent by email to approver.

Get Request Items - Get SharePoint list items based on filter

Apply to each Pending Request - If conditional checking if request is not Data Sensitive and External request. 

If doesn't meet the condition, item will be added to array.

Condition CheckIfArrayIsNotEmpty - Send e-mail only if there are items that require approval:

Expression to check if array is not null:

length(variables('varApprovalRequiredItems'))

Microsoft Flows - Update SharePoint List Items - Target Date for next working day

Requirement

A customer has a list of new requests and one of the staff member had to manually process the incoming requests and set a Due date for an item to be processed and also automatically assign an item to a her. The request is to automatically check if a request can be set as ready to be provisioned and set a Due date.

Challenge

To determine the working dates and set dates that come on a Friday to the following Monday instead of the next day.

The Flow

I have created a Flow that is going to run on a schedule and the cool thing I find to set Flows on a recurrence is that you can select to run it only on working days if you required. I currently have another Flow that relates to this same requests process and I like the way it only runs on working days and it runs a few times a day. As this other Flow sends out emails, there is no spam sent during non working days.

I then had to Get the List Items from the Request List, filtering it by the Choice column Status:

Once I have grabbed the items, I have added a "Apply to each" action, based on the value returned. I have also added a condition, the items could be processed only if the "Data Sensitive" column was not set to "Sensitive Data". Once that was in place I then added an "Update Item" action like below:

As the final formula is based on the current date of the processed Item, I needed to have a way of testing the functionality and see how the formula would work on Fridays and Saturdays. So I temporarily created a Date column called 'TempCreated'.

The formula below has 2 IF statements, if a request comes on a Friday, it will add 3 days to the value, IF it comes on a Saturday, 2 days will be added. Else, 1 day will be added and the request target date will be set to the next day.

if(equals(dayOfWeek(items('Apply_to_each')?['TempCreated']),5), addDays(items('Apply_to_each')?['TempCreated'],3), if(equals(dayOfWeek(items('Apply_to_each')?['TempCreated']),6),addDays(items('Apply_to_each')?['TempCreated'],2),addDays(items('Apply_to_each')?['TempCreated'],1)))

The next step now is to be able to determine the public bank holidays.

Final Formula

The final version of the working formula is below:

if(equals(dayOfWeek(utcNow()),5), addDays(utcNow(),3),if(equals(dayOfWeek(utcNow()),6),addDays(utcNow(),2),addDays(utcNow(),1)))