3 Replies Latest reply on Jul 30, 2015 7:02 PM by BradKnewstubb

    Quote Table with Auto Calculated Line Items



      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.


        • 1. Re: Quote Table with Auto Calculated Line Items

          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

          Caulkins Consulting, Home of Adventures In FileMaking

          • 2. Re: Quote Table with Auto Calculated Line Items

            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,


            • 3. Re: Quote Table with Auto Calculated Line Items

              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.

              1. 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?
              3. 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.