Custom Script

With the Linnworks Custom Script option, bespoke SQL queries can be written for the precise requirements of a particular business' processes and conventions. This SQL can then be executed in the Custom Script section. To run a custom report or other piece of SQL, the text of the file should be copied and pasted into the Custom Script window. There is an option to save custom script for further use.
NB! The Custom Script option is available for Subscription plans Advanced and up. Please see our Pricing page for details.
custom script1

General

The Custom Script report is a powerful method of giving direct read access to your database. In order to use this function, you must be familiar with both the Linnworks database structure, see here, and creating SQL queries. Please note that you will only be able to use SELECT statements with Query Data Custom Scripts.

Linnworks can offer bespoke report creation as a chargeable service. For more information, terms and conditions, please see click here.

Limits to maintain server and database integrity: there is a 100mb limit implemented for the amount of data that is retrieved via a custom script. Any data in excess of this will be truncated and a warning will be displayed. 

Usage Guide

  1. Go to Dashboards > Query Data.
  2. Select Custom Script from the drop-down menu.
  3. Click button +New.
  4. Add script name and description.
  5. Paste your script in Configuration.
  6. Add parameters (if required).
  7. Enable paging (if required).
  8. Save the custom script and click Generate Report or Download (CSV File).

Once the query is saved, you can edit it or delete by clicking the respective button.

Parameters

To make your custom script more specific you can add various parameters, for example:

customquery1

SELECT
o.nOrderId as OrderID,
o.Source,
o.SubSource
FROM [order] o
WHERE ( o.[Source] = @Source or @Source = 'All') and  o.dReceievedDate between @StartDate and @EndDate

@Source @StartDate and @EndDate are parametres and you would need to specify additional details for them. These details should be added for each parameter that you are going to use in your custom script: 

  • Query Parameter Name: same name as in the script but without @.
  • Display Name: how you would like it to be displayed in the Query Data screen.
  • Display Type:
    • Select
    • Boolean
    • Date 
    • String
  • Default Value: optional, if added it can be edited in the Query Data screen
  • Sort Order: parameter order number in the Query Data screen
  • Database Type:
    • VARCHAR
    • NVARCHAR
    • FLOAT
    • INT
    • UNIQUEIDENTIFIER

parameters3

Select Parameter query for Source in Dropdown Query
 SELECT 'All' as [Value], 'All' as [Text]
UNION ALL
SELECT DISTINCT
    Source as [Value],
    Source as [Text]
FROM [System_Channel_Setting] 

Once parameters are added you can save the script and use it to generate the report and download a CSV file. Below you can see the result of the parameters added in the example script:

Paging Enabled

If you expect custom query result to have many rows it is strongly recommended to use paging, otherwise, it can slow down the system performance and as a result, the query will not be executed.

Example of the paged script:

SELECT
o.nOrderId as OrderID,
o.Source,
o.SubSource,
TotalRows = COUNT_BIG(*) OVER()
FROM [order] o
WHERE ( o.[Source] = @Source or @Source = 'All') and o.dReceievedDate between
@StartDate and @EndDate
ORDER BY  o.nOrderId
OFFSET @EntriesPerPage * (@PageNumber - 1) ROWS
FETCH NEXT @EntriesPerPage ROWS ONLY;

parameters6

Please Note! SQL query for Select parameter type must contain 2 columns: Text and Value.

SELECT pkStockLocationId as [Value], 
Location as [Text] 
FROM [StockLocation]
WHERE bLogicalDelete = 0

 Sample Queries

Below are some more common examples of custom queries that may be useful. Further examples can be found here.

Inventory

The script below shows the stock level in all Locations.

SELECT
si.ItemNumber, si.ItemTitle, sLoc.Location, sl.MinimumLevel, sl.InOrderBook, sl.OnOrder, il.BinRackNumber
FROM
StockItem si
LEFT OUTER JOIN StockLevel sl on si.pkStockItemId = sl.fkStockItemId
LEFT OUTER JOIN StockLocation sLoc on sl.fkStockLocationid = sLoc.pkStockLocationId
LEFT OUTER JOIN ItemLocation il on il.fkStockItemId = si.pkStockItemID AND il.fkLocationId = sLoc.pkStockLocationId
WHERE
si.bLogicalDelete = 0
ORDER BY
si.ItemNumber, sLoc.Location

Orders

Below are the commonly requested relationships for Linnworks orders

  • Retrieve inventory items from Open Orders (current Open Orders received today)
SELECT
'Channel Order ID' = o.ReferenceNum,
'Channel Order Date' = o.dReceievedDate,
'Channel' = o.Source + '_' + o.SubSource,
'Channel SKU' = oi.ChannelSKU,
'Qty ordered' = oi.nqty,
'LW Order ID' = o.norderID,
'LW SKU' = si.ItemNumber,
'Lw Title' = si.ItemTitle
FROM
[Open_Order] o
INNER JOIN
[Open_OrderItem] oi
on o.pkOrderID = oi.fkOrderID
LEFT OUTER JOIN
[StockItem] si
on si.pkstockItemId = fkStockItemID
WHERE
Convert (DATETIME, FLOOR(CONVERT(FLOAT, o.dReceievedDate))) = CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GetDate())))
ORDER BY
o.dReceievedDate ASC,
o.norderID ASC
  • Retrieve inventory items from Processed Orders (orders processed today)
SELECT
'Channel Order ID' = o.ReferenceNum,
'Channel' = o.Source + '_' + o.SubSource,
'Channel SKU' = oi.ChannelSKU,
'Qty ordered' = oi.nqty,
'LW Order ID' = o.norderID,
'LW SKU' = si.ItemNumber,
'Lw Title' = si.ItemTitle,
'Lw Processed Date' = o.dReceievedDate
FROM
[Order] o
INNER JOIN OrderItem oi on o.pkOrderID = oi.fkOrderID
LEFT OUTER JOIN [StockItem] si on si.pkstockItemId = oi.fkStockItemID_processed
WHERE
Convert (DATETIME, FLOOR(CONVERT(FLOAT, o.dProcessedOn))) = CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE())))
ORDER BY
o.dProcessedOn ASC, o.norderID ASC

Further Reading