Create Dataverse Tables from BC

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:

In this case we are using the employee table.

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 TypeDataverse Field Type
IntegerInteger
BigIntegerBigInt
DecimalDecimal (precision is 2)
DurationDuration
DateDateTime (format DateOnly)
DateTimeDateTime (format DateAndTime)
TextString (value Text)
CodeString (value Text)
DateFormulaString (value Text)
GuidString (value Text)
OptionChoice (local)
BooleanBoolean

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.


4 COMMENTS

Chuck

This is brilliant. I have been dreading having to build out the dataverse tables for the ISV we manage. We will definitely be trying this!

    Bert Verbeek

    Thanks Chuck. Hope it works for you.

Jef

Hi Bert,
I’m trying out this tool. This could help me lots of time, but i always get a bad request when creating a new table:
{
“error”: {
“code”: “0x80040203”,
“message”: “Required field ‘PrimaryAttribute’ is missing”
}
}
Something you came across already?

    Bert Verbeek

    Hi Jef,
    Have you also created fields inside the table?
    Because if you have selected the fields he will make the Primary keys also as primary in Dataverse.

Leave a Reply

Your email address will not be published. Required fields are marked *