The post you are reading now was inspired by a recent question I saw on my Twitter. User was asking if it is possible to reassign a task to another employee if the current assignee is out of office without an access to their Office 365. I found this question as a direct link to a well known functionality of business applications called “substitutions”.

Substitutions is a functionality that can be implemented in various ways, but in the end it has just one purpose – to tell the system who it should assign that particular task if the original assignee is out of office.

Then scenarios are various – should it assign a task to the original assignee and their substitutions, or maybe just to the substitute. Should it wait then for all answers or just the first. If done after a task is assigned, should it also notify the new assignee about a task or maybe not. Maybe it should also notify the original assignee that their task has been reassigned. Number of questions really increases the more you go into company’s procedures and policies, however one thing remains the same – the task must be assigned to a different person, than it should be originally.

First – the screencast of the solutions

Before I really move into details please have a look at the screencast I have recorder for you to show you the described solutions.

Substitutions in Microsoft Flow

Important! The below solutions require you to have a P1 subscription for Microsoft Flow (and PowerApps if you want to build app), so that you can get access to Common Data Services (CDS)

Common Data Service

For those of you who don’t know, all approvals in Microsoft Flow are stored in Common Data Services. Below is a simple schema of the relationships between entities taking part in this process:

Let me shortly describe you the entities:

1. Approval – it is the main entity. It stores “header” information for approvals, like a title, creation date, who created and who is set as the requester, also the results: when it was completed, what was the outcome.
2. Approval Request – this is the entity that stores every created task’s details – to whom is assigned, when, what are the possible outcomes, what are the details. The more assigness you have in your task, the more entries here will be created. Actually – one per each assignee.
3. Approval Response – this entity keeps all the information about the completed tasks. If the task requires everyone to respond, here you will find every single completed task details. If only the first one – there will be just one entry.
4. User – this is the crucial entity. It maps AAD users to records used inside CDS. Basically every user object in CDS is not recognized using its e-mail address, but unique GUID, that is kept in Users entity. So to know who is assigned a task, you need to join that entity with Users and discover user data using their GUID.
5. Flow Approval – that entity keeps details about assigned tasks from Flow’s perspecitve. It will give you information about Flows’ which have assigned tasks – their GUIDs, names etc…

Having that written, you see that the entities substitutions (or task reassignment) need are Approval and Approval Request.

How to reassign a task?

The answer is very simple. In “Approval Request” entity there is a column called “Owner”:

Replacing value in this column with a value looking up a different user will let you to reassign a task to someone else.

The easiest way is to open the entity in Excel file and then simply to modify column’s value by selecting a different user from the list of available:

Using this approach you can change the assignee even if they are not present at the office and unable to do it on their behalf.

PowerApps approach

The PowerApp you’ve seen in the screencast is available here: https://powerusers.microsoft.com/t5/Community-Apps-Gallery/Approval-tasks-reassignment-app/td-p/342522.

This is just a very simple application, that has three entities from CDS configured: User, Approval and Approval Request. Onstart it is getting data from these entities and saves them to collections, because having them in app gives a little boost to performance, but also allows me to use expressions that are not possible to delegate. Operation uses also the “concurrent” function to make the loading even faster:

Concurrent(
    ClearCollect(
        ActiveApprovals,
        Filter(
            Approvals,
            IsBlank(Result)
        )
    ),
    ClearCollect(
        ActiveAssignments,
        'Approval Requests'
    )
)

Then it is built from three areas. First is gallery of currently existing, not completed tasks, built using the expression:

SortByColumns(Filter(ActiveAssignments, 'Approval Id Index' in ActiveApprovals.msdyn_flow_approvalid), "createdon", Ascending)

The name of assignee is displayed using the below expression:

"Assigned to: " & AsType( ThisItem.Owner, [@Users] ).'Full Name'

I had to use function “AsType” because Owner is a polymorphic lookup – the same column can be looking up Team and User (https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/working-with-references).

Second is an area showing information about current assignee. Each label is using the same, “AsType” function to get information from CDS about a user, e.g.

AsType( Gallery1.Selected.Owner, [@Users] ).'Full Name'
Office365Users.UserPhotoV2(AsType( Gallery1.Selected.Owner, [@Users] ).internalemailaddress)

Third is a combobox with a list of other users that the task can be reassigned to. It is using “User” entity as a datasource:

Below there is a card showing information about selected user. Because the selected item in combobox is an object, labels are simply using that reference to get details from the User entity, e.g.

DataCardValue3.Selected.'Full Name'

At the bottom you can find button to replace. It is simply patching the selected in gallery record with the new data and overwriting the “Owner” value:

Patch(
    'Approval Requests',
    LookUp(
        'Approval Requests',
        'Approval Id Index' = Gallery1.Selected.'Approval Id Index'
    ),
    {Owner: DataCardValue3.Selected}
);

That’s it! Now let’s move to Microsoft Flow.

Microsoft Flow approach

Basically this is the solution that can be named a substitutions mechanism.

It is working on top of a data source that should keep information about the substitutions, like CDS entity in my example. It should at least know who is absent, who should be substituting, from when and until when:

The Flow itself should be triggered on schedule, e.g. every day after midnight and then:

1. It should calculate current date so that it then
2. can get a list of substitutions starting from the given date – using the filter query:
   
   
3. Next it should get list of not completed tasks – those that don’t have any value in “Result” column:
   
   
4. Next Flow is getting a list of all assigned tasks from Approval Request entity.
5. Now I want to create an array that contains only GUIDs of the approvals that are not completed. To do that I am using action called “Select” (under “Data Operations”). As “From” I’m using the “value” from step no. 3 and as “Map” I’m using “Approval” also from step no. 3:
   
   
6. Next I am using the “Filter” action to leave only those items from the “Approval Request” entity – so only those assigned tasks that are not yet completed – which GUIDs are on the list from step no. 5:
   
   
7. Next, for each substitution from step no. 2…
8. I am leaving only the records, that have “Owner” the same as “SubstituteFor” – so that I filter out list of assigned tasks from step no. 6 removing all tasks not assigned to the user who should be substituted in current step of a loop:
   
   
9. And then finally, for each such task…
10. I am replacing original “Owner” with a value from Substitutions entity:
    

And that’s basically all! The flow I described above is simply used to replace current assignees with the other employees based on the defined rules in the “Substitutions” entity. You can call it MVP (Minimum Viable Product). However it can be used to not only replace, but also to change assignee to a different person in a moment when a task is just about to be assigned.

Also, what can be added here is a set of notifications, so that the new assignee (if a task is reassigned) will be informed about new task to complete, as well as the original assignee can be informed about a successful substitution. Moreover, what can be done, is that after the time of substitution is passed, Flow could reassign back all tasks from the substitute to original approver.

I hope you will find this description very useful. Share, comment, any feedback highly appreciated. If you have any questions about the implementation or would like to ask for help – contact me!