Exact Online Bulk endpoints (server-side filtering)

Ideas (en)
, , ,
1

I make a suggestion, when using the Bulk endpoints of Exact Online.

The Bulk Exact Online API endpoints do not support all filtering features available in the equivalent not bulked endpoint. That means that an Invantive client clause WHERE to a bulk endpoint may do filtering

  • API side if the filtering is available
  • or on the client (Invantive) side if not available in the API

Example:
BulkTransactionLines enpoint :

Only the following filters are supported for this endpoint:
Account, CostUnit, CostCenter, Date, Division, Document, EntryID, EntryNumber, FinancialPeriod, FinancialYear, GLAccount, ID, InvoiceNumber, JournalCode, Modified, OrderNumber, Project, Status, TrackingNumber, Type, VATType, VATCode, YourRef

whereas on the Transactionlines enpoint support filtering on any fields.

Could that be a good idea that in Query Tool, Invantive could issue a warning before or after executing the statement to remind that the WHERE clause is using filters not supported in the endpoint?

See if it is wise in other command lines Invantive products.

2

Thanks for the suggestion to display a warning.

You are correct; Invantive SQL maintain in metadata a list of columns for which so-called “server-side filtering” can be applied. See for instance Limit Data load from Simplicate on Invantive Cloud to Azure Data Factory - 6 van forums.

Server-side filtering has a number of limitations.

From a computational theory point of view first of all, it is so-called a Turing NP-complete problem. In short: it can be very hard and computationally expensive to extract a relevant server-side filter from an Invantive SQL statement.

Good news however, is that Invantive SQL has no ambition on doing a perfect job in all circumstances: server-side filtering is an optimization technique and we feel already happy when it considerably improves performance in most common scenarios.

At this moment, the SQL engine internally checks the metadata whether a simple filter such “COLUMN = VALUE” can be forwarded to the (API) server behind the Invantive SQL driver. This information is also visible in the view SystemTableColumns@DataDictionary.

To be completed.