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