Search code examples
dynamics-business-centraldynamics-al

Synchronization Issue with Customizing an Integration with Microsoft Dataverse

followed documentation : https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/administration/administration-custom-cds-integration docs and https://yzhums.com/17065/

When I launch “Run Full Sync.” and look at the job log of my testing table, only records initially created in business central are inserted in the crm. Any rows that I created from my integration table in the crm are not inserted in business central. enter image description here

I suspect that this note below may be the cause because some columns were skipped during the creation of the proxy table using AL Table Proxy Generator butI have no way to know since no error messages are showing up regarding that. enter image description here https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-al-table-proxy-generator

Example Let's say I have 2 records in my testing table on the crm side. enter image description here I can see them in my proxy Table List on the business central side, but they are not inserted yet in my target table. enter image description here enter image description here

Note for the first screen shot: If I select one of those two rows in my proxy testing table and click on create in business central, it creates it successfully in bc... so I'm wondering why that is working and not the "run full sync"

Then I create a record from bc before running the full synchronization and look at the results. enter image description here

We can see here that one record was inserted from BC to the crm (all fields were mapped correctly) but nothing from the crm to BC. enter image description here enter image description here

Here's my project folder tree. If you need to see a specific page let me know. enter image description here

CODE Tab50100.Testingtableforintegration.al

table 50100 "Testing table for integration"
{
    Caption = 'Target integration table';
    DataClassification = ToBeClassified;

    fields
    {
        field(1; "name"; Code[25])
        {
            Caption = 'name';
            DataClassification = ToBeClassified;
        }

        field(2; "my bc field one"; Text[50])
        {
            Caption = 'my bc field one';
            DataClassification = ToBeClassified;
        }
        field(3; "my bc field two"; Integer)
        {
            Caption = 'my bc field two';
            DataClassification = ToBeClassified;
        }
    }
    keys
    {
        key(PK; "name")
        {
            Clustered = true;
        }
    }
}

Pag50101.TestingIntrecordslist.al

