CRM Integration: Add a custom field to the existing CRM table with a custom transfer field process

This is the second part of my posts about custom CRM integration. See all related articles in CRM Integration | MSDyn365 Business Central – Tomas Kapitan (kepty.cz) category.

---

In the previous part, we discussed how to add a custom text field to CRM integration for the OOTB table (the Account table). But we do not always have fields with the same data types in both BC and CRM, or we need to

do some advanced transformation that can’t be done with transformation rules (I’ll describe transformation rules probably in the next part). In this part, I will show you how to do the custom transformation of the value when synchronizing the field from BC to CE or vice versa. Source codes for this part could be found here: AL-Example-CRMIntegration/AddFieldToExistingTable-CustomValue at main · TKapitan/AL-Example-CRMIntegration (github.com)

For example, you need to synchronize the Blocked field from the Customer/Vendor account. The OOTB functionality synchronizes only active customers/vendors, but you can change the filter in “Integration Mapping” to allow all entities. There is no OOTB field in CE, so we need to add our custom field to CE (similarly to what I showed in the previous post).

tableextension 50000 "TKA CRM Account" extends "CRM Account"
{
    fields
    {
        field(50000; TKA_Blocked; Option)
        {
            ExternalName = 'tka_blocked';
            ExternalType = 'Picklist';
            Description = '';
            Caption = 'Blocked';
            OptionMembers = " ",Ship,Invoice,Payment,All;
            OptionOrdinalValues = 0, 350000000, 350000001, 350000002, 350000003;
            DataClassification = CustomerContent;
        }
    }
}

pageextension 50000 "TKA CRM Account List" extends "CRM Account List"
{
    layout
    {
        addbefore(Coupled)
        {
            field(TKA_Blocked; Rec.TKA_Blocked)
            {
                ToolTip = 'Specifies data from a corresponding field in a Dataverse entity. For more information about Dataverse, see Dataverse Help Center.';
                ApplicationArea = All;
            }
        }
    }
}

codeunit 50000 "TKA CRM Setup Defaults"
{
    SingleInstance = true;

    var
        IntegrationFieldMapping: Record "Integration Field Mapping";

    [EventSubscriber(ObjectType::Codeunit, Codeunit::"CDS Setup Defaults", OnAfterResetCustomerAccountMapping, '', false, false)]
    local procedure OnAfterResetCustomerAccountMappingCDSSetupDefaults(IntegrationTableMappingName: Code[20])
    var
        Customer: Record Customer;
        CRMAccount: Record "CRM Account";
    begin
        IntegrationFieldMapping.CreateRecord(
            IntegrationTableMappingName, Customer.FieldNo(Blocked), CRMAccount.FieldNo(TKA_Blocked),
            IntegrationFieldMapping.Direction::Bidirectional, '', true, false
        );
    end;

    [EventSubscriber(ObjectType::Codeunit, Codeunit::"CDS Setup Defaults", OnAfterResetVendorAccountMapping, '', false, false)]
    local procedure OnAfterResetVendorAccountMappingCDSSetupDefaults(IntegrationTableMappingName: Code[20])
    var
        Vendor: Record Vendor;
        CRMAccount: Record "CRM Account";
    begin
        IntegrationFieldMapping.CreateRecord(
            IntegrationTableMappingName, Vendor.FieldNo(Blocked), CRMAccount.FieldNo(TKA_Blocked),
            IntegrationFieldMapping.Direction::Bidirectional, '', true, false
        );
    end;
}

These three objects are almost completely the same as in the previous article (except we are using different field names). You should understand every line from these objects.

So, how to create a custom transformation? We need to subscribe to an event that is raised when a field is updated – in Codeunit “Integration Record Synch.” event OnTransferFieldData. This event is called for every field and is used for both transferring data, but also for decided if the field has changed since the last synchronization run.

As parameters, you get Source and Destination FieldRef (Source – table that has the new values, Destination – table we want to update), and three var parameters to indicate if we set the value, what is the new value and if we want to run standard conversion for found value.

