Query Exact Online REST interface using joins and SQL

Wiki (en)
,
1

Today, I’ve posted a happy message about being able to query Exact Online using multiple joins. It was amazing to find the same happiness in the replies from other people working with Exact Online.

As you might know, Exact Online offers no direct access using SQL to their database, just like many other cloud-based solutions. And similar to Facebook, LinkedIn and StackOverflow, Exact offers APIs using REST and XML.

However, I myself are hardly able to write any correct XSLT or REST statements in C# or VB, but SQL flows easy out of my keyboard and when the business requirements change, the SQL statement easily reflects that change.

The last year we’ve been working on enabling the use of SQL in combination with Exact Online (and other online platforms). Together with an experienced Exact Online consultant (Hans Jansen of QExpertise, thanks a lot!) I’ve used SQL to extract the relevant data set for an external party. In this post I want to share you this as a sample.

Use the following SQL in Invantive Control for Excel to retrieve a list of unshipped but paid sales order line items to instruct your warehouse:

select soe.ordernumber
,      soe.deliverydate
,      soe.linenumber
,      soe.itemcode
,      soe.itemdescription
,      soe.quantity
,      soe.unitdescription
,      act.city
from   salesorderlines soe
join   salesorders sor
on     sor.orderid = soe.orderid
join   SalesInvoiceLines sie
on     sie.salesorderline = soe.id
join   salesinvoices sic
on     sic.invoiceid = sie.invoiceid
join   items itm
on     itm.id = soe.item
join   receivableslist rce
on     rce.invoicenumber = sic.invoicenumber
join   accounts act
on     act.id = sor.deliverto
where  itm.ItemGroupCode = 'AssembledProductGroup'
and    rce.journalcode = '80'
and    soe.quantitydelivered = 0
and    soe.quantity =  soe.quantityinvoiced
2

Here is my modest contribution (teacher: Guido) to get nice view on cash position (and more if you look) of your eol divisions applying the left join subject.

The only big thing coders need to have in mind is that the left join is done on client side, not database side. Don’t left join million lines tables or you will get throttled by eol… you need to sub-filter your results in the joins with adequate where clause

USE division1@eol division2@eol etcc…
SELECT bke2.Division, bke2.DivisionCompanyName, bke2.JournalCode, bke2.JournalDescription, bke2.OpeningBalanceFC, bke2.ClosingBalanceFC,
bke2.FinancialYear, bke2.FinancialPeriod, bkemax.LastEntryDate AS LastMovDate,
SUM(CASE WHEN bkel2.AmountDC<0 THEN bkel2.AmountDC + (CASE WHEN bkel2.AmountVATFC IS NULL THEN 0 ELSE bkel2.AmountVATFC END) ELSE 0 END) AS Debit,
SUM(CASE WHEN bkel2.AmountDC>0 THEN bkel2.AmountDC + (CASE WHEN bkel2.AmountVATFC IS NULL THEN 0 ELSE bkel2.AmountVATFC END) ELSE 0 END) AS Credit
FROM BankEntries@eol bke2
LEFT JOIN
( SELECT bke.Division, MAX(bke.EntryNumber) AS EntryNumber, bke.JournalCode, MAX(bkel.Date) AS LastEntryDate
from bankEntries@eol bke
LEFT JOIN BankEntryLines@eol bkel ON bke.EntryID = bkel.EntryID
WHERE bke.created >= sysdateutc- 31
GROUP BY bke.Division, bke.JournalCode
) bkeMax
ON (bke2.Division = bkeMax.Division And bke2.EntryNumber = bkeMax.EntryNumber)
LEFT JOIN BankEntryLines@eol bkel2 ON bke2.EntryID = bkel2.EntryID
WHERE bke2.created >= sysdateutc- 31
GROUP BY bke2.Division, bke2.DivisionCompanyName, bke2.JournalCode, bke2.JournalDescription, bke2.OpeningBalanceFC, bke2.ClosingBalanceFC,
bke2.FinancialYear, bke2.FinancialPeriod, bkemax.LastEntryDate

1 like