Note
A more extensive Twinfield driver is now also available with Invantive Cloud.
Summary
The Twinfield proxy from BI-ON-FOCUS can be used by Invantive Data Replicator to replicate many Twinfield companies into a relational database for use with BI and analysis tools. This note describes how to code the Twinfield replication.
Since 2020 Invantive SQL provides native support on Twinfield API in the form of a new connector.
Twinfield
Currently, neither Invantive Data Replicator nor Invantive SQL provides a connector with the Dutch accounting package Twinfield. However, Twinfield partners with a number of app suppliers to provide an interface to the data managed by Twinfield. One of these partners is BI-ON-FOCUS, which provides a proxy gateway between Twinfield and Power BI.
This proxy can be used - when used in accordance to the agreement - to also open up Twinfield for other sources using Invantive Data Replicator and so provide massive reporting across thousands of companies with a variety of solutions such as Exact Online, Visma.net, Salesforce or Loket.
Invantive Data Replicator
Invantive Data Replicator uses the connectors provided by Invantive SQL to access source systems and replicate the data contained in those to a traditional platform such as SQL Server, Oracle, MySQL, PostgreSQL or the Invantive storage format. These replicas and their metadata is maintained automatically. Depending on the needs and supported possibilities, a number of optimizations are present like incremental trickle loading or smart replication. The data flow is depicted below:
Retrieving Data from a Proxy
The Twinfield for Power BI solution from BI-ON-FOCUS uses a proxy on HTTP which retrieves, caches and provides Twinfield contents in JSON format. Authentication and autorization is done using credentials per office provided transported using Basic Authorization. The credentials include the Office number (a Twinfield concept) as a user name and an access token as password.
Twinfield Proxy Credentials
Since Data Replicator is normally used for hundreds or thousands of source, we will use a small in-memory structure to contain the credentials per Twinfield Office. For improved security, we recommend to use an encrypted storage like the bundled KDBX connector or encrypted storage in a relation database.
The credentials for the proxy are made available as an in-memory structure using:
select crd.*
from csvtable
( passing '372012345012,abcdef1234567890'
|| '#483122355900,abcdef1234567890'
row delimiter '#'
column delimiter ','
columns office_number varchar2 position 1
, access_token varchar2 position 2
) crd
The results resembles:
Twinfield Transaction Download
The transaction data from the Twinfield proxy can then be downloaded by extending the query by a HTTP download, setting the Content-Type request header to application/json and presenting the credentials using basic authorization:
select crd.office_number
from csvtable
( passing '372012345012,abcdef1234567890'
|| '#483122355900,abcdef1234567890'
row delimiter '#'
column delimiter ','
columns office_number varchar2 position 1
, access_token varchar2 position 2
) crd
join httpdownload@datadictionary
( url => 'https://twinfield.bi-on-focus.nl/transactions?type=current'
, contentType => 'application/json'
, headers => 'Authorization=Basic%20'
|| urlencode(base64_encode(ascii_to_blob(crd.office_number || ':' || crd.access_token)))
) htp
The httpdownload table function is available with the DataDictionary connector. To see all available options, please press F4 in the Invantive Query Tool with the cursor positioned on httpdownload.
The ascii_to_blob encodes the Basic authentication text with user and password to a BLOB, which is then encoded using base64. Ultimately, the urlencode ensures that special characters such as trailing ‘=’ are escaped sufficiently for use in a header.
Twinfield Transactions in Table Format
The download from the Twinfield proxy is returned in JSON format. In this last step, we transform the data in JSON format to a table format:
select crd.office_number
, dta.*
from csvtable
( passing '372012345012,abcdef1234567890'
|| '#483122355900,abcdef1234567890'
row delimiter '#'
column delimiter ','
columns office_number varchar2 position 1
, access_token varchar2 position 2
) crd
join httpdownload@datadictionary
( url => 'https://twinfield.bi-on-focus.nl/transactions?type=current'
, contentType => 'application/json'
, headers => 'Authorization=Basic%20'
|| base64_encode(ascii_to_blob(crd.office_number || ':' || crd.access_token))
) htp
join jsontable
( '[*][*]'
passing htp.content_clob
columns period varchar2 path 'Period'
, name varchar2 path 'Name'
, status varchar2 path 'Status'
, customer varchar2 path 'Customer'
, currency varchar2 path 'Currency'
, value_fc varchar2 path 'Value'
, value_eur varchar2 path 'Euro'
, transaction_type varchar2 path '[''Transaction type'']'
, transaction_date varchar2 path '[''Trans. date'']'
, value_usd varchar2 path '[''$'']'
, open_amount varchar2 path '[''Open amount'']'
, invoice_number varchar2 path '[''Invoice no.'']'
, due_date varchar2 path '[''Due date'']'
, payment_status varchar2 path '[''Payment status'']'
, payment_number varchar2 path '[''Payment no.'']'
, pay_date varchar2 path '[''Pay date'']'
) dta