How do I create an Exact Online API interface?

Wiki (en)
, , , ,
1

Dutch version

Exact Online interfaces

An API connection with Exact Online allows you to quickly and efficiently process data from other systems or Excel sheets into your accounting system. An Exact Online API interface saves a lot of time and prevents mistakes during repetitive actions.

Exact Online has over 1.000 API’s which are all accessible via Invantive SQL. Invantive SQL embeds the complex techniques needed so you can focus on the functionality thanks to SQL statements that are real-time converted into read and write actions on Exact Online and over 70 other (cloud) platforms.

Often Excel files are also used as the source of an API interface. Most users can prepare an Excel sheet much faster than entering sales invoices piece by piece with similar data each time. Excel files can be read both interactive through the Excel add-in Invantive Control for Excel with Exact Online as through uploads on Get My Report as well as massively through Invantive Data Hub.

How do I create an Exact Online API interface?

Making an Exact Online API connection or multiple API connections between different systems where Exact Online is also connected can be done in several ways:

  • REST APIs: the tables starting with ExactOnlineREST are REST APIs.
  • XML APIs: the tables starting with ExactOnlineXML are the XML APIs.

Each REST API and XML API is already defined as a table. No more work is required here within the Exact Online API interface.

Exact Online forms one side of the API interface. For the other side of the API connection you can choose from over 70 platforms such as various Visma products but also Chargebee, ActiveCampaign, Teamleader, Yuki or AFAS. Also for all these platforms, the interface has access to every API: every API is already predefined and tested, and immediately usable.

A schematic representation of the Exact Online data model can be found at:

A searchable overview of the available Exact Online tables can be found at:

Detailed examples of Exact Online API interfaces

For common combinations with an Exact Online interface, scripts for API interfaces are already available. In case of doubt whether an Exact Online interface is already coded, we advise you to leave a comment below.

Some popular examples of interfaces with Exact Online with statements ready to run are:

There are also predefined API interfaces such as the following in Get My Report:

Manual or unattended/automatic

Processing data with an Exact Online API interface can be done in several ways: both manual and fully automatic are possible. In general, it is wiser to start with a manual Exact Online link if the processes and/or data have not been processed automatically before. If few operational issues remain one can link Exact Online fully automatically.

It is however desirable to regularly check the status of a fully automated link. This can be done via emails from the links or by reading the processing reports from the software.

Retrieving data from Exact

Retrieving data from Exact Online works immediately after logging into Exact Online with user name, password and verification code with, such as for example:

select *
from ExactOnlineREST.Journals

on the Exact Online REST API or the equivalent Exact Online XML API but with different and more field names:

select *
from ExactOnlineXML..Journals

This approach is the same on all products. This approach with select is besides for an Exact Online interface also the same for the other systems.

Exchanging the data with the interface between Exact Online and the other platform can be done via the insert, update and delete statements, but synchronizing is even more convenient with synchronize. An example of synchronize for linking Exact Online and Visma.net Financials can be found in Synchronize Exact Online with Visma.net Financials. After the synchronize statement ends, two tables are synchronized in terms of content. The synchronize statement itself figures out in a jiffy which mutations are needed and can be processed efficiently.

XML loading in Exact

For fast loading of seriously large numbers of transactions into Exact Online, the Exact Online XML API is recommended. This uses very few API calls and processing time for large volumes, as demonstrated in our research described at Does Invantive SQL match the speed of Infinite Probability Drive?.

Loading via the Exact Online XML API is a bit more laborious than via the Exact Online REST API and works via the table UploadXMLTopics. An example of this is at Inlezen extra velden van Exact Online artikelen (Dutch). Linking can be done for dozens of types of data such as creating journal entries, adding invoices, mutating orders, loading CRM relationships and loading product information for production.

A good test is to first use Get My Report via an upload module to load an Excel sheet with for example 100,000 sales orders into Exact and see if you like it.

Load transactions via Exact Online REST API

Exact Online has a little mischief in the software. The traditional difference between header and lines in transactions seems to be absent. Therefore, for example, every sales order must have at least one line. The XML API always provides all data in one go. For the Exact Online REST API, loading sales orders, purchase and sales invoices, orders and accounting entries therefore requires transaction logic. An example via the REST API is elaborated in Create an Exact Online Sales Order with Details through REST Calls.

Multiple Exact Online companies

Most entrepreneurs have only a few Exact companies in their subscription, but large accountants who use Invantive often have hundreds or thousands of companies for which they keep accounts of hundreds of customers. Especially for accountants it is necessary to select the right companie(s) from the accounts.

