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?
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.