inventory on product listing

[firejdl]

Is there any EASY way to add a "Number in Stock" column to the Catalog > Products > Manage page? This is something that our vital to our client. We spent a few hours working on it yesterday, and it seriously gave me nightmares. after wading through files like get_records.func.php, get_rel_value.func.php, and admin_show_records.func.php, i finally gave up. we told the client that it's not possible at the moment, but she's very insistent on it.

any ideas?

other than that, the product works great! the client has been using it for almost two years now with no problems. we just upgraded her to 1.5.3 and she loves all the new features. thanks for all your hard work!

[pbxcom]

I was going to reply that I have been unable to find this but, I took one more look as I was replying to your post and guess what.

Go to the "Table_Definitions" table in the database edit the "Products" "Table_Name" (record # 9). You will see in that record "Table_Field" and "Record_Field" and you have probally guessed the rest. "Table_Field" is what is normally displayed or what is displayed when you click "Hide Extra Fields" and "Record_Field" is what is displayed when you click "Show All Fields".

I have been looking for this for over a month.

[firejdl]

that doesn't really help us much. we knew about those fields already, but we don't know how to link the Warehouse REL table to the Products table for the display.

by the way, we're using v1.5.3. what you mentioned would probably work in the version we had before, though.

[Jamie]

There is no easy way to get that information in the Products table view in 1.5.3. The info is in that view by default in v2.0.0, but that won't help you until it is available.

If you just need to see the information, you could paste a custom query into the DB Query tool:

SELECT
p.record_number
,p.Name
,p.Thumbnail_Image
,p.Base_Price
,SUM(oi.Quantity) AS Total_Sold
,SUM(oi.Item_Subtotal) AS Total_Sales
,p.Image
,p.Product_Code
,p.Date_Added_to_Cart
,SUM(pw.Stock) 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.Products_rn
GROUP BY
p.record_number
ORDER BY
p.Name ASC


If you need it sorted by stock, just change "ORDER BY p.Name ASC" to "ORDER BY Total_Stock DESC".

Thanks,
Jamie

[firejdl]

The problem with that is the length of time it takes to complete the query. our client has well over 2000 products in her Products table!! we've had no other speed issues, but for this query it takes 84.1012 seconds to complete! that is completely unacceptable for retrieving data to display on a PHP page, as you can imagine. for one it overruns the time limit imposed by PHP [I know I can up the time limit, but...], and for two, our client would not want to wait that long just to see how many of each item is in the inventory, as you can imagine.

The SQL that more fits our client's needs is as follows:

SELECT
p.Product_code,
p.weight,
p.Name,
p.Date_Added_to_Cart,
pw.Stock AS Total_Stock,
c.Name AS catname,
p.Base_Price
FROM
Products AS p
LEFT JOIN
REL_Products__Warehouses
AS
pw
ON
p.record_number = pw.Table_1
LEFT JOIN
REL_Products__Categories
AS
rpc
ON
p.record_number = rpc.Table_1
LEFT JOIN
Categories
AS
c
ON
rpc.Table_2 = c.record_number
ORDER BY
p.Date_Added_to_Cart DESC
LIMIT
0 , 20

this actually takes even longer [by 3 seconds], even though we are limiting the number of rows to retrieve!

[jackkyll]

I think the question being asked is the same one that I was going to ask. I REALLY really need to see how much stock I have in the items list. Going catagory by catagory on the website is time comsuming.

Jamie said "The info is in that view by default in v2.0.0, but that won't help you until it is available."... is this a type O since we are on 2.1.something or are you saying for that person to get to 2.0.0?

I know I used to have this feature but lost it in the upgrade when the warehouse came in and I really need it. I don't know anything about messing with the database at the back end so I don't feel comfortable doing that.

I don't care how much I sold or the amount of money I brought in for that product (I do that in Quickbooks), but I do need to make sure my inventory is accurate, which it's not right now.

Thanks!

Melissa

[Jamie]

You can view the number of units in stock for any product in the default view for the Products table. In the control panel, click "Products > Manage". Then view the "Total Stock" column.

[jackkyll]

I just had a huge DUH.... so sorry. I didn't scroll over far enough. I thought I saw it before, but forgot I guess.

Thanks Jamie!

Melissa