Top
Photo by Luis Villasmil on Unsplash

Working with approval tasks in Dataverse


This post is inspired by some recent talks I have delivered. I will describe you deeply how approval tasks are organized in Microsoft Dataverse and what can we do with them.

The basics

Power Automate and Microsoft Teams approval tasks are stored in Microsoft Dataverse. The database related to the environment, where a task is created.

Good to know! Tasks are always stored within the Microsoft Dataverse in the environment, where they are created. Therefore there is no “one-place-to-rule-them-all” available as of today.

Dataverse tables structure for approvals
  • Flow approval – stores basic information about each approval like when it was created and by whom. Also, in case it was created by an action that as well waits for an approval, it stores Flow Notification URI, which is used then to complete waiting on the action,
  • User – in Dataverse all users’ objects are stored inside this table, it is synced with a list of users who has access to the environment which is synced with Azure AD,
  • Approval – header table of all approvals. It stores approval name, requester, details of approval,
  • Approval request – stores info about each single task generated for the approval (the more assignees, the more tasks),
  • Approval response – stores info about each completed task, like the outcome.

So now we know the basics. Anytime a task is created in either Power Automate or Microsoft Teams, process creates entries in Flow approval, Approval and Approval request tables. Let me now tell you what actually is being created per each scenario.

Task created

With each new task data is created in 3 tables:

  1. Approval
    1. Approval – autogenerated ID
    2. Allow Reassign – boolean
    3. Approval Model Id and Approval Stage – has the same value as Approval
    4. Created By – who created the approval
    5. Created On – timestamp
    6. Details – markdown contents of the task description
    7. Modified By – by default the same user who created
    8. Modified On – by default the same date as Created On
    9. Owner – again, user who created
    10. Send Email Notifications – boolean
    11. Stage – Basic
    12. Status – Active
    13. Status Reason – Pending
    14. Title – title of the task
  2. Flow approval
    1. Flow Approval – the same identifier as generated for Approval, inside Approval table
    2. Created By – it will be Microsoft Flow
    3. Created On – timestamp
    4. Flow Id – it seems to be flow id, but used internally
    5. Flow Name – guid of the flow where the task was created
    6. Flow Notification URI – URL that once called (POST) completes waiting action
    7. Flow Run Sequence Id – that is ID of the instance, that where the task was created
    8. Modified By – as Created By
    9. Modified On – as Created On
    10. Owner – who created task
    11. Send Flow Email Notification – boolean
    12. Send Flow Push Notification – boolean
    13. Status – Active
    14. Status Reason – Created or WaitingForApproval (if action waits)
  3. Approval Request
    1. Approval Request – autogenerated id
    2. Allow Reassignment – boolean
    3. Approval Id Index – identifier of the Approval, generated in Approval table
    4. Approval Stage – as above
    5. Created By – who created task
    6. Created On – timestamp
    7. Last Notified On – if notification about task assignment was sent – when
    8. Modified By – by default the same as Created By
    9. Modified On – by default the same as Created On
    10. Owner – who created task
    11. Response options – list with possible outcomes
    12. Stage – Basic
    13. Stage Index – Basic
    14. Status – Active
    15. Status Reason – Active

Lots of fields to be set, but most of them is updated automatically based on the connection that is used to create records in Dataverse.

Tip! In Flow Approval table, for approvals generated with actions that wait for approval, you can find links inside Flow Notification URI column. This URL can be used to submit a POST request, that simply completes the waiting action. It is built from the following pieces:
https://[instance].westeurope.logic.azure.com/workflows/[Flow Id]/runs/[Flow Run Sequence Id]/actions/[Action name that waits for approval]/run?api-version=2016-06-01&sp=%2Fruns%2F[Flow Run Sequence Id]%2Factions%2F[Action name that waits for approval]%2Frun%2C%2Fruns%2F[Flow Run Sequence Id]%2Factions%2F[Action name that waits for approval]%2Fread&sv=1.0&sig=[some signature key]
Moreover, along with the POST, you can submit a body contents of your choice, so simply pass all the information you need after waiting is completed (like outcome, comments etc…).

Task completed

  1. Approval
    1. Completed On – timestamp
    2. Modified On – timestamp
    3. Modified By – who completed the task
    4. Result – string, the outcome
    5. Stage – Complete
    6. Status – Inactive
    7. Status Reason – Completed
  2. Flow Approval
    1. No changes, if no action waits for approval, if yes:
    2. Status – Inactive
    3. Status Reason – Completed
  3. Approval Request
    1. No changes
  4. Approval Response
    1. Approval Response – autogenerated identifier
    2. Approval Id Index – Approval identifier generated in Approval table
    3. Approval Stage – as above
    4. Comments – any comments written along with approval
    5. Created By – Microsoft Flow
    6. Created On – timestamp
    7. Modified By – Microsoft Flow
    8. Modified On – timestamp
    9. Owner – who completed task
    10. Response – outcome
    11. Stage – Basic
    12. Stage Index – Basic
    13. Status – Inactive
    14. Status Reason – Committed

