How do I download only my purchase invoice documents from Exact Online with Invantive Query Tool?

Question

To comply to regulations, I'm trying to download the purchase invoice documents (as PDF files) from some of my divisions to save them on-disk for archiving purposes.

I use Invantive Query Tool to do this. I like to know which table to use and how to export these attachments only regarding purchase invoice documents.

Answer 1

You can indeed do this by using the export options in Invantive Query Tool or Invantive Data Hub.

What you need is a query that hooks up the document information of type 20 (purchase invoices) with the actual attachment files. You can find a list of types and their description in the DocumentTypes view. You can find the document attachment files in the DocumentAttachmentFiles table.

When you have retrieved that, you can export the documents from that query to disk using a local export documents statement.

The full query is here:

use 123456

select /*+ join_set(dae, document, 10000) */ attachmentfromurl
,      dct.division || '/' || dae.id || '-' || filename 
       filepath
from   exactonlinerest..documents dct
join   DocumentAttachmentFiles dae
on     dae.division = dct.division
and    dae.document = dct.id
where  dct.Type = 20
order
by     dct.division
,      dae.id

local export documents in attachmentfromurl to "c:\temp\docs" filename column Filepath

Make sure to set the ID of the division right in the use statement (this is the technical ID, not the 'division number', which can contain duplicates). You can find that in the top menu bar under Partitions. Or simply use use all to get the documents from all divisions (this might take a while).

Also set the file path right where it says c:\temp\docs now. Then hit F5 in the Query Tool to execute, or run the script from Data Hub.