Quote Table with Auto Calculated Line Items
I am putting together a database for a building product manufacturer and need some help with the quoting section of my database.
The company manufacturers prefabricated wall panels that come in about 20 different shapes and sizes as well as other products used to build a house with their system. I plan on using a join table to a stock table to store all the different items which will work to a point.
Each wall panel requires a certain number of screws and glue cartridges for installation and I would like to calculate this within the quote. The Screws and glue are stock items so need to be deducted from the stock table.
My questions are:
1. Is there a way to automatically create line items (Glue and Screws) when a wall panel line item is added? (Sometimes a quote will not include a wall panel so glue and screws can't be permanent).
2. If I can create this how can I auto update the quantities as more panels are added to the quote?
Thanks for any help.