Demystifying Modern Approvals Data Storage In Dataverse

Introduction

We all have been using modern approvals for the last few years now. It improves every month as new features are incorporated into it. Approvers can act from outlook, teams or even from FLOW mobile app. Approvers can make use of any kind of device such as a laptop or mobile device to take action and it works the same.

There are some scenarios or requirements where we need to write custom code to manipulate the approvals like canceling approval or approving on behalf of someone or assigning it to someone else automatically or handling scenarios of approver out of reach for few days.

To handle all such scenarios, you first need to understand where and how modern approvals data is being stored in the backend.

Details

As you know every instance/subscription of office 365 comes with a default environment, and the default environment includes a default Dataverse (previously known as Common data service) database into it. Microsoft uses Dataverse as data source for many applications such as D365 modules, PowerApps portal, power virtual agent, etc.

Similarly, Microsoft is storing Modern Approvals data in Dataverse. If you want to know more about CDS/Dataverse and its structure then please visit this article – Overview of Dataverse in Power Platform.

In Dataverse, modern approvals data is being stored mainly in three tables

• Approval
• Approval Request
• Approval Response

To see this in live, navigate to PowerApps maker portal -> From left side navigation expand Dataverse -> click on tables -> from right top corner change the filter to Show All tables -> in search text box type approval. -> You can see the approvals table as shown below

Approval table

A new record gets added to this table whenever a modern approval action gets executed in the FLOW. For each execution new row will get created.

To see the data stored in the table -> select the table -> Click on last tab Data -> Select All columns view

It stores information such as Approval ID, Completed On, Details, Owner (the account used to create this approval), Result, Stage, Status Reason, etc. Note: It does not store details of to whom it is assigned.

Approval Request table

A separate record gets created in this table for each assigned to persons in approval action. If approval is assigned to a single user, then only one record gets created, but if it’s assigned to multiple users say 4, then 4 rows are created in this table.

This table stores information like Approval Request ID, Approval ID index (foreign key from previous table), Response Options, Owner, Stage, Status.

Approval Response table

Whenever user responds to approval notification then a record gets created in this table. So, record gets created only when user responds otherwise no entry will be added in this table.

It stores information like Response ID, Approval ID index, Comments, Response.

Now that you know how data is being stored in the backend, let me know in the comments on how you would get the list of pending approvals that are assigned to a specific user. What query you would fire and on which tables?

Check out detailed YouTube video from here.

Summary

Microsoft is recommending using Dataverse for your applications and is already using it successfully for in-house projects of so many Microsoft 365 applications. You digitalize your business using model-driven app and Dataverse very easily.

Hope this helps. Thank you for reading!