Tracking inventory and Sales where products NOT one to one

[mpau]

I'm building a somewhat complex inventory tracking database. The database tracks customers, vendors, orders, purchase orders and inventory. Inventory is built or depleted based on orders (deplete inventory) and purchase orders (increase inventory). I have built a successful model using the following tables (--||> signifies the direction of the one to many relationship in the tables):

Customer --||> Order--||> LineItems<\\--Inventory--||>PurchaseOrderLineItems<||--PurchaseOrder<||--Vendors

In the system I've built, PurchaseOrderLineItems properly increase inventory and LineItems properly decrease. Everything works on a one to one basis. The product bought is the same as the product sold.

The problem I'm having I need to build a model where NOT all inventory is bought and sold on a one to one basis. Instead some products that are bought for inventory aren't the end product sold to the customer. For example, boxes. Boxes are bought but not sold directly. Instead, each time a particular product is sold a box is used. So, I'm trying to tell the DB to deplete the inventoryStock of a box whenever a particular product is sold. I have figured out how to do this on the LineItem record but can't figure out how to get the Inventory Item record for boxes to see the result. Same is true for products that are sold that rely on and draw from several inventory parts.

Any thoughts or ideas? I'm stumped and open to any ideas.

I've thought about nesting products, about using category "stamps", about conditional value lists, and script triggers but can't figure out what to do. Right now, the best I've come up with is to Trigger a Script where each time a LineItem is created via a portal in an order record another LineItem is created for a Box with the same quantity. This works and I have the Script running on modification of quantity as well. But there are too many variables here. If the product LineItem is deleted the box line item needs to be as well, and I can't figure this one out. It also relies on a Next Portal Row script step which seems less than ideal for me.

I'm happy to email anyone the current solution if they'd like to mess around with it or have a closer look. I've attached a screenshot of the table view and the inventory view