3 Replies Latest reply on Mar 1, 2011 9:03 AM by philmodjunk

    Subtotal Help for Checkbox Selections



      Subtotal Help for Checkbox Selections


      I have a table of 20-25 different records.  Each record represents a possible option that can be selected in the confuguration process of piece of equipment.  Fields in each record are a Description of the item and a Cost.

      In that same database, I have another table that I use to actually go through and come up with the formal quotation for this product.  In this table, I have a field that shows the users all possible options that are valid for the above mentioned option table.  I use conditional (or dynamic, never can keep it straight) value list on this field to show the possible options (it's just a list of the descriptions from each record.  If I add more records at a later date as we offer different options, the value list update) and let the user check off the ones they want.  This part is working great.  Each record in this table represents a different quote, and this part is working great.

      My problem is, I can't figure out how to tie a cost to those items selected.  I'd like to have a field in this second table that shows the total of the cost for all options that have been checked, but for the life of me I can't figure out how to do it.

      If anyone has some insight they can offer, or examples that can be downloaded, I'd be very appreciative.


        • 1. Re: Subtotal Help for Checkbox Selections

          It would help to see the relationship between your two tables.

          Off hand, I'd be leaning away from using a single check box field with a single record in the second table for this. I'd probably be working with a set of records with a single value check box for each record. The user selects the check box for each feature that they want to include and then the costing is simple to set up as it is based on the presence or absence of a single value in the check box field.

          • 2. Re: Subtotal Help for Checkbox Selections

            Right now, I have a relationship between the main table, Quotation, and the other table in the same DB that includes all the possible options, Options.  I have a field in Quotation I call SELECTIONS that I link to a field in OPTIONS called DESCRIPTIONS.

            This relationship is used to allow me to have a Value List in table Quotation that relflects all possible options and updates automatically if any new are added or any are deleted.

            • 3. Re: Subtotal Help for Checkbox Selections

              Quotation::selections = Options::Descriptions

              Options has "20-25 different records.  Each record represents a possible option that can be selected..."

              Quotation consists of one record for each quote with Selections formatted as a checkbox group?

              I suggest this table structure instead:


              Quotations::QuoteID = Quote_Options::QuoteID
              Options::Description = Quote_Options::Selection

              Use a portal to Quote_Options to list each option you choose to include in the quote. Each Quote_Option record can look up the option cost from a field in options. You can add a field in Quote_Options formatted as a single value check box that the customer can click to make their final selections.

              You can then define a calculation field, cSelectedOptionCost, as IF ( Selected ; OptionCost ; "" )  where Selected is a single value check box field that enters a 1 if the box is checked. (You can resize the field so that only the checkbox is visible in the portal.

              Then a calculation field in Quotations: Sum (  Quote_Options::cSelectedOptionCost ) or a summary field in Quote_Options that computes the "total of" cSelectedOptionCost can compute the cost based on each option that the customer selects.

              The above table structure may look familiar. It's patterned after this structure for managing sales invoices and/or purchase orders:


              You can find an example of  this set up in the Invoices starter solution or in this demo file created by Comment that you can download: