I have an SQL query which exports the ExternalReference field fro Linnworks, here's the command I use in an SQL script (run from Query Data)...

ExternalReference AS 'ExtRef',

...however I've noticed that in the past few days, this ExternalReference data is missing from LW orders (i.e. ordrs that Linnworks has retrieved from Amazon)...however, fortunately it seems that for Amazon the missing data can also be gleaned from ChannelReference (or Reference) i.e. the same data that I need that's missing, therefore how can I export the ChannelReference (or Reference) from LW.

neither of these commands work in my SQL script...

ChannelReference AS 'ExtRef',

Reference AS 'ExtRef',

Many thanks!

Ok, I sussed it (by using template designer I was able to glean the varable I needed), this gets me going...

'ExtRef' = CASE
               WHEN o.source = 'Amazon FBA' THEN ReferenceNum
               WHEN o.source = 'Amazon' THEN ReferenceNum
               else ExternalReference
               END,

 

Hello Rob,

This is Ethan from HyperCommerce. You could actually use the Secondary Reference.

Actually, If you need the Sales Record Number from EBAY instead which most sellers do, try this instead:

'Channel Reference Id' = CASE
               WHEN o.source LIKE 'AMAZON%' THEN ReferenceNum
               WHEN o.source = 'EBAY' THEN SecondaryReferenceNum
               else ExternalReference
               END

 

If you need custom solutions such as Advanced SQL Queries, Custom Reports, API Solutions or Integrations, please feel free to reach me at ethan@hypercommerce.co.uk