page 50101 "target table records list"
{
    ApplicationArea = All;
    Caption = 'target table records list';
    PageType = List;
    SourceTable = "Testing table for integration";
    UsageCategory = Lists;
    CardPageId = "target table Card";
    Editable = false;


    layout
    {
        area(content)
        {
            repeater(General)
            {
                field("code"; Rec."name")
                {
                    ApplicationArea = All;
                    ToolTip = 'Specifies the value of the code field.';
                }
                field("my bc field two"; Rec."my bc field two")
                {
                    ApplicationArea = All;
                    ToolTip = 'Specifies the value of the my bc field two field.';
                }
                field("my bc field one"; Rec."my bc field one")
                {
                    ApplicationArea = All;
                    ToolTip = 'Specifies the value of the my bc field one field.';
                }
                field(SystemCreatedAt; Rec.SystemCreatedAt)
                {
                    ApplicationArea = All;
                    ToolTip = 'Specifies the value of the SystemCreatedAt field.';
                }
                field(SystemCreatedBy; Rec.SystemCreatedBy)
                {
                    ApplicationArea = All;
                    ToolTip = 'Specifies the value of the SystemCreatedBy field.';
                }
            }
        }
    }



    actions
    {
        area(Processing)
        {
            group(ActionGroupCDS)
            {
                Caption = 'Dataverse';
                Visible = CDSIntegrationEnabled;

                action(CDSGotoTargetRecord)
                {
                    Caption = 'Go to Dataverse record';
                    ApplicationArea = All;
                    Visible = true;
                    Image = CoupledCustomer;
                    Enabled = CDSIsCoupledToRecord;
                    ToolTip = 'Open the coupled Dataverse Record';

                    trigger OnAction()
                    var
                        CRMIntegrationManagement: Codeunit "CRM Integration Management";
                    begin
                        CRMIntegrationManagement.ShowCRMEntityFromRecordID(Rec.RecordId);
                    end;
                }
                action(CDSSynchronizeNow)
                {
                    Caption = 'Synchronize';
                    ApplicationArea = All;
                    Visible = true;
                    Image = Refresh;
                    Enabled = CDSIsCoupledToRecord;
                    ToolTip = 'Send or get updated data to or from Microsoft Dataverse.';

                    trigger OnAction()
                    var
                        CRMIntegrationManagement: Codeunit "CRM Integration Management";
                    begin
                        CRMIntegrationManagement.UpdateOneNow(Rec.RecordId);
                    end;
                }
                action(ShowLog)
                {
                    Caption = 'Synchronization Log';
                    ApplicationArea = All;
                    Visible = true;
                    Image = Log;
                    ToolTip = 'View integration synchronization jobs for the customer table.';

                    trigger OnAction()
                    var
                        CRMIntegrationManagement: Codeunit "CRM Integration Management";
                    begin
                        CRMIntegrationManagement.ShowLog(Rec.RecordId);
                    end;
                }
                group(Coupling)
                {
                    Caption = 'Coupling';
                    Image = LinkAccount;
                    ToolTip = 'Create, change, or delete a coupling between the Business Central record and a Microsoft Dataverse row.';

                    action(ManageCDSCoupling)
                    {
                        Caption = 'Set Up Coupling';
                        ApplicationArea = All;
                        Visible = true;
                        Image = LinkAccount;
                        ToolTip = 'Create or modify the coupling to a Microsoft Dataverse Record';

                        trigger OnAction()
                        var
                            CRMIntegrationManagement: Codeunit "CRM Integration Management";
                        begin
                            CRMIntegrationManagement.DefineCoupling(Rec.RecordId);
                        end;
                    }
                    action(DeleteCDSCoupling)
                    {
                        Caption = 'Delete Coupling';
                        ApplicationArea = All;
                        Visible = true;
                        Image = UnLinkAccount;
                        Enabled = CDSIsCoupledToRecord;
                        ToolTip = 'Delete the coupling to a Microsoft Dataverse Record';

                        trigger OnAction()
                        var
                            CRMCouplingManagement: Codeunit "CRM Coupling Management";
                        begin
                            CRMCouplingManagement.RemoveCoupling(Rec.RecordId);
                        end;
                    }
                }
            }
        }
    }
    trigger OnOpenPage()
    begin
        CDSIntegrationEnabled := CRMIntegrationManagement.IsCDSIntegrationEnabled();
    end;

    trigger OnAfterGetCurrRecord()
    begin
        if CDSIntegrationEnabled then
            CDSIsCoupledToRecord := CRMCouplingManagement.IsRecordCoupledToCRM(Rec.RecordId);
    end;

    var
        CRMIntegrationManagement: Codeunit "CRM Integration Management";
        CRMCouplingManagement: Codeunit "CRM Coupling Management";
        CDSIntegrationEnabled: Boolean;
        CDSIsCoupledToRecord: Boolean;

}

Pag50100.TestingIntegrationCard.al

page 50100 "target table Card"
{
    Caption = 'target table Card';
    PageType = Card;
    SourceTable = "Testing table for integration";

    layout
    {
        area(content)
        {
            group(General)
            {
                field("code"; Rec."name")
                {
                    ApplicationArea = All;
                    ToolTip = 'Specifies the value of the code field.';
                }
                field("my bc field one"; Rec."my bc field one")
                {
                    ApplicationArea = All;
                    ToolTip = 'Specifies the value of the my bc field one field.';
                }
                field("my bc field two"; Rec."my bc field two")
                {
                    ApplicationArea = All;
                    ToolTip = 'Specifies the value of the my bc field two field.';
                }
                field(SystemCreatedAt; Rec.SystemCreatedAt)
                {
                    ApplicationArea = All;
                    ToolTip = 'Specifies the value of the SystemCreatedAt field.';
                }
                field(SystemCreatedBy; Rec.SystemCreatedBy)
                {
                    ApplicationArea = All;
                    ToolTip = 'Specifies the value of the SystemCreatedBy field.';
                }
                field(SystemId; Rec.SystemId)
                {
                    ApplicationArea = All;
                    ToolTip = 'Specifies the value of the SystemId field.';
                }
            }
        }
    }
}

Generated Proxy Table.al

