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.

54 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
    • Tomasz Poszytek

      Please navigate to my YouTube channel: https://youtube.com/TomaszPoszytek – you can find there practical scenarios 🙂

      March 4, 2022 at 4:15 pm Reply
      • Denny OY

        Hi Tomasz ,
        I’m working on a approval flow where I need to update the approval action outside the approval flow (using Powerapps ). After watching your videos on YouTube, I can honestly say that you created a work of art.
        I’m new to this field, so could you be able to share me a full account of each step made in that flow.

        November 21, 2022 at 6:35 am Reply
        • Tomasz Poszytek

          Thank you. Speaking about sharing – I believe steps described and shown in videos are enough to make it possible to re-create them.

          December 14, 2022 at 10:22 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
  • Marko Obradovic

    Hi Tomasz, thanks for a great and helpful article. I tried to recreate the same scenario, and only thing I couldn’t achieve is to generate HTTP URI. Columns that I couldn’t find in dataverse were: instance, Action name that waits for approval, Some signature key. Any assistance from your side is extremely beneficial. Thanks in advance

    June 28, 2022 at 11:11 am Reply
    • Tomasz Poszytek

      You should check what environment roles does your account have. It should have “Approvals Admin” as far I know, so that you will have access to all the columns.

      July 15, 2022 at 11:58 am Reply
  • Daniel Westerdale

    Hi Tomasz. is the row data held in these tables purged after 30 days, as I have been given a requirement to run BI reports over the related approval tables ? I guess the alternative is to define our own db schema to replicate the approval tables, but only for certain approvals in our environment.

    July 15, 2022 at 9:38 am Reply
    • Tomasz Poszytek

      From what I know Approval tables are not having any retention policies, so data is stored there forever.

      July 15, 2022 at 11:27 am Reply
      • Daniel Westerdale

        Thanks for confirming.

        July 15, 2022 at 2:41 pm Reply
  • Karthik

    Hi, Tomasz. Thanks for the wonderful writeup, the task completion worked like a charm! I’m now working on the re-assign function, and have managed to add a new record with the target owner to the approval request table. However, this doesn’t show up on the Approvals page of the target owner. Any thoughts?

    My account has sys admin and approval admin rights. And, as I understand, re-assigning the task this way doesn’t trigger a notification to the user, or is there a way to it?

    September 6, 2022 at 9:55 am Reply
    • Tomasz Poszytek

      The reassignment trigger notification although it is sent with some random delay. Also, please check if the new assignee has the “Approval user” security role assigned.

      October 3, 2022 at 1:39 pm Reply
  • Sarah Cool

    Hi Tomasz, I tried to reassign approval tasks with dataverse and power automate. When I check my sent approvals, I can see the reassignment, but the person who is supposed to get the reassignment task, is not seeing the approval in the teams app, nor in the power automate approvals. He did get an email notification after one day.
    Can you please help me what I could be doing wrong?

    September 21, 2022 at 2:15 pm Reply
    • Tomasz Poszytek

      Can you check and confirm the user has Approval User env. security role assigned?

      October 3, 2022 at 1:26 pm Reply
      • Aylin

        Hi Tomasz, i have the same problem like Sarah and i checked that the user has Approval User env. security role assigned.

        December 5, 2022 at 12:00 pm Reply
        • Tomasz Poszytek

          Is it a custom environment or the default one?

          December 14, 2022 at 10:29 am Reply
          • Aylin

            it is a custom environment

            December 16, 2022 at 8:01 am
          • Tomasz Poszytek

            Could you grant them the “Basic User” env. role and if that doesn’t help – system customizer and check then?

            January 15, 2023 at 9:46 pm
          • Aylin

            Hi Thomasz,
            the problem is that the reassigned user has access to the item in the Approval Request list, but no access to the main item in the Approval list. So the main item is not shared with the user.  
            How can i assign the user the “Record was shared with me” permissions.

            December 16, 2022 at 1:14 pm
          • Tomasz Poszytek

            oh, that’s the good point. Didn’t know about it! Well, you can use the actions from Dataverse “unbound action” and then select grand access.

            January 15, 2023 at 9:49 pm
  • Gabriel Oxobi

    Hi Tomasz, I´m trying to develop a flow that gets triggered when a new row is added to Approval Requests table. It works fine when I create the approval, but it does not triggers when I reassign the approval to another person. I´m trying to obtain the data when person A reassigns the approval to B, I want to get the name and email of this last one to update the info in a Sharepoint list but I´m not able to do it. In every test I do, the result is my name and email.

    October 28, 2022 at 10:06 pm Reply
    • Tomasz Poszytek

      Because the reassignment doesn’t affect the Approval table. It is purely happening in Approval Request table.

      December 14, 2022 at 10:13 am Reply
  • Eric Mases

    Nice overview, thanks a lot!
    I am looking for a way to trigger the Follow up function which is available to Sender from the Approvals app.
    Doesn´t appear doable from the Dataverse table, only solutions I find are either to send Reminder emails (which doesnt contain the actionable message) or to Timeout the approval and create new (which spams the Sender of approvals).
    Any thoughts to share?

    November 18, 2022 at 2:43 pm Reply
    • Tomasz Poszytek

      Well, with Follow up there’s nothing oob as you noticed. And it’s either building your custom solution, that may include actionable messages too and a custom flow to complete tasks, or no actionable message, or the timeout to start approval again.

      December 14, 2022 at 10:20 am Reply
  • Nikhil

    Hi Tomasz, to update the Dataverse we need a premium license right. So is there a way to do it without premium license?

    May 5, 2023 at 9:35 am Reply
    • Tomasz Poszytek

      I think you answered yourself this question 🙂 Nope, you need premium license. Or use Logic Apps, but then you need Azure subscription.

      May 27, 2023 at 8:29 pm Reply
  • Clark

    Hi Tomasz, thank you for your sharing.
    I noticed that on the approval flow there is a “Name” column with blank value. May I know why does it blank?
    And if I want to delete records in Approval table (All of them), does it have any effect to my approval system?

    May 16, 2023 at 3:06 pm Reply
    • Tomasz Poszytek

      That is a good question why this column, despite that it’s required, is blank. No idea though 😉 Speaking about data deletion – nope, it has no impact on the approvals solution. You are just going to delete data.

      May 27, 2023 at 8:35 pm Reply
  • Sam Norman

    Hi Tomasz!

    Really appreciated and helpful article. I’m trying to reassing some approvals. When creating the new approval record in the approval requests table, I’m getting this error response:
    “A record already exists with the attribute values Index id. Approval, Owner User Index, Reassignment Source Index, Phase Index. The Uniqueness Constraint Index entity key requires that this set of attributes contain unique values. Please select unique values and try again.”

    However, the row is created on the table and I can see the the new approval request in the approvals tab of the new owner. Anything I’m missing out?

    Thank you!

    June 5, 2023 at 5:05 pm Reply
    • Tomasz Poszytek

      I actually haven’t seen such an issue. I may understand the reason why it’s showing up. Maybe you are not deactivating the existing record first?

      June 29, 2023 at 10:42 am Reply
  • Dmitry

    Hi Tomasz

    I was able to do everything you talk about here. Big koodos to you for making this post!

    The only thing I can’t figure out is when completing the task normally (from teams or outlook) once the task is completed, you get a teams notification (as a requestor) reading “Final Status: Completed”.

    When I do it with your method, the task completed normally and approval wait action is completed ok, but I do not get the “Final Status: Completed” notification.

    I am wondering if you have encountered anything similar.

    Appreciate your help!

    July 6, 2023 at 7:42 pm Reply
    • Tomasz Poszytek

      Yes, unfortunately you won’t be seeing that “natural” behavior when completing tasks via Dataverse manipulation. There is possibly something more happening on the API level, that I am not aware of.

      August 2, 2023 at 1:07 pm Reply
  • Shyam bhandaru

    Hi Tomasz,
    I am updating the Approval, response, and flow table using power app. working fine but the Avtivty card is not showing the response . Srill showing “Pending response”

    thanks,
    Shyam

    September 5, 2023 at 4:39 am Reply
    • Tomasz Poszytek

      What do you mean by “activity card”? What do you want to achieve?

      September 28, 2023 at 7:27 pm Reply
  • Ray Garza

    Hi Tomasz,

    Is there a tool to perform Approval audits, or does one need to be created?

    For instance, I have an application and send approval notifications to five departments. I process the responses as they arrive. Later, I need a report of when the approver was notified, when they responded (if at all), and what the response was.

    Thanks,
    Ray

    October 24, 2023 at 2:11 am Reply
    • Tomasz Poszytek

      Hi, there is no tool that provides such functionality that I know. I suppose it can be done using a Power Bi report on top of Microsoft Dataverse.

      January 13, 2024 at 7:44 pm Reply
  • Praveen B

    Hi Tomasz, I tried to reassign approval tasks with dataverse and power automate. When I check my sent approvals, I can see the reassignment, but the person who is supposed to get the reassignment task, is not seeing the approval in the teams app, nor in the power automate approvals. The person is having admin role, approver user role as well as system customiser role.

    December 28, 2023 at 1:46 pm Reply
  • Stephanie Anderson

    Hi Tomasz,

    I am trying to update an approval to be set as expired when my flow hits the 30 day time out threshold.
    Is there a way update the approval to show as expired though the Dataverse?
    I tried setting the Status Reason on the Approval table to Expired but it is still showing as the normal pending status to the users.

    Thank you

    January 5, 2024 at 7:11 pm Reply
    • Tomasz Poszytek

      Nope, there’s no such thing as “Expired” in Dataverse. No such status. Tasks in Dataverse do not expire, those are only Power Automate flows that do. You can only set it’s status to Inactive, so that it’s not visible to users.

      January 13, 2024 at 7:51 pm Reply
  • Nanette

    Great article, Tomasz! This will go a long way for me to update the status of the task without using the OOB approval action in Teams.

    However, I do have another question.

    How can I find the name of the person the task is assigned to? In my scenario, I have a workflow that assigns a task and needs to send a reminder to the task owner a few days later. But what if the owner reassigned the task? How can I find the name of the current assigned user so I can send the reminder to the right person?

    March 5, 2024 at 4:23 am Reply
    • Tomasz Poszytek

      This is always the “Owner” of the Approval Request record.

      March 17, 2024 at 11:03 pm Reply

Post a Comment

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