The Screws and glue are stock items so need to be deducted from the stock table.
Do you mean that including these items in a Quote, should then reduce your inventory levels for those items? That seems reasonable for an Invoice, but seems premature for a quote since the customer may not accept your quote and then the items are never actually used to manufacture the panels.
Except for Inventory changes, A "Quote" and an "Invoice" are essentially the same thing in terms of what needs to take place in your database. What you describe has been discussed here a number of times in terms of an invoicing system that sells "kits" or "package deals". Where the user selects a "kit" and a script kicks in to list the items that make up that kit as individual line items on an invoice.
This starts from this basic data model for sales, quotes and purchase orders (only the table and table occurrence names change):
customer----<Quotes-----<QuoteLineItems>--------Stock (---< means "one to many" )
For a "Kit" feature, we add one more table plus an extra Tutorial: What are Table Occurrences? of Stock and you create "Kit" (Panel) records in Stock using a portal to a "components" join table between two occurrences of the Stock table:
Quotes---<QuoteLineItems>-----Stock----<StockComponents>----Stock|Components (Stock|Components is a new occurrence of Stock.)
The match fields for just the added stuff:
Stock::__pkStockID = StockComponents::_fkStockID
Stock|Components::__pkStockID = StockComponents::_fkComponentID
You then use a script performed each time that you select a new stock item for a new QuoteLineItem to check and see if this is a "Panel" (It will have related records in StockComponents if it is a Panel) and if so, pulls up the list of component IDs from the Join table and adds them to the QuoteLineItems. The script can multiply unit quantities from the StockComponents table by a Quantity field in the QuoteLineItems record to compute total material quantities to add to the additional line item records.
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
Thanks for this Phil,
I found another forum post that you answered that is hopefully close enough to my question that I can make it work. However if I get stuck I will be sure to post again.
I would also like to commend you for your dedication in helping out so many people who have posted questions on this forum. I am new to filemaker (2 months) and have been using the forum almost daily and I invariably find that the best answers come from you. I think the way you answer even the most simple questions with care and respect and without the snarkiness that is so prevalent online is amazing. You are a total godsend to a learner like me. Filemaker should give you a medal!!
All the best,
I have my order form working (I have taken your advice and called it 'order' from which I can generate quotes, invoices, production orders etc.) I have a couple more questions to try and make the system run more smoothly.
- When I select Panel A it now inserts the required stock package (1xPanel A, 1xGlue, 40xScrews) which is awesome however when I add Panel B it is obviously going to create new lines for glue and screws. Is there a way to combine duplicate portal rows together?
- How would I make the totals live update? Say I had entered 3xPanel A packages but I then discover I only need 2.
I have a feeling that the live update may be quite complicated but I figure I might as well ask and see.