table 50101 "CDS cr46d_d365salestestingtableforbcintegration"
{
    ExternalName = 'cr46d_d365salestestingtableforbcintegration';
    TableType = CDS;
    Description = '';

    fields
    {
        field(1; cr46d_D365SalestestingtableforbcintegrationId; GUID)
        {
            ExternalName = 'cr46d_d365salestestingtableforbcintegrationid';
            ExternalType = 'Uniqueidentifier';
            ExternalAccess = Insert;
            Description = 'Unique identifier for entity instances';
            Caption = 'D365 Sales testing table for bc integration';
        }
        field(2; CreatedOn; Datetime)
        {
            ExternalName = 'createdon';
            ExternalType = 'DateTime';
            ExternalAccess = Read;
            Description = 'Date and time when the record was created.';
            Caption = 'Created On';
        }
        field(4; ModifiedOn; Datetime)
        {
            ExternalName = 'modifiedon';
            ExternalType = 'DateTime';
            ExternalAccess = Read;
            Description = 'Date and time when the record was modified.';
            Caption = 'Modified On';
        }
        field(25; statecode; Option)
        {
            ExternalName = 'statecode';
            ExternalType = 'State';
            ExternalAccess = Modify;
            Description = 'Status of the D365 Sales testing table for bc integration';
            Caption = 'Status';
            InitValue = " ";
            OptionMembers = " ",Active,Inactive;
            OptionOrdinalValues = -1, 0, 1;
        }
        field(27; statuscode; Option)
        {
            ExternalName = 'statuscode';
            ExternalType = 'Status';
            Description = 'Reason for the status of the D365 Sales testing table for bc integration';
            Caption = 'Status Reason';
            InitValue = " ";
            OptionMembers = " ",Active,Inactive;
            OptionOrdinalValues = -1, 1, 2;
        }
        field(29; VersionNumber; BigInteger)
        {
            ExternalName = 'versionnumber';
            ExternalType = 'BigInt';
            ExternalAccess = Read;
            Description = 'Version Number';
            Caption = 'Version Number';
        }
        field(30; ImportSequenceNumber; Integer)
        {
            ExternalName = 'importsequencenumber';
            ExternalType = 'Integer';
            ExternalAccess = Insert;
            Description = 'Sequence number of the import that created this record.';
            Caption = 'Import Sequence Number';
        }
        field(31; OverriddenCreatedOn; Date)
        {
            ExternalName = 'overriddencreatedon';
            ExternalType = 'DateTime';
            ExternalAccess = Insert;
            Description = 'Date and time that the record was migrated.';
            Caption = 'Record Created On';
        }
        field(32; TimeZoneRuleVersionNumber; Integer)
        {
            ExternalName = 'timezoneruleversionnumber';
            ExternalType = 'Integer';
            Description = 'For internal use only.';
            Caption = 'Time Zone Rule Version Number';
        }
        field(33; UTCConversionTimeZoneCode; Integer)
        {
            ExternalName = 'utcconversiontimezonecode';
            ExternalType = 'Integer';
            Description = 'Time zone code that was in use when the record was created.';
            Caption = 'UTC Conversion Time Zone Code';
        }
        field(34; cr46d_Name; Text[100])
        {
            ExternalName = 'cr46d_name';
            ExternalType = 'String';
            Description = '';
            Caption = 'Name';
        }
        field(35; cr46d_mytestingcolumnone; Text[100])
        {
            ExternalName = 'cr46d_mytestingcolumnone';
            ExternalType = 'String';
            Description = '';
            Caption = 'my testing column one';
        }
        field(36; cr46d_mytestingcolumntwo; Integer)
        {
            ExternalName = 'cr46d_mytestingcolumntwo';
            ExternalType = 'Integer';
            Description = '';
            Caption = 'my testing column two';
        }
        field(37; cr46d_CoupledtoCRM; Boolean)
        {
            ExternalName = 'cr46d_coupledtocrm';
            ExternalType = 'Boolean';
            Description = '';
            Caption = 'Coupled to CRM';
        }
    }
    keys
    {
        key(PK; cr46d_D365SalestestingtableforbcintegrationId)
        {
            Clustered = true;
        }
        key(Name; cr46d_Name)
        {
        }
    }
    fieldgroups
    {
        fieldgroup(DropDown; cr46d_Name)
        {
        }
    }
}

Pag50102.ProxyTableList.al

