Zen-cart as an inventory management system?

**wolfderby** · 5 Jun 2021, 09:47 PM

So I need to get to 1.5.7, currently running a small biz on 1.5.6... but anyway... I've been using zen-cart as an inventory management system, by doing this w/ the product models...

PRODUCT1-001 < first time I ordered "product1"
PRODUCT1-002 < second time I ordered "product1"

then I have a custom "invoice field" w/ poor data atomization (just didn't know better at the time of creating the field) where I do...

060121VendorName002-003 < date of invoice (June 1st 2021), Vendor Name, 002 = 2nd time ordering from that vendor, 003 = 3rd item on the invoice

...then I have the product cost add-on (https://picaflor-azul.com/profit-mar...en-cart-plugin)

ANYWAY, my question is... "what's the proper relational database sort of way to manage re-purchases?"

Should I make a table that's `PRODUCTS_VARIATIONS` ... and have a foreign key that links the invoice and cost data?

I currently have a products table that simply gets bigger with each order, would it be significantly worth it to try to re-configure w/ this relational "variations" table?

I like the idea of building up one listing for "PRODUCT1" and then re-using it, but it seems like it might be an enormous undertaking. Like say one customer's order has 2 qty of PRODUCT1 and 1 comes from invoice1 and 1 comes from invoice2...

Anyway any thoughts... suggested googling, etc would be appreciated.

I had also messed w/ upgrading a quickbooks desktop sync add-on a while back by just googling error codes before I understood what objects were as a data-type... this I mention because I could possibly try to use that to manage the inventory.

**Philibel** · 5 Jun 2021, 10:47 PM

Just a few remarks.

Aren't you making it to difficult for yourself with giving every item an other product id for every incoming invoice? If the price changes this effect the value of goods in stock anyhow. If your purchase price changes significantly you'll have to adjust your sales price even if this means a drop in margin. Competitors with less stock will sell cheaper and leave you with disgruntled customers.

**swguy** · 5 Jun 2021, 11:01 PM

If you need inventory management, you should look at inventory management systems like Webgility, Quickbooks, etc.
Zen Cart is not an accounting system.

**carlwhat** · 5 Jun 2021, 11:02 PM

you really need a PO type of system. which would involve a number of tables such as:

Click image for larger version.

Name: po_tables.png
Views: 73
Size: 13.3 KB
ID: 19603

an example of the po_details table:

Click image for larger version.

Name: po_tables_2.png
Views: 81
Size: 76.3 KB
ID: 19604

and then you would need some code to input this table. and it all should tie to your products table.

i have done this before for clients, but it is not packaged up as a plug-in.

best.

**wolfderby** · 20 Jun 2021, 03:47 PM

Originally Posted by Philibel

Just a few remarks.

Aren't you making it to difficult for yourself with giving every item an other product id for every incoming invoice? If the price changes this effect the value of goods in stock anyhow. If your purchase price changes significantly you'll have to adjust your sales price even if this means a drop in margin. Competitors with less stock will sell cheaper and leave you with disgruntled customers.

I haven't figured out a simpler way yet... Typically I just let the lower priced items sell first, then know that... yeah there was a cost increase on it's replacement so the customer will have to pay more.

Our customers are pretty happy w/ our pricing.

**wolfderby** · 20 Jun 2021, 03:50 PM

Originally Posted by swguy

If you need inventory management, you should look at inventory management systems like Webgility, Quickbooks, etc.
Zen Cart is not an accounting system.

My main issue w/ ever figuring this sort of approach out, was that my industry only has barcodes on about 1/2 the product, and I never got to the point where I put my own barcodes on when receiving.

Something I'll have to give some thought to though. I have a script that generates this PRODUCTMODEL-001 incrementing for me.

**wolfderby** · 20 Jun 2021, 03:53 PM

Originally Posted by carlwhat

you really need a PO type of system. which would involve a number of tables such as:

an example of the po_details table:

and then you would need some code to input this table. and it all should tie to your products table.

i have done this before for clients, but it is not packaged up as a plug-in.

best.

This is the sort of approach I was thinking might at one time help... This I will have to give some thought. I suppose on easypopulate import, the added quantity could be added to what's there?