A new module in the Exact Online Reporting app “Get My Report” allows you to download many or all documents from one or many Exact Online companies in one go. This note describes how you can use Invantive SQL yourself to combine a lot of documents into one ZIP file as done by the new module.
Download all Exact Online Documents
Get My Report allows you to download documents using a new module:
Next you need to specify the currently sole parameter: download documents from a specific company or from all companies to which you have access:
Code of Implementation
There is an Invantive Data Access Point preset which executes when you click on the Download button. This preset has two Invantive SQL statements attached that you can also run from all Invantive SQL compatible products such as Invantive Data Hub, Invantive Control for Excel or Invantive Business for Outlook.
The first statement is a Procedural SQL statement which execute the ‘use’ statement to select one or all Exact Online companies:
-- -- Download documents from one or more companies. -- begin use select /*+ ods(false) */ spn.code from systempartitions@DataDictionary spn where spn.code = coalesce(:division_code, spn.code) ;
And then create an in-memory table with the selection of the documents to be exported. A join is made to the Exact Online table Transactions to find the entry number, journal code and description of the associated ledger entry.
The column file_name_path is composed of the division label, a category like ‘Sales invoices’ and then the document name, so the resulting zip file will look like:
For each and every Exact Online company there will be a separate folder, like here named ‘1 - Zorgdienst Hengelo & Almelo & Zwolle NV (102673)’.
Note that a zip file allows inclusion of the same file name twice which conveniently moves the consequences of using duplicate file names within Exact Online to the extraction moment. As an alternative you could use the document folders available in Accountancy Premium Edition (for some non-customer oriented reason this functionality is only available to the limited number of users of this module, leading to limited coverage by app developers) or the document ID as part of the file name path.
create or replace table documentstoexport@inmemorystorage as select dae.AttachmentFromUrl file_contents , dae.DivisionLabel || '/' || dct.categorydescription || '/' || dae.filename file_name_path , dct.subject , dct.typedescription , dae.DivisionLabel , dae.FileName , dct.accountcode , dct.accountname , dct.categorydescription , dct.ContactFullName , dct.DocumentDate , dct.projectcode , dct.projectdescription , dct.salesinvoicenumber , dct.salesordernumber , dct.shopordernumber , txn.journalcode , txn.entrynumber transactionentrynumber , txn.description transactiondescription , case when txn.entrynumber is not null then txn.journalcode || ' - ' || txn.entrynumber || ': ' || txn.description else null end transactionlabel , case when dct.projectcode is not null then dct.projectcode || ' - ' || dct.projectdescription else null end projectlabel , case when dct.accountcode is not null then dct.accountcode || ' - ' || dct.accountname else null end accountlabel from exactonlinerest..documentattachmentfiles@eol dae join exactonlinerest..documents@eol dct on dct.id = dae.document left outer join exactonlinerest..transactions@eol txn on txn.entryid = dct.financialtransactionentryid ; end;
A large number of columns is also selected, but neither exported nor filtered on.
To improve performance in scenarios where you only need documents of a specific type you could add a parameter on category. This filter will automatically be forwarded to Exact Online by Invantive SQL.
You can also add a filter on optional columns such as account code to filter on documents of a specific account. However, remember that account code is an optional column, so use a where-clause like:
and case when :filter_parameter is not null then accountcode = coalesce(:filter_parameter, accountcode) else true end
or even better use two variants to improve performance, since a complex case can not yet be rewritten into an OData filter by Invantive SQL.
In the next statement the zip file is created using a group function named ‘zip’. It is similar to well-known SQL group functions like ‘sum’ and ‘count’, but has two expressions instead of one as with ‘sum’ or ‘count’:
- The content of the zip file entry.
- The file name of the zip file entry.
The statement also generates a handy file name for the zip which includes some origin information besides the zip file itself:
select to_char(sysdate, 'YYYYMMDDHH24MISS') || '-documents-' || normalize(me.DataContainerID, 200, false) || '-' || normalize(me.DivisionCustomerName, 200, false) || '.zip' zip_file_name , zip.zip_file_contents from me@eol join ( select zip(v.file_contents, v.file_name_path) zip_file_contents from documentstoexport@inmemorystorage v ) zip
On products such as Invantive Query Tool you will need to use an Invantive Script statement to export the zip file(s) to the file system or use the context menu to export the file contents from the grid:
local export documents in zip_file_contents to "C:\temp\out" filename column zip_file_name