I usually have a case statement and different procedures for different tables. In our example, we need four different procedures – CustomerToCRMAccount, CRMAccountToCustomer, VendorToCRMAccount and CRMAccountToVendor.

    [EventSubscriber(ObjectType::Codeunit, Codeunit::"Integration Record Synch.", OnTransferFieldData, '', false, false)]
    local procedure OnTransferFieldDataIntegrationRecordSynch(SourceFieldRef: FieldRef; DestinationFieldRef: FieldRef; var NewValue: Variant; var IsValueFound: Boolean; var NeedsConversion: Boolean)
    begin
        case CRMHelpers.GetSourceDestinationCode(SourceFieldRef.Record(), DestinationFieldRef.Record()) of
            CRMHelpers.GetCustomerToCRMAccountCode():
                UpdateCRMAccountFromCustomerOnTransferFieldData(SourceFieldRef, DestinationFieldRef, NewValue, IsValueFound, NeedsConversion);
            CRMHelpers.GetCRMAccountToCustomerCode():
                UpdateCustomerFromCRMAccountOnTransferFieldData(SourceFieldRef, DestinationFieldRef, NewValue, IsValueFound, NeedsConversion);
            CRMHelpers.GetVendorToCRMAccountCode():
                UpdateCRMAccountFromVendorOnTransferFieldData(SourceFieldRef, DestinationFieldRef, NewValue, IsValueFound, NeedsConversion);
            CRMHelpers.GetCRMAccountToVendorCode():
                UpdateVendorFromCRMAccountOnTransferFieldData(SourceFieldRef, DestinationFieldRef, NewValue, IsValueFound, NeedsConversion);
        end;
    end;

The CRMHelpers codeunit is my custom codeunit that defines the mapping codes. In the base app, these texts as defined as text constants, I prefer this dynamic solution, but we just need to identify which record we are changing.

codeunit 50050 "TKA CRM Helpers"
{
    var
        SourceDestinationCodeLbl: Label '%1-%2', Locked = true;

    procedure GetSourceDestinationCode(SourceRecordRef: RecordRef; DestinationRecordRef: RecordRef): Text
    var
    begin
        if (SourceRecordRef.Number <> 0) and (DestinationRecordRef.Number <> 0) then
            exit(StrSubstNo(SourceDestinationCodeLbl, SourceRecordRef.Name(), DestinationRecordRef.Name()));
        exit('');
    end;

    procedure GetCRMAccountToCustomerCode(): Text
    var
        Customer: Record Customer;
        CRMAccount: Record "CRM Account";
    begin
        exit(StrSubstNo(SourceDestinationCodeLbl, CRMAccount.TableName(), Customer.TableName()));
    end;

    procedure GetCustomerToCRMAccountCode(): Text
    var
        Customer: Record Customer;
        CRMAccount: Record "CRM Account";
    begin
        exit(StrSubstNo(SourceDestinationCodeLbl, Customer.TableName(), CRMAccount.TableName()));
    end;

    procedure GetCRMAccountToVendorCode(): Text
    var
        Vendor: Record Vendor;
        CRMAccount: Record "CRM Account";
    begin
        exit(StrSubstNo(SourceDestinationCodeLbl, CRMAccount.TableName(), Vendor.TableName()));
    end;

    procedure GetVendorToCRMAccountCode(): Text
    var
        Vendor: Record Vendor;
        CRMAccount: Record "CRM Account";
    begin
        exit(StrSubstNo(SourceDestinationCodeLbl, Vendor.TableName(), CRMAccount.TableName()));
    end;
}

More interesting are procedures called UpdateXXXFromYYYOnTransferFieldData, for example, UpdateCRMAccountFromCustomerOnTransferFieldData. What is in these procedures? It’s a simple code – first, you need to check if the source and destination fields are those fields you want to map/transform manually. I usually have case true statements with source/destination field checks.

Then we need to transfer data from the variant variable we got from the publisher, implement our logic and return the target value by assigning the NewValueVariant parameter. Do not forget to set IsValueFound to true to skip standard behaviour.

There is also a parameter called NeedsConversion. This parameter indicates whether you want to run standard conversion for your found value. This can be useful when you are not sure about returned value content, and I always recommend setting the variable to true for all blob, date, time and DateTime fields.

    local procedure UpdateCRMAccountFromCustomerOnTransferFieldData(SourceFieldRef: FieldRef; DestinationFieldRef: FieldRef; var NewValueVariant: Variant; var IsValueFound: Boolean; var NeedsConversion: Boolean)
    var
        Customer: Record Customer;
        CRMAccount: Record "CRM Account";
        TempOption: Integer;
    begin
        case true of
            (SourceFieldRef.Number() = Customer.FieldNo(Blocked)) and (DestinationFieldRef.Number() = CRMAccount.FieldNo(TKA_Blocked)):
                begin
                    TempOption := SourceFieldRef.Value();
                    case TempOption of
                        Customer.Blocked::All.AsInteger():
                            NewValueVariant := CRMAccount.TKA_Blocked::All;
                        Customer.Blocked::Invoice.AsInteger():
                            NewValueVariant := CRMAccount.TKA_Blocked::Invoice;
                        Customer.Blocked::Ship.AsInteger():
                            NewValueVariant := CRMAccount.TKA_Blocked::Ship;
                        else
                            NewValueVariant := CRMAccount.TKA_Blocked::" ";
                    end;
                    IsValueFound := true;
                    NeedsConversion := false;
                end;
        end;
    end;

That’s all! The next part will focus less on AL Language and our customization but more on how to use OOTB functionality for some more advance data manipulation during integration (transformation, integration mapping etc.)