Create Dataverse Tables from BC

A couple of months I have blogged about how to sync your BC data to Dataverse with less code:
Sync your BC data to Dataverse with less code – Discover Microsoft Business Central (bertverbeek.nl)

But there was only one thing left and that is the amount of work you spent on creating Dataverse tables.
Since there is a Dataverse Web API you can also do it through that way:
Use the Microsoft Dataverse Web API (Dataverse) – Power Apps | Microsoft Learn

With the latest pull request on on the Github Repo “DataverseSyncUI”
Added feature of creating Dataverse tables via API by Bertverbeek4PS · Pull Request #4 · Bertverbeek4PS/DataverseSyncUI (github.com)
You can now also create a table in Dataverse from Business Central!

Functional section

First you have to create a App registration in your Azure portal with the following API Permissions:

After you have created the secret you can enter the Application Client ID into your admin center in Dataverse.
Just go to Dataverse and select the “Admin Center” and choose the right environement.
After this you see the “Application Users” below the section “Users + permissions”:

Click on “New app user” and assign your created App registration.
After this choose the right “Business Unit” and “Security Roles”. If you have push the button “Create” it will create the record for you.

After this you can return to Business Central and enter the credentials:

In the field “Prefix Dataverse” you can enter your own prefix the get unique tables and colums in Dataverse.
In the lines you can put in your table that you can to sync with Dataverse:

If you want to choose the fields you want to sync go in the ribbon to “Fields”. Here you can choose your own fields or in the ribbon choose “Enable all valid fields”

NB: Only the following fields you can export: BigInteger, Boolean, Code, Date, DateFormula, DateTime, Decimal, Duration, Guid, Integer, Option and Text.

Right know if you leave the screen en select the function “Create Dataverse Table”. With this action the Dataverse Web API will triggered and after couple of seconds your Dataverse table is created:

Right know you must create the integration table with the tool altpgen.exe that is in your AL extension folder from Visual Studio Code and you can put it in your own extension.

When the extension is loaded you can select your Dataverse table:

In in the “Fields” screen you can choose the mapping or push the button “Map Dataverse Fields”

After this you can use the function “Create Integration Table Mapping” and then the function “Create Job Queue”.

The first function will create the integration mapping inside the Dataverse standard and the second one will create a Job Queue for the table.
NB: before you can sync your data please update your security roles with the new tables.

When the sync has started you can see the data will appear in Dataverse:

And you are done with less code 😊

NB: there is now also an option “Integration Deletion Conflict Resolution” which calls “Delete Records” in this case if a record is deleted in Business Central or Dataverse then the records will delete in the other system!

Technical section

Creating tables inside Dataverse you must use the endpoint “EntityDefinitions”.

Also the response will be a 204 – No content response!

A good starting point is to look here if you want to create your own call:
Create and update table definitions using the Web API (Microsoft Dataverse) – Power Apps | Microsoft Learn
Here you can find all the properties and how you can create a table. But also some example on how to create the columns.

The creation of the json file you can look into the codeunit “DataverseUIDataverseInt.Codeunit.al”.
Here you can also see that the generation of option fields are not Global. So there is no creation of a Global Choice!

Also the following translations to fields is used:

BC Field Type	Dataverse Field Type
Integer	Integer
BigInteger	BigInt
Decimal	Decimal (precision is 2)
Duration	Duration
Date	DateTime (format DateOnly)
DateTime	DateTime (format DateAndTime)
Text	String (value Text)
Code	String (value Text)
DateFormula	String (value Text)
Guid	String (value Text)
Option	Choice (local)
Boolean	Boolean

Also in this case I have created my own request for getting the access token. In this case haven’t tried to use the module Oauth2.