Advice on direction and script
I'm building a database to track the Inventory and Invoices for my small business making bath and body products. Before I start the next item on my list, I'd really appreciate some direction - I don't feel like it's beginner work.
I make various items - bar soaps, creams, sugar scrubs, etc. They are sold separately and are bundled into gift packs. For example, a "Pamper Pack" contains a bar soap, a jar of cream, a bottle of body wash, a jar of sugar scrub and a pack of bath salts.
For the purposes of sales, I need to track how many packages I've sold. For inventory purposes, I need to know how many of each item has been sold.
I have a field on the Inventory table ("Contained In") that lists all my packages. Each indivdiual product (ie. bar soap) has the appropriate packages ticked off. I will also identify the specific fragrance ("Item").
When I sell a package, I want to show the package sale on the invoice. Behind the scenes, I want to create an entry for each individual item sold so that the inventory is updated properly.
Clear as mud?
I'm seeing some scripting in my future but what is the best way to structure a solution? I don't need a completely customizeable solution - there can certainly be some hard-coding of values.
The script should be triggered upon sale of a package. I need to grab the package type and flavour and use those values to identify all the Invetory items that are contained in the package. Then I need to create an Invoice Data record ( or should it be a separate table? ) to show the sale of each indivdiual item.
All thoughts and advice are much appreciated.