Inventory control in eCart5

I am a WebAssist Extension Library customer. I have 2 working eCart5 websites needing inventory control. Before I start to interpret the solutions posted in the forum I wondered if the PowerStore3 inventory control can be easily added to eCart5 and if so, do I need to buy it or is it available separately? Otherwise, would this forum support help me add a bespoke solution?

The inventory control methods that PowerStore uses are very specific to a PowerStore implementation. The ideas behind it would work the same in an eCart site, but the implementation would need to be specific to your site.

I think it would be best for you to create your own for your current site based on your needs. It all starts with the db and having columns for the inventory. You then need to adjust your admin area to work with inventory for an item.

The front end part is all about checking the inventory when an item is added to the cart, and when the items quantity is updated. After a successful transaction you will need to update the inventory for the purchased items.

Thanks Eric. Is what you're suggesting a manual solution? IE when a transaction is confirmed and the product is shipped the inventory field is manually revised to the new amount and if someone tries to put more then is in the stock field a "show region" appears saying "Out of Stock" triggered by a recordset? Would you recommend I follow the thread that I think "Best way to tackle inventory control? I have several ideas...need help" dated 2-17-2010 by sam308940? Is this a way to subtract what's ordered automatically from the Stock field? Finally, if I have a new cart requirement, would you then recommend I buy PowerStore?

The solution suggested is put together manually as there are no server behaviors for it. The idea is that you would check the requested quantity against the available inventory with a recordset. You would then do like you say and show an error message to the user to let them know. This is how it is setup in PowerStore.

In the thread you mentioned the query given by me is just for checking purposes, it will not update the inventory. To update the inventory will require doing a loop through the items in the cart and having an update occur for each one. This part is a little custom and will require some hand coding to get into place properly.

If you are thinking about using PowerStore it will handle all of this for you. Let us know if you want to go the manual route post back with any specific questions that you have.

Thanks Eric. I think I can see how to do this but will need some help:

I have a new field in the products table call inventory. Here we show the current level of stock.

On the product detail page with the "add to cart" button we create an additional, new recordset.

Questions:

In the new recordset, how do I subtract the inventory (a field) from the shopping cart quantity (a form variable)? I can see how you've done it for Sam subtracting the inventory from the order details:

SELECT (table1.inventory - table2.ordered) AS available_inventory
FROM table1 JOIN table2 ON table1.id = table2.foreignkey
WHERE available_inventory > 0

But I need to do a table.field minus a form variable. Is it done in the DW's recordset SQL variables?

Then I show an "Out of stock" message, using DWs "Show if recordset is empty" or is it ".... not empty"?

So you would like to check the quantity that the user has entered versus the available stock when they are trying to add it to the cart correct?

If you are trying to compare the posted value against a column in your table then you would need to do it within the advanced recordset creation view. In here you would be able to craft the query just like the sample that you noted, but where you are using the value from the form you should just enter a name for this value, then in the bottom you will need to define the query parameter. Click on add and set the name to the name you used in the sql, then for the value you can enter $_POST['<quantity field>'] as the runtime value, for the default value you should use something that will never work out, like 0.

Please post back and let me know if this is the way you are trying to do it and if this will work for you or if you have other requirements. Please post back with the details.

That's it in the first instance. I will need to do the automatic update later using the loop. But for now if we just crack the manual method.

If I call the column name: colname, the recordset would look like this:

SQL:
SELECT (table.inventory - colname) AS available_inventory
FROM table
WHERE available_inventory > 0

Variables:
Name: colname
Type: Integer
Default value: 0
Run-time: $_POST['<table.inventory>']

The recordset query looks ok to me, you would of course need to ensure that the posted element you list is the element with the quantity that the user enters. This way you will check on the available quantity minus the quantity that the user is trying to add.

I can't get this to work. Shouldn't the colname be in the WHERE clause? available_inventory is coming up as unknown column.

Also, I'm not sure how to ensure that the posted element listed is the element with the quantity that the user enters which checks on the available quantity minus the quantity that the user is trying to add.

The query seems a little off, you should try with it setup like this instead:

SELECT table.inventory
FROM table
WHERE (table.inventory - colname) > 0

For the runtime value of the colname parameter you should use the bindings to select the quantity field from the add to cart form.