// List of records of the proxy table that shows us the records imported from Dynamics Sales CRM
// each table we want to import need to have its corresponding proxy table. Even the default tables like contact has its proxy tablke that have been automaticaly created by the connection setup.
page 50102 "Proxy Table List"
{
    ApplicationArea = All;
    Caption = 'Proxy testing Table - Dataverse';
    PageType = List;
    SourceTable = "CDS cr46d_d365salestestingtableforbcintegration";
    UsageCategory = Lists;

    layout
    {
        area(content)
        {
            repeater(General)
            {
                field(cr46d_Name; Rec.cr46d_Name)
                {
                    ApplicationArea = All;
                    ToolTip = 'Specifies the value of the Name field.';
                }
                field(cr46d_mytestingcolumnone; Rec.cr46d_mytestingcolumnone)
                {
                    ApplicationArea = All;
                    ToolTip = 'Specifies the value of the my testing column one field.';
                }
                field(cr46d_mytestingcolumntwo; Rec.cr46d_mytestingcolumntwo)
                {
                    ApplicationArea = All;
                    ToolTip = 'Specifies the value of the my testing column two field.';
                }
                field(cr46d_D365SalestestingtableforbcintegrationId; Rec.cr46d_D365SalestestingtableforbcintegrationId)
                {
                    ApplicationArea = All;
                    ToolTip = 'Specifies the value of the D365 Sales testing table for bc integration field.';
                }
            }
        }
    }
    actions
    {
        area(processing)
        {
            action(CreateFromCDS)
            {
                ApplicationArea = All;
                Caption = 'Create in Business Central';
                Promoted = true;
                PromotedCategory = Process;
                ToolTip = 'Generate the record from the coupled Microsoft Dataverse row.';
                Image = AssemblyBOM;

                trigger OnAction()
                var
                    // initialise proxy table variable and CRMIntegrationManagement code unit
                    ProxyTable: Record "CDS cr46d_d365salestestingtableforbcintegration";
                    CRMIntegrationManagement: Codeunit "CRM Integration Management";
                begin
                    // apply the actual current page filter (selected record) on the proxy table records and pass it to the 
                    // CreateNewRecordsFromCRM function for insertion in the target table
                    CurrPage.SetSelectionFilter(ProxyTable);
                    CRMIntegrationManagement.CreateNewRecordsFromCRM(ProxyTable);
                end;
            }
        }


    }

    var
        CurrentlyCoupledProxyTable: Record "CDS cr46d_d365salestestingtableforbcintegration";

    // is launched before the startup of the page and calls the OnRun() function of codeunit 5330 "CRM Integration Management", which then 
    // execute CheckOrEnableCRMConnection to establish the connection with dataverse.
    trigger OnInit()
    begin
        Codeunit.Run(Codeunit::"CRM Integration Management");
    end;

    // this function is used by the codeunit dataverse event
    procedure SetCurrentlyCoupledProxyTable(ProxyTable: Record "CDS cr46d_d365salestestingtableforbcintegration")
    begin
        CurrentlyCoupledProxyTable := ProxyTable;
    end;

}

Cod50100.DataverseEvent.al

// note to read comments: entry here means a line in a table or a single record.


