Subtract more than one item from the same product
Accepted Solutions
Hi @Arturo_Ayala - this is how I would approach this base design:
I would have 4 tables:
- Products - the bags
- Components - the zipper, the strap etc
- Product Compositions - The linking of components with products
- Orders - orders of products
Have a look at this base that I’ve mocked up:
The Products and Components tables are pretty straightforward. The Product Compositions records the components (and quantities of each) that go to make up a single bag:
So, Bag 1 has 2 zips, 1 Strap, 2 Hooks and so on. This might look like overkill at first glance, but I think it facilitates the solution you are after.
Orders records sales of each item (maybe with other order data like date, customer etc):
Now, back in the Products table you can see the link to orders and then do a count of Quantity Sold:
In the Product Compositions table you can lookup the product quantity sold and work out the total number of components sold based on the quantity per product (composition) and the quantity sold:
Finally, in the Components table you can record your opening (or total) inventory received, do a roll-up of the total inventory sold and take these two away to get the total remaining inventory:
Now, as items are recorded in the Orders table, the Total Inventory Sold and Remaining Inventory will increment and decrement appropriately.
As you take in more inventory - say another 100 zips, you update the Opening inventory field to be 200 zips, i.e. the total received over all time.
This is probably a simplified version of what you might have already but hopefully points you in the right direction to keep your inventory updated.
JB
Hi @Arturo_Ayala - this is how I would approach this base design:
I would have 4 tables:
- Products - the bags
- Components - the zipper, the strap etc
- Product Compositions - The linking of components with products
- Orders - orders of products
Have a look at this base that I’ve mocked up:
The Products and Components tables are pretty straightforward. The Product Compositions records the components (and quantities of each) that go to make up a single bag:
So, Bag 1 has 2 zips, 1 Strap, 2 Hooks and so on. This might look like overkill at first glance, but I think it facilitates the solution you are after.
Orders records sales of each item (maybe with other order data like date, customer etc):
Now, back in the Products table you can see the link to orders and then do a count of Quantity Sold:
In the Product Compositions table you can lookup the product quantity sold and work out the total number of components sold based on the quantity per product (composition) and the quantity sold:
Finally, in the Components table you can record your opening (or total) inventory received, do a roll-up of the total inventory sold and take these two away to get the total remaining inventory:
Now, as items are recorded in the Orders table, the Total Inventory Sold and Remaining Inventory will increment and decrement appropriately.
As you take in more inventory - say another 100 zips, you update the Opening inventory field to be 200 zips, i.e. the total received over all time.
This is probably a simplified version of what you might have already but hopefully points you in the right direction to keep your inventory updated.
JB