Task reassigned

In this scenario, the only change that takes place is present inside Approval Request table:

  1. Original task
    1. Status – Inactive
    2. Status Reason – Reassigned
  2. New created entry
    1. All data the same as when a new task is created plus
    2. Reassigned From – identifier of the original Approval Request entry

Important! When Reassigning tasks, be sure to follow the principles:
1. Users can’t reassign tasks not assigned to themselves,
2. Users can’t reassign to the themselves,
3. Users can’t reassign to task’s creator.

Task canceled

  1. Approval
    1. Result – Canceled
    2. Stage – Complete
    3. Status – Inactive
    4. Status Reason – Canceled
  2. Flow Approval
    1. No changes, if no action waits for approval, if yes:
    2. Status – Inactive
    3. Status Reason – Completed

And that’s it. I hope you will find it really useful when working with tasks outside of the basic UI in Power Automate or Microsoft Teams.


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.

15 Comments
  • Joseph

    Dear Tomasz and many thanks for sharing your knowledge. However, i found it difficult to implement this. Could share a practical scenario so i apply as directed. thanks

    February 19, 2022 at 1:01 am Reply
  • Natalia

    Hi Tomasz, great work here.
    I’m trying to make this solution work. I want to quit the approval by updating the tables and I succeed BUT: in teams status remains as Requested (in all 4 tables, in power automate approvals and in na email notofication status is changed to Approved, only in teams it remains as requested but it is final status so user cannot do anything else there).
    With POST I have a problem that I don’t know how can I get the signature key. By checking the approval table i’ve noticed that this field is filled with unique key for each entry.

    April 7, 2022 at 1:18 pm Reply
    • Tomasz Poszytek

      Hey, thank you! Basically once you do updates in all required tables (especially inside Approval table) the status in Teams is changed as well. Jsyk – tested a moment ago. With the POST request – where do you need the signature key? Flow Notification URI is complete – you should just use it to make a POST request.

      April 12, 2022 at 8:12 am Reply
  • Sagar

    Hi Tomasz,
    I am creating a Power app through which I need to approve or reject approval flow by just clicking approve or reject button in Power app. Is there any way to do this.

    April 10, 2022 at 7:40 am Reply
    • Tomasz Poszytek

      Yes, simply follow the steps I described – you need to update specific tables and in the end send POST request to FlowNotificationURI.

      April 12, 2022 at 8:03 am Reply
  • Zach Bean

    This is great! Is there a way to grab requestor, basically sent on behalf of details. Seems those are null but we are using it a lot since we dont want each approval to look like its coming from the service account only.

    April 11, 2022 at 7:44 pm Reply
    • Tomasz Poszytek

      When you fill in the “Requestor” column, then this person is saved as the “Owner” of the record inside “Approval” table. If Requestor is not filled, then it the account that executes action will be used – I think this is what you’re looking for.

      April 12, 2022 at 8:15 am Reply
  • Iain Ray

    Hi Tomasz, thanks for the breakdown above, been looking for when an approval is created where to find the Approvers, i can’t seem to find them when an approval has been created and pending, only after a response has been added to the table. An ideas?

    But i want to build a list of what people have to approve in Sharepoint for a counter on our intranet, rather than having to look in Teams Approvals.

    Thanks

    April 12, 2022 at 2:10 pm Reply
    • Tomasz Poszytek

      Hey, all approvers can be found inside Approval Request table. There are entries generated per each task in the process.

      April 12, 2022 at 2:21 pm Reply
  • Sharon

    Hi Tomasz, In the Approval Response table Owning User is Blank. I cant find any of my Approvers anywhere in the table. Am I supposed to add the “Owner” column somehow?

    April 26, 2022 at 2:21 pm Reply
    • Tomasz Poszytek

      You should have data in “Owner” and “Owning User Index” columns. These two columns store information about user who completed task.

      April 26, 2022 at 2:44 pm Reply
      • Sharon

        I do not have “owner” I only have “owning User” and of all the columns – that one is blank! The Response column is populated and its shows Approve, Reject & Pending But the Owner is blank! It’s so odd & Frustrating. The information you provided on this webpage is fantastic and very helpful. Thank you. I just wish that one column would populate! Without the former “task” list I need to find a way to track the approval status.

        April 26, 2022 at 4:58 pm Reply
        • Tomasz Poszytek

          Thanks for your kind words. I was thinking, that maybe you don’t have a correct security role assigned, that maybe columns Owner and Owning User Index have column security enabled, but that’s not a thing in my case. My account has “System administrator” security role assigned. What role has your account?

          April 26, 2022 at 10:12 pm Reply
  • Sharon

    I agree with you! I have not been able to figure out what my assigned security role is. My hunch is that I and NOT set to system administrator!

    May 6, 2022 at 8:56 pm Reply

Post a Comment

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