Synchronizing countries from D365 Sales to D365 Business Central

Surprisingly, the out-of-the-box integration between Microsoft Dynamics 365 Sales and Microsoft Dynamics 365 Business Central does not synchronize countries. Why? Because it could be hard to maintain this synchronization as, by default, the country field in D365 Sales is just a plain text field (and, as you know, in Business Central, we have a standalone Country/Region table).

The main problem here is not the synchronization itself but possible data inconsistency (as users in D365 Sales can type any value in the country field and the synchronization won’t be able to figure out the correct country in Business Central).

However, you can customize D365 and limit possible values or add some form of validation. This can be done relatively quickly, and once you have such validation in place, you can add support for the country synchronization to the standard connector. How? Continue reading.

We probably want to add custom data transfer process to allow custom country mapping. For this purpose, we can use one of the core events in Codeunit 5336 Integration Record Synch. – OnTransferFieldData.

codeunit 5336 "Integration Record Synch."
{
    ...

    [IntegrationEvent(false, false)]
    local procedure OnTransferFieldData(SourceFieldRef: FieldRef; DestinationFieldRef: FieldRef; var NewValue: Variant; var IsValueFound: Boolean; var NeedsConversion: Boolean)
    begin
    end;
}

Once we know the publisher, we can easily create a subscriber. We need 4 parameters:

• SourceFieldRef: FieldRef = to check whether the field we got is the one we want to use for the data transfer transformation
• DestinationFieldRef: FieldRef = to check whether the field we got is the one we want to use for the data transfer transformation
• NewValue: Variant = to return the new value
• IsValueFound: Boolean = to indicate whether we found the new value or we want to run the standard process

[EventSubscriber(ObjectType::Codeunit, Codeunit::"Integration Record Synch.", 'OnTransferFieldData', '', false, false)]
procedure SetCountryInBCFromCRM(SourceFieldRef: FieldRef; DestinationFieldRef: FieldRef; var NewValue: Variant; var IsValueFound: Boolean)
begin
    // CHECK IF SOURCE AND DESTINATION FIELDS ARE COUNTRY FIELDS
    IsValueFound := FindCountryRegionCodeFromCRMCountry(SourceFieldRef.Value(), NewValue);
end;

And how are we mapping countries? We are following the basic logic – first, we try to find the CRM country in the Country/Region table in the Code field, in the Name field (exact or partial match), and if we do not find the corresponding value, we have a custom mapping table that we are using for countries that (for example) we want to map to another country, or if the country has multiple names)

procedure FindCountryRegionCodeFromCRMCountry(CRMCountryName: Text; var BCCountryCode: Code[10]): Boolean
begin
    if CRMCountryName = '' then
        exit(false);

    CountryRegion.SetRange(Code, CopyStr(CRMCountryName, 1, MaxStrLen(CountryRegion.Code)));
    // IF FOUND, RETURN FOUND COUNTRY

    CountryRegion.SetRange(Name, CRMCountryName);
    // IF FOUND, RETURN FOUND COUNTRY

    CountryRegion.SetFilter(Name, '@%1', CRMCountryName);
    // IF FOUND, RETURN FOUND COUNTRY

    CRMCountryMapping.SetRange("CRM Country Name", CRMCountryName);
    // IF FOUND, RETURN FOUND COUNTRY

    CRMCountryMapping.SetFilter("CRM Country Name", '@%1', CRMCountryName);
    // IF FOUND, RETURN FOUND COUNTRY

    // THROW AN ERROR IF NOT FOUND
end;