Invoice attachment URL data modelling Exact Online

I’m trying to get the attachment URL of an outstanding invoice (ReceivablesList) but I can’t seem to figure out how to set up the datamodel for this particular case.

The same has to be done for the PayablesList.

Is there a similar case in the past? Or some guidance in the right direction?

What are you trying to achieve? I seem unable to derive that from the question.

Use case: Aginglist in Power BI. A matrix detailing all outstanding invoices in agebuckets (AR and AP on seperate pages) the user can drill down to invoice detail and click on the URL which directs them to the EOL attachment.

In that case it is recommended to extract the outstanding invoices using APOutstandingItems and AROutstandingItems.

The best way to find the documents (URLs using an Exact Online-secured connection) is to query DocumentsIncremental. In SQL-statement:

select Division
,      Id
,      Type
,      DocumentDate
,      FinancialTransactionEntryID
,      SalesInvoiceNumber
from   DocumentsIncremental
where  Type in (10 /* Sales */, 20 /* Purchase */)

Please note that Exact Online can register multiple files per document. The best way to find the document attachments (URLs using an Exact Online-secured connection) is to query DocumentAttachmentsIncremental. In SQL-statement:

select Division
,      Document
,      FileName
,      URL
from   DocumentAttachmentsIncremental

The column Document relates back to Id of DocumentsIncremental.

In case there is a need to include the actual BLOB in the report, the table DocumentAttachmentFiles can be used. Note that this table can be become huge.

For TransactionLines

For a relationship with transaction lines, you can use something like:

join   DocumentAttachmentsIncremental dat
on     dat.Division = tle.Division
and    dat.document = tle.Document
and    dat.FileName like '%.pdf'

This question was automatically closed after at least 2 weeks of inactivity after a possible solution was provided. The last answer given has been marked as a solution.

Please ask a new question via a separate topic if the problem occurs again. Please include a link to this topic in the new question by pasting its URL into the text.