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.
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"?