Top

Substitutions in Microsoft Flow


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:

Common Data Services schema for Microsoft Flow approvals

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”:

WHere to find Owner in CDS

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:

Combobox using User entity as 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:

Entity keeping information about substitutions

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

Substitutions Microsoft Flow
  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!


Tomasz Poszytek

Hi, I am Tomasz. I am expert in the field of process automation and business solutions' building using Power Platform. I am Microsoft MVP and Nintex vTE.

16 Comments
  • Bartosz Domżalski

    Great post revealing the way and details about how to reassign/replace approver. For the whole picture about the automation of out of office approver substitution I miss a nice small bit – checking for email out of office auto-reply (like https://powerusers.microsoft.com/t5/Building-Flows/Automatically-reassign-an-approval-when-someone-is-out-of-office/td-p/243773).

    I tried to implement approver replacement but had no Approval Records in the CDS. Has that changed since the time of the post? Should those records be created by the same action as the Approval record? Maybe some additional conditions must be met?

    April 30, 2020 at 6:25 pm Reply
    • Tomasz Poszytek

      You can’t see them in Power Automate, when you are using CDS actions or you can’t see them under “Entities”? If the second one, be sure you have the selection for view set to “All” above the list of entities, that by default is set to “Default”.

      April 30, 2020 at 10:40 pm Reply
      • Bartosz Domżalski

        I see the Approval Request entity in CDS but no record in “Data” section. Also the action GetItems_V2 in flow (I used the “Get an email report of approvals waiting for my response” template) gives empty collection.

        May 2, 2020 at 9:05 am Reply
        • Bartosz Domżalski

          I’ve wrongly assumed that if I see Approval entities I will also see Approval Request ones. It was a matter of access rights…

          May 3, 2020 at 9:31 pm Reply
          • Tomasz Poszytek

            I’m glad you found it 🙂 I thought it was a matter of the view in the first place, but that’s right – access rights to entities is also important.

            May 3, 2020 at 10:30 pm
  • Evan

    Hi Tomasz,
    This is nice, I like your style
    may I ask about the column in the Approval Requests entity called msdyn_flow_approvalrequestidx_reassignedfromid
    It seems that , for a re-assignment, this column is important for the auditing among other things, at least according to this article here:
    https://2die4it.com/2019/04/20/reassign-flow-approval-request-from-the-actionable-message/
    In that approach they actually created a brand new approvalRequest…I like your approach better because its easier…I just wanted to get your thoughts on the matter,

    June 10, 2020 at 12:22 am Reply
    • Tomasz Poszytek

      Hi Evan! So I somehow agree, that the proper way to re-assign a task to a user is to simply create new record, with ID related to that previous task, with proper statuses etc… I have also done an app for that and it’s working like a charm. This approach would also help to eg. track then reassignments for audit purposes. The approach I described above is just much simpler and faster, since you only need to actually change assignee. Both will work. Question is what is your goal 😉

      June 10, 2020 at 11:24 am Reply
  • Ryan

    Hi, Tomas. Is there a way to retrieve the flow approval / run history via API or Power BI ? Also, can we directly trigger Flow Approval via API not via Email or Flow Portal. Thanks!

    June 23, 2020 at 10:49 am Reply
  • Steve Zhang

    Hi Tomas. I tried your task reassignment apps, it works very well expect ALL MUST APPROVER / ALL MUST RESPONSE. it looks like Entity APPROVAL result will only be changed after all child tasks were completed which means the child tasks will always under Active task even it was responded. I can see there is Request ID and Response ID but not sure how to filter out all finished requests.

    July 21, 2020 at 8:03 am Reply
    • Tomasz Poszytek

      You need to query responses. Then filter requests for which a response exists. Those are completed.

      July 28, 2020 at 7:46 am Reply
  • Yash

    What we have in place is a approval task in powerautomate which contains contents and attachments for the email that goes out. How can we trigger that when we reassign the approval task?

    September 8, 2020 at 4:03 pm Reply
    • Tomasz Poszytek

      You should look at custom connector made by Daniel Laskewitz (https://github.com/Laskewitz/Approvals) and doing re-assignment using the Flow’s endpoints. What I am showing in the article is a “brut-force” approach. It re-assigns task, but since it operates on a CDS level, it doesn’t trigger sending of emails.

      September 10, 2020 at 2:14 pm Reply
  • Yash

    Hi,

    I am not sure but my previous query got removed.

    Quick one for you, if we re-assign workflow using flow.microsoft.com it sends an email to the assignee, however, when we do it using the method in your post the email does not get sent out. Any ideas why that would be?

    Cheers

    September 10, 2020 at 8:24 am Reply
    • Tomasz Poszytek

      Hey, I replied in my previous comment. My approach is a “brut-force”. It re-assigns task, but since it operates on a CDS level, it doesn’t trigger sending of emails. Check Laskewitz’s swagger.

      September 10, 2020 at 2:14 pm Reply
      • Yash

        Thanks for your response. Maybe caching issue. I will try and will let you know if i get success.

        September 10, 2020 at 9:41 pm Reply

Post a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.