Bulk Download Documents from Exact Online Companies

[forums]

Summary

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