Replication of own Business Central table to CDS

Hello Team!

As I promised before - new blog about building replication of custom table to CDS.

About CDS connection setup you could read here and now we could start.

All the code for current demo you can find in my repository - https://github.com/ABaludin/CDSBox

Let's begin:

Step1.

I created table 50140 "AWR_Box" (AWR is just a prefix of our company) and page 50140 "AWR_Box List" to input data.

Table has next fields with different types to show you nuances of replication of each  type:

So you can see that I used all the types which we usually operate.

Page is type of List and contains only repeater with fields nothing more.

You could already publish your app and input some data like this:

Step 2.

On this step I create the same table in CDS.

Open your environment using the link https://make.powerapps.com/environments/ your environment ID

and click on Entities tab -> New entity

Name it "Box" and input Box No. as a Primary field. Click on Create.

You'll get next structure. You can see automatically created Box field with type Unique Identifier, your primary field Box No. and set of other CDS fields which creating for each entity.

Click on Add field and create next fields:

Important tips:

1. Floating point number by default has minimum value = 0. Change it in Advanced options of the field to -2,147,483,648 if you planning to use negative numbers

2. Text fields by default has 150 chars length - update it to 250 for Comment field

3. Option Set need to be created. Use the same set as in the Business Central - Small,Medium,Large with default value = Small.

Click on Keys tab and create new key with Box No. field.

Do not forget to click on Save Entity.

Step 3.

On this step I create intermediate integration table in Business Central which used to pass data to CDS.

Microsoft provides special tool to generate CDS Integration table object for BC - altpgen.exe. You could find it in your AL extension folder.

In my case it's:

C:\Users\YOUR USERNAME\.vscode\extensions\ms-dynamics-smb.al-5.0.288712

Remember that extension folder name could be different depending on AL extension version.

Create arguments file args.rsp in your VS Code project (also could be found in my Github repo). Fill -project,-packagepath and -serviceuri parameters with your folders and  cds environment url.

and then run next command in terminal (Change path to your folder where altpgen.exe placed) :

C:\Users\andrey.baludin.AVENIR\.vscode\extensions\ms-dynamics-smb.al-5.0.288712\bin\altpgen.exe '@args.rsp'

CDS authorization window will open and after signing in you'll see next:

And you'll get new table with type CDS in your project:

Important thing. It incorrectly generate date fields so fix Shipment Date field type from DateTime to Date. External field type should left DateTime.

Add Data classification property and save the table.

Step 4.

Create "CDS Boxes" List page for CDS_Box table with all the fields and add OnInit trigger with next code:

    trigger OnInit()
    begin
        Codeunit.Run(Codeunit::"CRM Integration Management");
    end;

without that - page not open.

This page we need to control transferred to CDS data.

Step 5.

On this step I create management codeunit.

Open Common Data Service Connection Setup page in Business Central and click Navigate -> Integration Table Mappings. You'll see the full list of your tables which replicating to CDS with fields mapping. But you can't insert records here so we need to create codeunit which do it for us.

1. Create InsertIntegrationTableMapping function to insert table mapping:

local procedure InsertIntegrationTableMapping(var IntegrationTableMapping: Record "Integration Table Mapping"; MappingName: Code[20]; TableNo: Integer; IntegrationTableNo: Integer; IntegrationTableUIDFieldNo: Integer; IntegrationTableModifiedFieldNo: Integer; TableConfigTemplateCode: Code[10]; IntegrationTableConfigTemplateCode: Code[10]; SynchOnlyCoupledRecords: Boolean)
begin
    IntegrationTableMapping.CreateRecord(MappingName, TableNo, IntegrationTableNo, IntegrationTableUIDFieldNo, IntegrationTableModifiedFieldNo, TableConfigTemplateCode, IntegrationTableConfigTemplateCode, SynchOnlyCoupledRecords, IntegrationTableMapping.Direction::ToIntegrationTable, 'CDS');
end;

2. Create InsertIntegrationFieldMapping function to insert fields mapping:

    procedure InsertIntegrationFieldMapping(IntegrationTableMappingName: Code[20]; TableFieldNo: Integer; IntegrationTableFieldNo: Integer; SynchDirection: Option; ConstValue: Text; ValidateField: Boolean; ValidateIntegrationTableField: Boolean)
    var
        IntegrationFieldMapping: Record "Integration Field Mapping";
    begin
        IntegrationFieldMapping.CreateRecord(IntegrationTableMappingName, TableFieldNo, IntegrationTableFieldNo, SynchDirection,
            ConstValue, ValidateField, ValidateIntegrationTableField);
    end;

