Synchronize Customers Across Exact Online Companies

Invantive SQL includes many SQL-extensions to ease data replication and synchronization across (cloud) applications and within cloud applications across partitions. In this article I will illustrate the synchronization of Exact Online customers across multiple Exact Online companies.

Please use an Invantive SQL frontend such as Invantive Query Tool, Invantive Control for Excel or Invantive Cloud To execute this sample. A free trial for 180 days is available from the Invantive Cloud website.

The basics illustrated here also work across cloud applications; in a similar way you can synchronize customers for instance between Exact Online, Teamleader and ActiveCampaign. At Invantive our back office consists of seven systems, all with a synchronized data set. That works great; sole disadvantage is that a data entry error in one system automatically replicates everywhere, sometimes increasing the impact of an error. A number of features are available in Invantive SQL to avoid automated massive problems, but still always you need to test, verify and check your functionality.

As a preparation for this sample make sure you have at least two Exact Online companies in one or multiple countries. Choose one Exact Online company to be the master company, containing the customers master. Other Exact Online companies will be used as targets. There is no upper limit to the number of Exact Online companies you can replicate the data to; the sample can be used for replication into 500 companies without changes.

The first step is to select the source company and the target companies. The target companies will be memorized in an in-memory table. This step takes a few milliseconds.

local define SOURCE_COMPANY "102673"

create or replace table targetcompanies@inmemorystorage
-- Randomly select 10 companies.
select code 
from   systemdivisions 
where  code != ${SOURCE_COMPANY}
-- Uncomment the following to choose just one specific target company.
-- code = 868056
limit 10

The next step is to choose a selection or all customers in the source company. The following queries first select the source company and then selects all construction companies and combines them with the target companies using a cartesion join, which takes one second upto a few hundred companies:


-- Create cartesian product of target companies and
-- Exact Online accounts to load.
create or replace table accounts_soll@inmemorystorage
select tgt.code
,      act.code
,      act.chamberofcommerce
from   exactonlinerest..accounts act
join   targetcompanies@inmemorystorage tgt
where  act.division = ${SOURCE_COMPANY}
and like 'Aanneming%'

The intended situation has now been stored in an in-memory table, containing maybe 5.000 customers. The final step switches focus to the chosen target companies with the ‘use’ statement.

The final synchronize statement is the real work horse. It compares the existing customers in the target companies with the intended customers. Customers already registered on the same code will be updated, wherease new customers will be loaded into the Exact Online companies with the code, name and chamber of commerce number provided:

-- Focus on all selected target companies.
use select code from TARGETCOMPANIES@InMemoryStorage

synchronize exactonlinerest..accounts
from        accounts_soll@inmemorystorage
with        insert
by          division
,           code
continue on first 500 errors

This last step typically takes 200 ms per new and/or updated customer.