good morning @kha
to share, on my side, we are using a mix of Query Tool and Data Hub to replicate one-way sync information from Exact Online to our on-premise-AWS-SqlServer. We have 7 divisions on Exact Online.
Then we are able to do our own kitchen on the SQL server…
Quer yTool is used first to create and test queries, then when the query is validated, working and tested from Query Tool we migrate to an automated daily script with Data Hub.
It works like a charm.
The only point is that replicating all tables is not a behaviour to expect… given the numbers of endpoints of the Exact Online API.
For example we have one script that replicates the cash position from all the banks:
USE division1@eol division2@eol etc…;
SELECT
bke2.Division,
bke2.DivisionCompanyName,
bke2.JournalCode,
bke2.JournalDescription,
bke2.OpeningBalanceFC,
bke2.ClosingBalanceFC,
bke2.FinancialYear,
bke2.FinancialPeriod,
bkemax.LastEntryDate AS LastMovDate,
SUM(CASE WHEN bkel2.AmountDC<0 THEN bkel2.AmountDC + (CASE WHEN bkel2.AmountVATFC IS NULL THEN 0 ELSE bkel2.AmountVATFC END) ELSE 0 END) AS Debit,
SUM(CASE WHEN bkel2.AmountDC>0 THEN bkel2.AmountDC + (CASE WHEN bkel2.AmountVATFC IS NULL THEN 0 ELSE bkel2.AmountVATFC END) ELSE 0 END) AS Credit
FROM BankEntries@eol bke2
LEFT JOIN
( SELECT
bke.Division,
MAX(bke.EntryNumber) AS EntryNumber,
bke.JournalCode,
MAX(bkel.Date) AS LastEntryDate
from bankEntries@eol bke
LEFT JOIN BankEntryLines@eol bkel ON bke.EntryID = bkel.EntryID
WHERE bke.created >= sysdateutc- 31
GROUP BY bke.Division, bke.JournalCode
) bkeMax
ON (bke2.Division = bkeMax.Division And bke2.EntryNumber = bkeMax.EntryNumber)
LEFT JOIN BankEntryLines@eol bkel2 ON bke2.EntryID = bkel2.EntryID
WHERE bke2.created >= sysdateutc- 31
GROUP BY
bke2.Division,
bke2.DivisionCompanyName,
bke2.JournalCode,
bke2.JournalDescription,
bke2.OpeningBalanceFC,
bke2.ClosingBalanceFC,
bke2.FinancialYear,
bke2.FinancialPeriod,
bkemax.LastEntryDate
or scripts to replicate selected tables:
USE division1@eol division2@eol etc…;
create or replace table eol_rest_salesinvoicesbulk@sqlContainer
as select * from salesinvoicesbulk@eol
create or replace table eol_rest_subscriptions@sqlContainer
as select * from ExactOnlineREST.Subscription.subscriptions@eol
create or replace table eol_rest_Subscriptionlinesbulk@sqlContainer
as select * from ExactOnlineREST.Subscription.SubscriptionLines@eol
create or replace table eol_rest_salesinvoicelinesbulk@sqlContainer
as select * from salesinvoicelinesbulk@eol
create or replace table eol_rest_glaccountsbulk@sqlContainer
as select * from ExactOnlineREST.Financial.glaccountsbulk@eol
create or replace table eol_rest_GlClassificationsBulk@sqlContainer
as select * from ExactOnlineREST.Financial.GLClassificationsBulk@eol
create or replace table eol_rest_GLAccountClassificationMappings@sqlContainer
as select * from ExactOnlineREST.GLAccountClassificationMappings@eol
create or replace table eol_xml_balancelines@sqlContainer
as select * from ExactOnlineXML.XML.BalanceLines@eol BL
create or replace table eol_rest_transactionlinesincremental@sqlContainer
as select * from transactionlinesincremental@eol