In my session at Dataverse Summit 2022 I also introduced Business Central’s feature, the virtual tables in Dataverse. Virtual tables complement the possibilities such as Data Sync and Connectors to connect both worlds.

In my opinion, virtual tables are the best way of integration. Unlike data synchronization, you do not copy the data from one system to another. Furthermore, virtual tables in Dataverse are similar to physical tables in usage and this is a clear advantage in contrast to connectors.

In other words, a Virtual Table is like a physical table in Dataverse, but the data remains in the external system.

Virtual Tables

Ok, what is a virtual table in detail? Microsoft Docs says:

“…a virtual table is a custom table in Microsoft Dataverse that has columns containing data from an external data source. Virtual tables appear in your app to users as regular table rows, but contain data that is sourced from an external database, such as an Azure SQL Database. Rows based on virtual tables are available in all clients including custom clients developed using the Dataverse web services…”

The benefits for me are obviously. Firstly a virtual table is a table in Dataverse. This means, I can use this table im my model driven apps. Moreover, I can setup and customize views and forms for my virtual table.

Secondly, my virtual tables connects to an external datasource e.g. to my Business Central. In other words, my table data is not stored in Dataverse, instead it is stored in my external data source: in Business Central. The following picture simplify the architecture:

Additionally, I can use the transferred actual relations as well as I can create new synthetic relations between virtual tables and physical tables. This offers huge potential for my Power Apps and Power Automate Flows!

Setup Virtual Tables from Business Central

Good news, I there is an Assisted Setup in Business Central “Dataverse Connection Setup” which helps me to setup everything in a few minutes:

First I need to select my Dataverse environment and sign in as a dataverse administrator:

The wizard helps me also to install the needed Business Central Virtual Table app in Dataverse:

This link forward me to the Microsoft App Source app for Dataverse:

From here I’m able to select my Dataverse my target environment and start the installation:

Afterwards I can finish the wizard in Business Central with a final question:

To explain this: The user created in Dataverse is a 1st party app user that will call Business Central CRUD APIs on behalf of the actual users with a Business Central license.

Finally, everything should be ready for using my virtual tables in Dataverse. My Dataverse Connection Setup overview provide me all necessary Business Central information and some useful actions to navigate directly to my Available Virtual Tables in Dataverse:

Adding Business Central Virtual Tables to Dataverse

By default no table is added in Dataverse. I can change this in my Dataverse table “Available Business Central Tables”. For this I navigate from Dataverse Connection Setup to my table (old crm-like layout) or I open the table directly (new Dataverse designer layout):

Here are all my available virtual tables listed as records based on all available API Routes in Business Central. Own APIs are also included.

I strongly recommend to filter and use always for the latest API, before you toggle the visible button for a virtual table. Furthermore, it is extremely important that you wait until the system finishes creating the virtual table for you:

After a while, my virtual tables are present:

… and ready to use:

Using Virtual Tables

As described before, using a virtual table is extremely simple. I can now use the native Dataverse technology to access my data such as Dataverse connectors in Power Automate Flows:

A closer look to my trigger code shows me the secret. You see, my used table is “dyn365bc_customer_v2_0”, which is my virtual table for the Business Central table customer:

{
    "inputs": {
        "host": {
            "connectionName": "shared_commondataserviceforapps",
            "operationId": "SubscribeWebhookTrigger",
            "apiId": "/providers/Microsoft.PowerApps/apis/shared_commondataserviceforapps"
        },
        "parameters": {
            "subscriptionRequest/message": 4,
            "subscriptionRequest/entityname": "dyn365bc_customer_v2_0",
            "subscriptionRequest/scope": 4
        },
        "authentication": "@parameters('$authentication')"
    },
    "metadata": {
        "operationMetadataId": "5ca1c2ed-2b41-4da0-8174-198d2aacd9e0"
    }
}

Another option is to use my virtual tables now in my model driven apps:

Benefits & Limitations

Virtual tables from Business Central in Dataverse are amazing. As you have seen in a very view examples, I can use the native Dataverse technology such as Triggers and Actions in my Power Automate Flows. The Business Central virtual tables offer my CRUD operations (create, read, update, delete)as well as CUD events (create, update, delete).

Additionally, all actual references in Business Central are also added to my Dataverse virtual tables. This means in detail, my Sales Orders have a relation to their Sales Orders Lines. With such a relation, I’m able to present my related “Sales Order Lines” in a sub grids in my model driven app:

Additionally, I can create synthetic relations between physical and virtual tables in Dataverse. I’ll cover this in a future blog post.

On the other hand, the are also a view limitations. First of all, connecting Business Central to Dataverse is almost impossible, when you do not have the same base currency in both systems. Furthermore, I can currently only use my Business Central virtual tables from one company in a one Dataverse environment.

Summary

From my point of view, adding virtual tables is a great advantage for Dataverse. I can naturally incorporate my external data into my business processes in Dataverse. In other words, I don’t copy my external data into Dataverse – I simply reference my external tables and records in Dataverse.

Stay tuned … i will blog more about this topic in the future!