SQL Database for Exact via Invantive

Hi,

I would like to know if there is a tool in Invantive that will get the data from Exact Online and house it in a MySQL database that I can access/connect to directly. I would like to have an SQL connection to Exact Online data and I was hoping Invantive would have a possible way to do it.

There are various options:

Note that MySQL has no bulk loader; each individual row must be loaded one-by-one, whereas for instance SQL Server, Oracle and PostgreSQL offer bulk loaders for significant higher throughput.

Note that Data Replicator is for use in large enterprise environments with thousands or more of companies.

Thanks for your response.

I currently have a subscription with Invantive Office for Entrepreneurs.

Do I need an additional subscription for the above mentioned options?

Invantive Office for Entrepreneurs covers the three Invantive Cloud options. Data Hub and Replicator are separate subscriptions.

The coverage of a subscription can also be found on Invantive Cloud:

  • select “Agreements” in the left menu bar,
  • select your agreement,
  • select the blue underlined product code (something like “SUB…”),
  • the details are displayed.

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
1 like