Error itgensql005 when fetching serial number from Exact Online GoodsDeliveryLines for upload to Freshdesk ticket

Question

I want to exchange information between ExactOnline and Freshdesk based on deliveries (Exact Online Accounts -> Freshdesk Contacts, Exact Online deliveries -> Freshdesk tickets).

The serial number of delivered goods is not available in either the ExactOnlineREST..GoodsDeliveryLines table nor in ExactOnlineXML..DeliveryLines.

The following query lists all columns that are also documented on Exact Online REST API GoodsDeliveryLines:

select * from goodsdeliverylines

All other fields of the documentation on REST APIs are included in GoodsDeliveryLines, solely serial numbers and batch numbers not.

I've tried - as on ExactOnlineXML tables where there column only come into existence when actually specified - to use:

select stockserialnumbers from goodsdeliverylines

This raises however an error:

itgensql005: Unknown identifier 'stockserialnumbers'.

How can I retrieve the serial numbers?

Answer 1

StockSerialNumbers is an array, on the Exact Online documentation it says:

Collection of batch numbers

so far every delivery lines, there can be 0, 1 or more serial numbers included.

These serial numbers were not available till some time ago; please make sure you upgrade to at least build 16282 of the Exact Online SQL provider. It should work then using a query on a separate table:

select ssrdivision
,      ssritemcode
,      ssrserialnumber
from   GoodsDeliveryLineSerialNumbers  

Output:

ssrdivision | ssritemcode | ssrserialnumber
----------- | ----------- | ---------------
868,035     | OUT30074    | 132
868,035     | OUT30074    | 456

Use of serial numbers may require more modules from the respective supplier Exact Online like "Trade", but when you can see them in the web user interface, then you have them already. If you get an HTTP 401 unauthorized, you don't have the module for serial numbers.

Answer 2

Since stockserialnumbers is actually a list and not a single field, you have to query it using the entity GoodsDeliveryLineSerialNumbers, which you can find in the latest release.

select * from GoodsDeliveryLineSerialNumbers

If you execute the above query, you will get the fields for GoodsDeliveryLine and those of the underlying serial numbers. The latter fields are prefixed with Ssr to disambiguate both entities. This means you don't need an additional join on GoodsDeliveryLine, which may benefit performance.