Inventory Report

[billkater]

I am wanting to print a inventory report. What is the field name in the database that holds that information? I can find the rest of the information on the products under the Products field.

[Jamie]

Hi,

The stock is tracked in the REL_Products__Warehouses table. That table relates a product to a given warehouse, and stores the stock count for that item for each particular warehouse it is in. If you are not using the warehouse feature, then the stock information is still stored there, but the warehouse defaults to record # 1, which is for the address location specified in store settings.

If you run this MySQL query, it will return the total stock for each product, along with the total sales $ and total quantity sold:

SELECT
p.record_number
,p.Name
,IF(SUM(oi.Quantity),SUM(oi.Quantity),0) AS Total_Sold
,IF(SUM(oi.Item_Subtotal),SUM(oi.Item_Subtotal),0) AS Total_Sales
,IF(SUM(pw.Stock),SUM(pw.Stock),0) AS Total_Stock
FROM
Products p
LEFT JOIN
Ordered_Items oi
ON
p.record_number = oi.Item_rn
LEFT JOIN
REL_Products__Warehouses pw
ON
p.record_number = pw.Table_1
GROUP BY p.record_number
ORDER BY p.Name ASC

[billkater]

By using the query you gave i can modify it to include other attributes such as below:

SELECT p.Product_code, p.weight, p.Name,
IF (
SUM( oi.Quantity ) , SUM( oi.Quantity ) , 0
) AS Total_Sold,
IF (
SUM( oi.Item_Subtotal ) , SUM( oi.Item_Subtotal ) , 0
) AS Total_Sales,
IF (
SUM( pw.Stock ) , SUM( pw.Stock ) , 0
) AS Total_Stock
FROM Products p
LEFT JOIN Ordered_Items oi ON p.record_number = oi.Item_rn
LEFT JOIN REL_Products__Warehouses pw ON p.record_number = pw.Table_1
GROUP BY p.record_number
ORDER BY `p`.`Product_code` ASC LIMIT 0 , 30

This would output the Item number,. weight, Name, Total sold, Dollars sold, inventory total.

Thanks for the quick reply.

Could a report option be added to the next release to do the same?

Bill

[Jamie]

Hi Bill,

The next version will not include an export feature, but the views to manage your products will include this info, and be much more manageable. The query I posted here is the default query that is run when you click "Catalog > Products > Manage" in the next release. I removed some of the product fields for the post, but the total sales, stock, and qty sold are in there by default.

Thanks,
Jamie

[firejdl]

is it possible to insert that SQL code somewhere and get this same sort of report on the Catalog > Products > Manage page now? it is vital to our client.