Reading in extra fields of Exact Online articles

Extra Fields on Items

Exact Online allows users to define extra fields on items. The extra fields can, for example, contain text or a value from a list. The structure of the extra fields is set up per administration in Exact Online as master data. More information about these extra item fields can be found at Oproepen van Extra Velden in de Power BI connector - 2 van forums.

Reading via REST API

Reading the values of the extra fields can be done via the tables based on the Exact Online REST APIs, such as ItemExtraFieldValues. Originally, it was only possible to read the contents of extra fields per item, but this can now also be done for multiple items at once, which improves speed. The API for the fields does not excel in stability; therefore, allow some extra time when building integrations.

Importing via XML API

Importing data into the free fields can only be done via the Exact Online XML API. The following instructions explain how.

You can import the values via the UploadXMLTopics view. When performing an insert on this view, the XML is split into logically correct chunks so that the XML does not exceed the limits of the XML API. The data is then imported into one or more administrations.

It is therefore also possible to load multiple administrations at once with extra product information in a single import. However, the definitions of the free fields must then be identical in every Exact administration.

Compiling XML for Exact

The first step in importing values is compiling the XML according to the specifications. The query below first selects two administrations and then creates an in-memory table with two rows: one per Exact Online administration (“division”).

For each administration, a value is specified for the first extra field for every item. That extra field must be called “KOPIE OMSCHRIJVING”. The value will be aligned with the description of the product.

The listagg group function in the inner query ensures that the XML for each item is concatenated into one long piece of text, without any separator characters. The outer query takes all the data per administration and adds the mandatory XML header and footer. The xmlformat function first checks the XML for validity and then converts it into a more easily readable format with sufficient line breaks and indentation.

--
-- Select two Exact Online administrations.
--
use 2123456, 2344242

create or replace table xmlupload@inmemorystorage
as
select 'voorbeeld.xml' filename
,      'Items' topic
,      to_char(division) division
,       xmlformat
       ( '<?xml version="1.0" encoding="utf-8"?>'
       || '<eExact xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="eExact-XML.xsd">'
       || xmlcomment('For division ' || division)
       || '<Items>'
       || xml
       || '</Items>'
       || '</eExact>'
       )
       filecontents
from   ( select division
         ,      listagg
                ( '<Item ID="{' || itm.id || '}" code="' || itm.code || '" >'
                  || '<ItemExtraFields>'
                  || '<ExtraField1 description="KOPIE OMSCHRIJVING">'
                  || xmlelement('Description', itm.description)
                  || '</ExtraField1>'
                  || '</Item>'
                , ''
                )
                xml
         from   itemsbulk itm
         group
         by     division
)

Automatically Importing XML

The contents of the staging table can then be imported into Exact Online using the following SQL. This SQL splits the XML into chunks of 25,000 characters and loads them into Exact Online. This is remarkably fast; the XML API is still by far the fastest alternative for bulk loading and/or updating data in Exact Online: not just for items. For more background information on performance in Exact Online, see for example Does Invantive UniversalSQL match the speed of Infinite Probability Drive?.

--
-- Load into Exact Online. Errors are logged in UploadXMLTopicFragments.
--
insert into UploadXMLTopics
( topic
, payload
, division_code
, orig_system_reference
, fragment_payload_flag
, fragment_max_size_characters
, fail_on_error
)
select topic
,      filecontents
,      division
,      filename
--
-- Try to split into pieces of 25 KB at most.
--
,      true fragment_payload_flag
,      25000 fragment_max_size_characters 
,      true fail_on_error
from   xmlupload@inmemorystorage
order 
by     division
,      filename

--
-- Check everything was loaded correctly in Exact Online.
-- There may be NO row with success = false.
--
select *
from   UploadXMLTopicFragments

The insert on UploadXMLTopics always succeeds. Any errors can be checked immediately after loading in UploadXMLTopicFragments. This view also contains a column with only the rejected mutations. After correction, these can either be imported manually via “Import/Export” in the Exact Online web interface, or submitted again via UploadXMLTopics.