Subtract more than one item from the same product

I am running a store that makes fabric bags. Each bag can be made from one or more products from the inventory, for example, two zippers, one strap, one tag, two hooks, etc. I need a formula that for each bag I sell it can subtract more than one item from each product in the inventory, this is kind of a cooking recipe. Please let me know if you need further information.

Thank you!

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

4 Likes

This looks like an excellent solution. My only addition would be another table to track components as they come in. Instead of manually entering the number of available components into the components table, make it a rollup from the incoming supplies table. Then add a field in the Components table that takes the total of everything that has come in and subtracts everything that has been used, which leaves the available component count.

2 Likes

I have a base in Universe that models this exact workflow
(supply --> components --> products --> stock --> sales)
but in the context of Jewelry. It could easily be modified to extend to this use case as well.

4 Likes

Yes, good idea. Inbound inventory is like a “reverse order” so new records in an inventory table rolls-up to the total inventory received

JB

1 Like