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
as
--
-- 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:
use ${SOURCE_COMPANY}
--
-- Create cartesian product of target companies and
-- Exact Online accounts to load.
--
create or replace table accounts_soll@inmemorystorage
as
select tgt.code
division
, act.code
, act.name
, act.chamberofcommerce
from exactonlinerest..accounts act
join targetcompanies@inmemorystorage tgt
where act.division = ${SOURCE_COMPANY}
and act.name 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
or
update
identified
by division
, code
continue on first 500 errors
This last step typically takes 200 ms per new and/or updated customer.