How to upload a document with attachments into Exact Online using REST API

Question

I want to upload files as attachment to a document in Exact Online.

The following SQL has been used to create the document:

insert into exactonlinerest..documents
( salesinvoicenumber
, type
, subject
, account
)
select 16700001
,      10
,      'subject 3'
,      id 
from   Accounts 
where  code like '%24274514'

This create a document related to Exact's sales invoice 16700001 and associates it with the account (customer) 24274514.

When I execute the following insert statement through the REST APIs of Exact Online:

insert into exactonlinerest..DocumentAttachments 
( attachment
, document
, filename
)
select attachment
,      'ea2d9221-31b0-4217-a82f-f29c5d517b41'
,      filename 
from   exactonlinerest..documentattachments
where  filename like 'Loonstrook%'

I get an error:

itgenoda001: Verplicht: Bijlage (https://start.exactonline.nl/api/v1/102673/documents/DocumentAttachments)

How do I upload these existing pay slips contained in Exact Online as attachments to a new document?

PS. the GUID ea2d9221... was found by querying the documents after inserting one.

Answer 1

The attachment column of the REST API table documentattachments is null always on retrieval.

You can use the pseudo-column attachmentfromurl as in:

insert into exactonlinerest..DocumentAttachments 
( attachment
, document
, filename
)
select attachmentfromurl
,      'ea2d9221-31b0-4217-a82f-f29c5d517b41'
,      filename 
from   exactonlinerest..documentattachments
where  filename like 'Loonstrook%'

The attachmentfromurl column can not be replaced by a http_get table function of Invantive SQL or a similar normal SQL function since the OAuth2 token is not automatically added to the HTTP request, since Exact uses an URL like:

https://start.exactonline.nl/docs/SysAttachment.aspx?ID=f28fd698-756f-4052-bea9-fc2130fe9ab1&Division=102673

when the document attachment is coming from Exact itself.

For external sources such as http://www.cnn.com this would work.

Result:

EDIT:

You can also upload files from the file system using a query such as:

insert into exactonlinerest..DocumentAttachments
( attachment
, document
, filename
)
select file_contents
,      dct.some_guid
,      file_path
from   ( select min(id) some_guid from exactonlinerest..documents ) dct
join   files('c:\windows\system32', '*.png', false)@os
join   read_file(file_path)@os
union all
select file_contents
,      dct.some_guid
,      file_path
from   ( select min(id) some_guid from exactonlinerest..documents ) dct
join   files('c:\windows\system32', '*.exe', false)@os
join   read_file(file_path)@os

Please note that there seems to be a 22 MB limit per file.