By default the data of all selected Exact Online companies will be put together. The field Division (REST) or DIVISION_CODE contains the unique number of the Exact company. The concept of companies (“Divisions”) is further explained in the right column of the API diagram.

The desired Exact companies of the accounting for the software link are selected depending on the product:

API Performance

A cloud application’s API is slower than exchanging data with a remote device. Yet there are gradations; if we look across all 1,000 linked APIs, we measure huge speed differences between largely similar APIs.

The fastest way right now to retrieve data from Exact Online are the sync APIs as described at Faster Exact Online download using incremental "sync API"-tables.

A general survey on the speed of different ways to read Exact Online with a comparison between the Exact Online XML API, the Exact Online REST API, SQL Server and SQL Server with bulk uploads can be found at Does Invantive SQL match the speed of Infinite Probability Drive?.

Integration of Power BI with Exact Online

For Power BI, there is a predefined link that allows all 1,000 available tables to be imported into Power BI Desktop, Power BI Service and Power Query for Excel. This Exact Online Power BI link can be found at:

A step-by-step plan for linking Exact Online with Power BI can be found at:

Exact Online Consolidate with Excel

Specifically for the Windows version of Microsoft Excel, there is an Exact Online Excel add-in. This allows you to use regular Excel formulas to process the data from Exact Online companies into Excel spreadsheets. The most commonly used Excel formulas for Exact Online can be found at The most popular Exact Online Excel add-in formulas. The software can be downloaded as MSI file.

Integrating Azure Data Factory with Exact Online

For Azure Data Factory, there is a predefined link for loading all 1,000 Exact Online tables into Azure Data Factory. This Exact Online Azure Data Factory link can be found at:

A step-by-step guide for linking Exact Online with Azure Data Factory can be found at:

Exact Online API Status

A connection with Exact Online requires a stable Internet connection, but the Exact Online APIs can also suffer from failures, ranging from slow operation to just being completely unreachable. The status of the Exact Online APIs can be retrieved with the tips described at Hoe vraag ik de status van een Exact Online storing op? (Dutch).

The Exact Online API status is also partially available through the Invantive Status page; see Status page expanded with throughput and Exact Online measurements.

Preventing malfunctions by Exact Online API Adjustments

The API is constantly undergoing changes. This often leads to unexpected API failures. Sometimes the cause of a failure is automatically resolved after a few days because someone else complained. But sometimes it is necessary to contact Exact Online API Support yourself. When using Invantive SQL this will normally rarely be necessary because for most common problems solutions have already been processed under the hood.

A number of factors to take into account when creating Exact Online REST API links are described at Impact Exact Online API adjustments July 1, 2021. Taking these into account will prevent errors and reduce the chance of REST API failures.

Rights and Scoping

The Exact Online API if you have to fully control it yourself for both accountants and business owners has many nuances in terms of permissions and so-called “scoping”.

The rights on an API of a user can be determined with the tips on Exact Online API rechten van een user op specifieke endpoint - 2 van forums (Dutch).

These are roughly the rights that a user also has on start.exactonline.nl, but each API requires a so-called “OAuth app”. The OAuth app can be registered by an Exact Online user with “Manage Subscription” rights at https://apps.exactonline.com.

A distinction is made between self and published apps. A self-registered app only gives access to its own companies, and is not affected by the under described “data scoping” and “division scoping”. User rights are applied, however.

An example of registering a proprietary app for on-premises use is described in Register Exact Online app for use with Invantive Control for Excel, while an example for a web app can be found in Register Exact Online app for use with Invantive Cloud.

A published app can exchange data with any administration after approval by a user of any company.

The rights for data exchanges are limited by:

  • data scoping: published apps can only access data elements that the publisher has indicated are needed (note: there are security holes)
  • division scoping: published apps only have access to companies that the user has selected.

Both data and division scoping are relatively susceptible to bugs to the Exact Online. It is also relatively difficult to properly test all variations in advance and also to build a logical collaborative whole.

Securing API Usage

Over time, more and more APIs can become linked and potentially fall out of focus. Exact therefore lets unused refresh tokens (application-specific passwords) expire after some time, but it is still wise to regularly check which apps and links are all running. The apps and links used can be found as described at Hoe zie ik welke Exact Online apps wij gebruiken? (Dutch).

Revoking permissions on an Exact app / link can be done from within the Exact website as described at How do I revoke app permissions for Exact Online?.

Need help?

Do you need help? Please leave a question on the site https://forums.invantive.com or contact Invantive by phone or e-mail. Invantive can also configure the Exact-interface for you based on the already available drivers.