Query Data report - Sku, Supplier, Item Title, Available and Min Quantity

I would like to pull a report with SKU, Item Title, Supplier and Available and Minimum quantity level. 


Can anyone reccomend a query data that would achieve this? Or alternatively an example of a script which would do this.


Thanks

Hello Mike,



 Thank you for your question. There are few sample queries available that you can use to tailor new queries to use with the Query Data tool. However, there isn't much information on how to get supplier data. There are two databases that we use for supplier information. The first one is "ItemSupplier" which links supplier ID with the items (columns fkStockItemID to link with the item and fkSupplierId to link with the supplier data). The second database is "Supplier" where "pkSupplierID" can be used to link with fkSupplierID and item and "SupplierName" can be used to pull the supplier name.


 There's documentation on this here:

http://www.linnworks.com/support/dashboards-sales-reporting/query-data/custom-script

http://www.linnworks.com/support/dashboards-sales-reporting/query-data/custom-script/further-sample-queries


 I took the first sample from the first link and edited it a bit so that it would give you the results that you are after for:

SELECT

si.ItemNumber, si.ItemTitle, sLoc.Location, sl.MinimumLevel, sl.Quantity, 'Available' = (sl.Quantity-sl.InOrderBook), sl.InOrderBook, su.SupplierName

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

LEFT OUTER JOIN ItemSupplier pp on si.pkStockItemId = pp.fkStockItemId

LEFT OUTER JOIN Supplier su on pp.fkSupplierId = su.pkSupplierID

WHERE

si.bLogicalDelete = 0

ORDER BY

si.ItemNumber, sLoc.Location


 It will display the location of the items as well since the item minimum level and available quantities depend on that.

Hope this helps.



With Best Regards,

Rimo


Linnworks Technical Support

Thanks Rimo, 


Just what i was needed. 


Kind regards

Hi Mike,

i want to pull report of  "All open orders" it should include "supplier name" and their "Locations" ! this will be really helpfull me to contact supplier to for ordered product directly.


Can you let me know script for this please?

Regards
Sajid

 

Hi Rimo,


We have a custom query we use to pull a report that you helped us with previously.  


SELECT

si.ItemNumber, si.ItemTitle, sLoc.Location, sl.MinimumLevel, sl.Quantity, 'Available' = (sl.Quantity-sl.InOrderBook), sl.InOrderBook, su.SupplierName

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

LEFT OUTER JOIN ItemSupplier pp on si.pkStockItemId = pp.fkStockItemId

LEFT OUTER JOIN Supplier su on pp.fkSupplierId = su.pkSupplierID

WHERE

si.bLogicalDelete = 0

ORDER BY

si.ItemNumber, sLoc.Location


This has been fine for us but now we have added a location and we need to tweak this so that it pulls the combined stock (from all locations) with the default / combined min quantity.


Can you advise on a tweak for the current script we have please so that i can see combined minimum and combined stock from all locations. 


Kind regards

Hello,



 Unfortunately, I'm unable to give you to script that would do that. The first one that I wrote was given as an example on how to combine different scripts using the data available on our documentation page.


 Please refer do these documentation articles if you need the script to be edited:

http://www.linnworks.com/support/dashboards-sales-reporting/query-data/custom-script

http://www.linnworks.com/support/dashboards-sales-reporting/query-data/custom-script/further-sample-queries


 Or contact our scripting team who will be able to write the custom script for you:

http://www.linnworks.com/support/scripting-terms-and-conditions



With Best Regards,

Rimo

Linnworks Technical Support

Morning Rimo, 


I understand. Can you advise me on one thing then? 


When I am editing the script how would I combine look for a specific thing? So for example. 


I want to find just 1 supplier. How would I use the script to search for just 1 supplier using the custom query? 


If you could let me know that i would appreciate it. 

Hello,



 You would be able to link [ItemSupplier] SQL table based on the pkStockItemid and then JOIN the [Supplier] SQL table with the [ItemSupplier] on pkSupplierID and then get the SupplierName from there. Using 'WHERE' you can then filter out the results to only show one supplier.


Using SQL WHERE: https://www.w3schools.com/sql/sql_where.asp

Using SQL JOIN: https://www.w3schools.com/sql/sql_join.asp


 Hope this helps.



With Best Regards,

Rimo


Linnworks Technical Support


Login to post a comment