Why not just create a product record for each. One product record for the 12 Tea bag and one for 40 tea bags. Each product can be associated with the proper labels.
This feels like a Join Table which could be called Box (I am not very good at naming things). Basically, this Box table would contain a foreign key to relate to your Tea Product. You could specify the quantities in this table and reference it on your Order layout.
I hope this helps.
1 of 1 people found this helpful
I agree about the join table. Use your existing product table as a basis for the data in your new join table.
The join table would have each of the line items you specified (box,labels, teas, etc.). This would allow you to create as many variations on your product packages as you want.
Product Packages => Items In Package <= Product List
One Product Package would be made up of many Items In Package and the items come from the Product List.
Philip, I think I understand your goal. Selling a package with varying ingredients. That says to me that you will end up (as per your example) with the equivalent of 5 SKU IDs. One for the package and one each for the ingredients. In solving this you will want to make sure that you don't end up having to do a lot of work every time the marketing guys decide to add a new package or variant. To that end you might consider using a SKU for your package which forms a formula (or code) to identify the ingredients. Then a calculated lookup or scripted set fields for the other 4 based on Position/Character builds enough line items(4) of the correct qty and type to constitue the package. That is to say: If the first character in the Package is such&such then the qty for both the tea and the lables = such&such. If the second character is that&that then the variety of tea and the variety labels = that&that. And so on ... It will be those ingredient line items that will drive the inventory reductions.
So what I have presented to you is only a foundation. You will have at least a Invoice table, Inventory table, Ingredient table. The relationships are pretty straight forward. The Invoice is comprised of packages (at a price). As you add a package to the invoice your script will create/add the appropriate lineitems to the Ingredients table. The Inventory table will reflect a starting qty minus ingredients sold. Obviously, there are refinements such as having the price of a package adjust to the sum of the current ingredient costs plus markup, etc. All said, just plan it out so as to not compute yourself into a corner. It's complex but not complicated.
Thank you very much everyone for your very helpful answers to my question!
You really helped me a lot!