I’m recently new to AppSheet and I want the quantity of products added to the cart to be subtracted from the inventory.
I have these tables: Products, Inventory, Orders, and Order Details. Orders and Inventory are not linked.
Products
| Product ID | Current Stocks (Virtual) |
|---|---|
| P001 | <br>SUM(SELECT(Inventory[Quantity],([Product ID] = [_THISROW].[Product ID])))<br> |
Inventory
| Inventory ID | Product ID | Quantity | Type |
|---|---|---|---|
| I001 | P001 | 10 | Buy |
Orders
| Order ID | Cart Details (Virtual) |
|---|---|
| OR001 | <br>REF_ROWS("Cart Details","Order ID")<br> |
Order Details
| Order Detail ID | Order ID | Product ID | Quantity |
|---|---|---|---|
| OD001 | OR001 | P001 | 1 |
I combined these templates: Order Capture How-to and Inventory Management
I also have these slices (if it helps): Cart and Cart Details. Only 1 order shows up inside Cart view.
What I would like to do is this:
Cart Details (after adding Product to Cart)
| Order Detail ID | Order ID | Product ID | Quantity |
|---|---|---|---|
| OD001 | OR001 | P001 | 2 |
Inventory
| Inventory ID | Product ID | Quantity | Type |
|---|---|---|---|
| I001 | P001 | 10 | Buy |
| I002 | P001 | -2 | Sell |
I tried to make a group of actions: Add to Cart then Remove Stock. For some reason, because of the sync delay, the Remove Stock action doesn’t work. Even when I tried to disable the delay, it still didn’t work. The Remove Stock action works in the Products view though.
LINKTOFORM("Cart Details_Form", "Product ID", [Product ID], "Order ID", SELECT(Cart[Order ID],TRUE))
ABS(LOOKUP([Product ID], "Cart Details", "Product ID", "Quantity"))*-1
Any help will be greatly appreciated. Thank you.
EDIT: Added more details if it helps.