// documentation: https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/administration/administration-custom-cds-integration
codeunit 50100 DataverseEvent
{
    // in the codeunit CRM Setup Defaults, when the procedure OnGetCDSTableNo is called, execute this local procedure.
    // launched when button setup couplings in BC is clicked
    [EventSubscriber(ObjectType::Codeunit, Codeunit::"CRM Setup Defaults", 'OnGetCDSTableNo', '', false, false)]
    local procedure HandleOnGetCDSTableNo(BCTableNo: Integer; var CDSTableNo: Integer; var handled: Boolean)
    begin
        // error('HandleOnGetCDSTableNo was called! %1', BCTableNo);
        // specify to the function OnGetCDSTableNo to exit immediatly due to handled := true because BCTableNo and CDSTableNo
        // are not default BC tables and will not be found in the codeunit CRM Setup Defaults. (For more details see codeunit 5334)
        if BCTableNo = DATABASE::"Testing table for integration" then begin
            CDSTableNo := DATABASE::"CDS cr46d_d365salestestingtableforbcintegration";
            handled := true; // handled means here taken care of by us.
        end;
    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::"CDS cr46d_d365salestestingtableforbcintegration" then
            Handled := LookupProxyTable(SavedCRMId, CRMId, IntTableFilter); // this will return true if CRMID point to a proxy table entry.
    end;

    // this function returns a boolean used to assign Handled var in HandleOnLookupCRMTables
    // the goal of this function is to replicate the procedure LookupCRM<tableName>(we can find the definitions of those functions in the codeunit 5332 - Lookup CRM Tables)
    // as if we were to add it directly (but we can't because microsoft don't want their code touched, that is why they specified a event trigger that we can use to extend their codeunit)
    local procedure LookupProxyTable(SavedCRMId: Guid; var CRMId: Guid; IntTableFilter: Text): Boolean
    var
        ProxyTable: Record "CDS cr46d_d365salestestingtableforbcintegration"; // the proxy table
        OriginalProxyTable: Record "CDS cr46d_d365salestestingtableforbcintegration";
        OriginalProxyTableList: Page "Proxy Table List"; // THIS IS A LIST NOT A CARD
    begin
        if not IsNullGuid(CRMId) then begin // if the crm id is not null. The crm id represents the id of an entry in the proxy table
            if ProxyTable.Get(CRMId) then // if the proxy table contains the entry linked with the crm id
                OriginalProxyTableList.SetRecord(ProxyTable); // Set the proxy table list to the proxy table that has been filtrated by the passed crm id
            if not IsNullGuid(SavedCRMId) then// ????
                if OriginalProxyTable.Get(SavedCRMId) then begin
                    Message('we are here in LookupProxyTable');
                    OriginalProxyTableList.SetCurrentlyCoupledProxyTable(OriginalProxyTable);
                end;
            ;
            // gives to the list the currently coupled proxy table
            // OriginalProxyTableList.SetCurrentlyCoupledProxyTable(OriginalProxyTable);
        end;
        ProxyTable.SetView(IntTableFilter);
        OriginalProxyTableList.SetTableView(ProxyTable);
        OriginalProxyTableList.LookupMode(true);
        if OriginalProxyTableList.RunModal = ACTION::LookupOK then begin
            OriginalProxyTableList.GetRecord(ProxyTable);
            CRMId := ProxyTable.cr46d_D365SalestestingtableforbcintegrationId;
            exit(true);
        end;
        exit(false);
    end;

    [EventSubscriber(ObjectType::Codeunit, Codeunit::"CRM Setup Defaults", 'OnAddEntityTableMapping', '', true, true)]
    local procedure HandleOnAddEntityTableMapping(var TempNameValueBuffer: Record "Name/Value Buffer" temporary);
    var
        CRMSetupDefaults: Codeunit "CRM Setup Defaults";
    begin
        CRMSetupDefaults.AddEntityTableMapping('cr46d_d365salestestingtableforbcintegration', DATABASE::"CDS cr4', DATABASE::"Testing table for integration", TempNameValueBuffer); //IMPORTANT TO CHANGE - LOGICAL NAME OF THE DV TABLE
        CRMSetupDefaults.AddEntityTableMapping('cr46d_d365salestestingtableforbcintegration', DATABASE::"CDS cr46d_d365salestestingtableforbcintegration", TempNameValueBuffer);
    end;

    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::Bidirectional, 'CDS');
    end;

    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;

    [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";
        ProxyTable: Record "CDS cr46d_d365salestestingtableforbcintegration";
        TargetTable: Record "Testing table for integration";
    begin
        // inserts a line in the integrationTableMapping
        InsertIntegrationTableMapping(
        IntegrationTableMapping, 'My testing table',
        DATABASE::"Testing table for integration", DATABASE::"CDS cr46d_d365salestestingtableforbcintegration", ProxyTable.FieldNo(cr46d_D365SalestestingtableforbcintegrationId), ProxyTable.FieldNo(ModifiedOn), '', '', true);
        // in the newly insertion, insert integration fields to couple
        InsertIntegrationFieldMapping('My testing table', TargetTable.FieldNo("name"), ProxyTable.FieldNo(cr46d_name), IntegrationFieldMapping.Direction::Bidirectional, '', true, false);
        InsertIntegrationFieldMapping('My testing table', TargetTable.FieldNo("my bc field one"), ProxyTable.FieldNo(cr46d_mytestingcolumnone), IntegrationFieldMapping.Direction::Bidirectional, '', true, false);
        InsertIntegrationFieldMapping('My testing table', TargetTable.FieldNo("my bc field two"), ProxyTable.FieldNo(cr46d_mytestingcolumntwo), IntegrationFieldMapping.Direction::Bidirectional, '', true, false);
    end;
}

So I would like to know first of all what is causing the synchronization to malfunction without raising any messages & how to solve it step by step/in detail if modifications are needed in the project code itself. Hope this was clear enough to illustrate my issue, if any additional clarifications are needed please let me know.

Thank you very much!

Solution

  • I solved my Issue, my proxy table was missing the field modified by from Dataverse. You can see the error message in the job queue entries.