Linnworks integrates with Sage via Tradebox. This integration is based on the order information exported from Linnworks and imported into Tradebox. The export file from Linnworks can be generated manually or exported on a regular basis, using a schedule.

Please note, this integration requires Custom Reporting functionality. If your account does not have access to Custom Reporting, please reach out to our sales team.


To generate the file manually:


Go to Linnworks > Dashboards > Query Data > select query type as "Tradebox Export" and select the start and end date.


You can then click Download (CSV file) button to export the data.

For more detailed information about manual Tradebox Export please refer to this documentation article.



Automatically scheduled exports:


To create a scheduled export for Tradebox, you need to use a custom script in the export data routine. 


1. go to Linnworks > Settings > Export Data > Add Scheduled Export > Export type: Custom

2. paste the script (below) there 

3. set up the export file path 

4. select the columns you wish to export 

5. set up a schedule based on which the export will be running


For more information on this, please refer to the documentation on Export Data and Schedules.



Query:


SELECT CONVERT(VARCHAR(10), o.dReceievedDate, 103) AS 'Order Date', 
    o.nOrderId AS 'Order ID', oi.ItemNumber AS 'Item ID', 
    'SKU' = CASE 
        WHEN pis.pkStockItemID IS NOT NULL THEN pis.ItemNumber 
        WHEN si.pkStockItemID IS NOT NULL THEN si.ItemNumber
        WHEN oi.ChannelSKU IS NOT NULL THEN oi.ChannelSKU 
        ELSE 'SKU Not Supplied' 
        END,
    'Product Name' = CASE 
        WHEN pis.pkStockItemID IS NOT NULL THEN pis.ItemTitle 
        WHEN si.pkStockItemID IS NOT NULL THEN si.ItemTitle 
        WHEN sis.cItemName IS NOT NULL THEN sis.cItemName 
        ELSE 'Product Name Not Supplied' 
        END, 
    'Currency' = CASE o.cCurrency 
        WHEN '' THEN 'GBP' 
        WHEN '$' THEN 'USD' 
        WHEN '' THEN 'EUR' 
        WHEN '' THEN 'GBP' 
        ELSE o.cCurrency 
        END, 
CASE WHEN oi.LineDiscount =0 THEN (oi.CostIncTax/ oi.nQty) ELSE CAST( (oi.CostIncTax +(oi.CostIncTax * oi.LineDiscount  / (100 -oi.LineDiscount ))) / oi.nQty AS DECIMAL(18, 2)) END AS  'Product Price', 
    oi.nQty AS 'Quantity', 
    o.fPostageCost AS 'Carriage Amount', 
CASE WHEN oi.LineDiscount =0 THEN 0 ELSE CAST(oi.CostIncTax * oi.LineDiscount  / (100 -oi.LineDiscount ) / oi.nQty AS DECIMAL(18, 2)) END AS 'Discount', 
    ISNULL(b.AccountName, '') AS 'Payment Method',
    ps.PostalServiceName AS 'Shipping Method', 
    'Billing Name' = REPLACE(ISNULL(oia.BillingName,o.cFullName),'"',''), 
    'Billing Company Name' = REPLACE(ISNULL(oia.BillingCompany,o.Company),'"',''), 
    'Billing Address 1' = REPLACE(ISNULL(oia.BillingAddress1, o.Address1),'"',''), 
    'Billing Address 2' = REPLACE(ISNULL(oia.BillingAddress2, o.Address2),'"',''), 
    'Billing Address 3' = REPLACE(ISNULL(oia.BillingTown, o.Town),'"',''), 
    'Billing Address 4' = REPLACE(ISNULL(oia.BillingRegion, o.Region),'"',''), 
    'Billing Address 5' = REPLACE(ISNULL(oia.BillingPostcode, o.cPostCode),'"',''), 
    'Billing Country' = ISNULL(oia.BillingCountryName, lc.cCountry), 
    'Billing Country Code' = CASE lc.cCountryCode 
        WHEN 'UNK' THEN 'GB' 
        ELSE ISNULL(billcountry.cCountryCode,lc.cCountryCode) 
        END, o.cEmailAddress AS 'Customer Email', 
    o.BuyerPhoneNumber AS 'Customer Telephone', 
    o.cFullName AS 'Shipping Name',
    REPLACE(o.Company,'"','') AS 'Shipping Company Name', 
    REPLACE(o.Address1,'"','') AS 'Shipping Address 1', 
    REPLACE(o.Address2,'"','') AS 'Shipping Address 2',
    REPLACE(o.Town,'"','') AS 'Shipping Address 3', 
    REPLACE(o.Region,'"','') AS 'Shipping Address 4', 
    REPLACE(o.cPostCode,'"','') AS 'Shipping Address 5', 
    lc.cCountry AS 'Shipping Country', 
    lc.cCountryCode AS 'Shipping Country Code', 
    'Order Status' = CASE o.nStatus
        WHEN '1' THEN 'Complete' 
        END, 
    'Marketplace' = CASE o.SubSource
        WHEN '' THEN o.Source
        ELSE o.Source + ' ' + o.SubSource
        END
FROM
    [Order] o 
    INNER JOIN OrderItem oi WITH(NOLOCK) ON oi.fkOrderID = o.pkOrderID 
    INNER JOIN StockItems sis WITH(NOLOCK) ON sis.pkStockID = oi.fkStockID 
    LEFT OUTER JOIN StockItem si WITH(NOLOCK) ON sis.fkStockControlStockItemId = si.pkStockItemID 
    LEFT OUTER JOIN StockItem pis WITH(NOLOCK) ON pis.pkStockItemID = oi.fkStockItemId_processed 
    INNER JOIN PostalServices ps WITH(NOLOCK) ON o.fkPostalServiceId = ps.pkPostalServiceId 
    INNER JOIN ListCountries lc WITH(NOLOCK) ON o.fkCountryId = lc.pkCountryId 
    LEFT OUTER JOIN Order_AdditionalInfo oia WITH(NOLOCK) ON oia.fkOrderId = o.pkOrderID 
    LEFT OUTER JOIN Accounting_Bank b WITH(NOLOCK) ON b.pkBankId = o.fkBankId 
    LEFT OUTER JOIN 
        (SELECT 
            cCountry,cCountryCode=MAX(cCountryCode) 
        FROM ListCountries 
        WHERE bLogicalDelete=0 
        GROUP BY cCountry) AS billcountry ON billcountry.cCountry = oia.BillingCountryName 
WHERE 
    (oi.fkCompositeParentRowId = '00000000-0000-0000-0000-000000000000' OR oi.fkCompositeParentRowId IS NULL) 
    AND o.nStatus = 1 
    AND (o.dprocessedon BETWEEN DATEADD(D,-1,GetDATE()) AND GETDATE())
    AND o.dProcessedOn IS NOT NULL 
    AND o.bProcessed = 1 
ORDER BY 
    o.nOrderId
SQL