3. Create event subscriber to insert data on after reset cds configuration which inserts all our mapping:

    [EventSubscriber(ObjectType::Codeunit, Codeunit::"CDS Setup Defaults", 'OnAfterResetConfiguration', '', true, true)]
    local procedure HandleOnAfterResetConfiguration(CDSConnectionSetup: Record "CDS Connection Setup")
    var
        IntegrationTableMapping: Record "Integration Table Mapping";
        IntegrationFieldMapping: Record "Integration Field Mapping";
        CDSBox: Record "AWR_CDS Box";
        Box: Record AWR_Box;
    begin
        InsertIntegrationTableMapping(
            IntegrationTableMapping, 'Box',
            DATABASE::"AWR_Box", DATABASE::"AWR_CDS Box",
            CDSBox.FieldNo(cr78b_BoxId), CDSBox.FieldNo(ModifiedOn),
            '', '', false);

        InsertIntegrationFieldMapping('Box', Box.FieldNo("Box No."), CDSBox.FieldNo(cr78b_BoxNo), IntegrationFieldMapping.Direction::ToIntegrationTable, '', true, false);
        InsertIntegrationFieldMapping('Box', Box.FieldNo(Weight), CDSBox.FieldNo(cr78b_Weight), IntegrationFieldMapping.Direction::ToIntegrationTable, '', true, false);
        InsertIntegrationFieldMapping('Box', Box.FieldNo("Shipment Date"), CDSBox.FieldNo(cr78b_ShipmentDate), IntegrationFieldMapping.Direction::ToIntegrationTable, '', true, false);
        InsertIntegrationFieldMapping('Box', Box.FieldNo(Delivered), CDSBox.FieldNo(cr78b_Delivered), IntegrationFieldMapping.Direction::ToIntegrationTable, '', true, false);
        InsertIntegrationFieldMapping('Box', Box.FieldNo(Comment), CDSBox.FieldNo(cr78b_Comment), IntegrationFieldMapping.Direction::ToIntegrationTable, '', true, false);
        InsertIntegrationFieldMapping('Box', Box.FieldNo("Items Qty."), CDSBox.FieldNo(cr78b_ItemsQty), IntegrationFieldMapping.Direction::ToIntegrationTable, '', true, false);
        InsertIntegrationFieldMapping('Box', Box.FieldNo("Carton Type"), CDSBox.FieldNo(cr78b_CartonType), IntegrationFieldMapping.Direction::ToIntegrationTable, '', true, false);
    end;

4. Create two event subscribers to show system that our table is Integration table:

    [EventSubscriber(ObjectType::Codeunit, Codeunit::"CRM Setup Defaults", 'OnGetCDSTableNo', '', false, false)]
    local procedure HandleOnGetCDSTableNo(BCTableNo: Integer; var CDSTableNo: Integer; var handled: Boolean)
    begin
        if BCTableNo = DATABASE::AWR_Box then begin
            CDSTableNo := DATABASE::"AWR_CDS Box";
            handled := true;
        end;
    end;

    [EventSubscriber(ObjectType::Codeunit, Codeunit::"Integration Management", 'OnIsIntegrationRecord', '', true, true)]
    local procedure HandleOnIsIntegrationRecord(TableID: Integer; var isIntegrationRecord: Boolean)
    begin
        if TableID = DATABASE::AWR_Box then
            isIntegrationRecord := true;
    end;

5. Create additional event subscribers for CDS_Boxes page to make it work:

    [EventSubscriber(ObjectType::Codeunit, Codeunit::"Integration Management", 'OnAfterAddToIntegrationPageList', '', true, true)]
    local procedure HandleOnAfterAddToIntegrationPageList(var TempNameValueBuffer: Record "Name/Value Buffer"; var NextId: Integer)
    begin
        TempNameValueBuffer.Init();
        TempNameValueBuffer.ID := NextId;
        NextId := NextId   1;
        TempNameValueBuffer.Name := Format(Page::"AWR_Box List");
        TempNameValueBuffer.Value := Format(Database::AWR_Box);
        TempNameValueBuffer.Insert();
    end;


    [EventSubscriber(ObjectType::Codeunit, Codeunit::"Lookup CRM Tables", 'OnLookupCRMTables', '', true, true)]
    local procedure HandleOnLookupCRMTables(CRMTableID: Integer; NAVTableId: Integer; SavedCRMId: Guid; var CRMId: Guid; IntTableFilter: Text; var Handled: Boolean)
    begin
        if CRMTableID = Database::"AWR_CDS Box" then
            Handled := LookupCDSBox(CRMId, IntTableFilter);
    end;


    local procedure LookupCDSBox(var CRMId: Guid; IntTableFilter: Text): Boolean
    var
        CDSBox: Record "AWR_CDS Box";
        CDSBoxList: Page "AWR_CDS Boxes";
    begin
        if not IsNullGuid(CRMId) then
            if CDSBox.Get(CRMId) then
                CDSBoxList.SetRecord(CDSBox);

        CDSBox.SetView(IntTableFilter);
        CDSBoxList.SetTableView(CDSBox);
        CDSBoxList.LookupMode(true);
        if CDSBoxList.RunModal() = ACTION::LookupOK then begin
            CDSBoxList.GetRecord(CDSBox);
            CRMId := CDSBox.cr78b_BoxId;
            exit(true);
        end;
        exit(false);
    end;

Now you can publish your app.

Step 6.

Open Common Data Service Connection Setup page. Click Actions -> Use Default Synchronization Setup. This will trigger our event and create tables and field mappings in system. Remember - if you change something in data structure or in codeunit code you need to run Use Default Synchronization Setup again.

Click on Navigate -> Integration Table Mappings. You'll see your table mapping:

Click on Mapping -> Fields and you'll get the fields mapping:

Now turn back to Integration Table Mappings, Select your Box mapping and click Synchronization -> Run Full Synchronization. Process will start and after end click on Synchronization -> Integration Synch. Job Log. You can see the result of synchronization here:

On message field you will get error messages if something went wrong.

Now open the "CDS Boxes" page and you find records passed to CDS:

and finally open your Box entity in CDS and find all the records there on Data tab:

By default it shows primary field and CreatedOn but playing with Forms tab you could change the view like this to ensure that all the fields replicated:

Additional materials that I used for this demo:

https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/administration/administration-custom-cds-integration

https://docs.microsoft.com/en-us/dynamics365-release-plan/2020wave1/dynamics365-business-central/business-central-integration-common-data-service

Hope that it'll help you to build your own integrations. Remember that link with all the BC code - https://github.com/ABaludin/CDSBox