Exact Online is the business software market leader in the Benelux. Exact Online is a go-to-provider for companies looking to automate their accounting, financial, ERP, HRM and CRM processes. Exact Online also offers a range of industry specific solutions to fully manage all of your business processes needs. Website: https://www.exact.com/
This article provides an overview to get started with the Exact Online connector in Peliqan. Please contact support if you have any additional questions or remarks.
Contents
- Contents
- Exact Online Data Pipeline
- Connect Exact Online
- Exact Online data sync to a data warehouse
- Configure custom sync frequency for selected tables
- Exact Online Data Activation
- Report Exact Online data to Spreadsheets and BI tools
- Dealing with Documents
- Creating a Document
- Updating a document
- Finding a Document
- Upserting a Document
- Finding Document Attachments
- Adding Attachments to a Document
- Delete records in the DWH that were deleted in Exact Online
- Custom ETL pipeline to sync a selection of divisions from Exact Online
- Need further help
Exact Online Data Pipeline
Connect Exact Online
Connect Exact online through the Peliqan Connectivity module:
Configuration options:
- Starting year
- Country
- (Optional) table selection via ‘Advanced’
Start the oAuth connection flow by selecting ‘Connect Exact Online’:
- Provide your username and password
- Authorize Peliqan to access your data
Select the required divisions you want the data to be synced from and select ‘Connect Exact Online’:
Exact Online data sync to a data warehouse
Peliqan offers an out of the box data warehouse. Optionally the user can choose to sync Exact online data to their own data warehouse such as Snowflake, Google BigQuery, MS SQL etc.
Data will be synced to the chosen data warehouse and made available through the Peliqan UI for exploration.
With the Peliqan federated query engine, queries on the Exact Online data can be written and data can be transformed and combined with other sources.
Configure custom sync frequency for selected tables
We’ve set the default sync frequency for the Exact Online connector to every 6hours due to the heavy rate limits Exact Online imposes.
However for many cases it is required to have a higher sync frequency for a specific set of tables: e.g. TransactionLines. With the Peliqan data activation library you con configure a more frequent sync for desired tables:
- Some details:
Exact Online Data Activation
Report Exact Online data to Spreadsheets and BI tools
With the Peliqan Excel add-in you can easily sync your Exact data or query output to Excel and use the powers of Excel to report on the data.
Connectivity to a BI tool such as Microsoft Power BI can be setup using the Postgres datasource credentials in the connection module.
Data sync with google sheet can be achieved by a data app writeback script.
Dealing with Documents
Peliqan offers Exact Online data activation functions for adding "Documents" and attaching files to documents. Attachments can be added to the document in the Base64 format after the document object has been generated.
Creating a Document
Updating a document
With document_update endpoint we can easily update the document object details and verify them using document_findone method:
Finding a Document
“FindOne Document” method allows user to find document based on Unique Document Name( Subject ). Name of document must always be a unique name.
Upserting a Document
“document_upsert” method allows users to add or update an object if the document with provided unique document name is present. In the below example, we performed upsert on document with “Subject” as serachfield that represents document name, which first searches for document with name = “327_INV/2024/00003”, and once it founds the object it updates the document details.
Now, again we perform upsert with document name “327_INV/2024/00003” as the name was replaced means no document object is present with the same. In this case upsert function creates a new document object with the same name.
Finding Document Attachments
With Peliqan, Document Attachments can be found in two ways, either by providing Document Attachment ID or Document Attachment Name. Make sure that you provide unique Filename.
Adding Attachments to a Document
Attachments/Files data are converted to Base64 byte Object and passed along with Document object ID & unique attachment Name
Delete records in the DWH that were deleted in Exact Online
The table deleted
provides a list of records that were deleted in Exact Online. In order to delete those records in the Peliqan data warehouse, you can run the below script on e.g. a daily or hourly schedule. This script performs a JOIN with the table entitytype
:
# This script will delete records in the DWH that were deleted in the source (Exact Online)
schema = 'exact online'
st.write("Getting most recent deleted records from Exact Online.")
pq.run_pipeline(connection_name = 'exact online', is_async = False, tables="deleted")
st.write("Done getting new deleted records.")
dbconn = pq.dbconnect(pq.DW_NAME)
schema = '"' + schema + '"'
ts = pq.get_state()
if not ts:
ts = "2020-01-01 00:00:00"
query = f"""
SELECT d.entitykey, d.division, e.name as entityname, d._sdc_batched_at
FROM {schema}.deleted d
INNER JOIN {schema}.entitytype e
ON d.entitytype = e.number
WHERE d._sdc_batched_at > '{ts}'
ORDER BY d._sdc_batched_at
"""
rows = dbconn.fetch(pq.DW_NAME, query = query)
st.write("Deleting %s records." % len(rows))
for row in rows:
table_name = schema + '.' + row["entityname"].lower()
id = row["entitykey"]
division = row["division"]
delete_query = f"DELETE FROM %s WHERE id='%s' AND division=%s" % (table_name, id, division)
result = dbconn.execute(pq.DW_NAME, query = delete_query)
st.write("Deleting %s with id %s in division %s: %s." % (row["entityname"], id, division, result["status"]))
if len(rows):
pq.set_state(row["_sdc_batched_at"])
Custom ETL pipeline to sync a selection of divisions from Exact Online
The standard ETL pipeline from Peliqan can sync either All Divisions or one specific Division. If you have hundreds of divisions in Exact Online, and you want to sync a selection only, you can use the below custom pipeline script. This script uses a table as input.
# Sync only selected divisions from Exact Online
# Uses a table as input, set table name below.
# Todo: add schedule every minute to this script (to process "Sync now")
table_name = "exact_online_divisions_to_sync.divisions"
connection_name = "Exact_online"
import time, datetime, pytz
dbconn = pq.dbconnect(pq.DW_NAME)
#Check if daily sync needed
last_sync = pq.get_state()
current_epoch_time = int(time.time())
if not last_sync or current_epoch_time - last_sync > 86400:
pq.set_state(current_epoch_time)
st.write("Doing daily sync of divisions.")
query_divisions_to_sync = f"SELECT * FROM %s WHERE sync_enabled = TRUE OR sync_now = TRUE" % table_name
else:
st.write("Doing 'Sync Now' divisions only.")
query_divisions_to_sync = f"SELECT division_id FROM %s WHERE sync_now = TRUE" % table_name
# Get divisions to sync from table, run pipeline per division, and update row in table
rows = dbconn.fetch(pq.DW_NAME, query = query_divisions_to_sync)
for row in rows:
division = row["division_id"]
st.write(f"Running ETL pipeline for Exact Online division: %s" % division)
run_pipeline_result = pq.run_pipeline(connection_name = connection_name, is_async = False, categories = str(division))
run_pipeline_details = pq.get_pipeline_runs(connection_name = connection_name, run_id = run_pipeline_result["run_id"])
status = run_pipeline_details["data"][0]["status"]
ts = datetime.datetime.now(pytz.timezone('Europe/Brussels'))
update_query = f"UPDATE %s SET sync_now = FALSE, last_sync = '%s', status = '%s' WHERE division_id = %s" % (table_name, ts, status, division)
dbconn.execute(pq.DW_NAME, query = update_query)
Need further help
Please contact our support for any further assistance via support@peliqan.io.