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][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.

No Comments

Post a Comment

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