7 Replies Latest reply on Jun 28, 2017 4:42 PM by philmodjunk

    Use check box to create subtotal

    kjw282

      I am creating a database of receipts for credit cards and I want to use the check box to only select those receipts that appear on the current bill as the bill comes due.  I want a total of the checked items and a total of the not checked items and then a grand total of both.  I started with the ESTIMATES Template and have been renaming and re-imagining the template.  I just don't know how to write the calculation for these fields.  I have Estimates Data and Estimates as the main tables.  I put the Check Box in Estimates Data fields and the totals appear in the ESTIMATES fields.

       

      Once I get this to be right, I want to produce a list from just the check box items, which will match my bill and those items no longer need to appear in my list because they are paid.  The remaining items will stay until I place an X in the check box and start the whole process again.

       

      Can someone help?

        • 1. Re: Use check box to create subtotal
          philmodjunk

          And what value does your check box set into the field when the check box is selected? I will assume the number 1 for this answer, substitute a different value as needed if you set up a different value list for the check box:

           

          Option 1:

          I am assuming a portal to Esitmates Date from a layout based on Estimates. Set up a relationship to a different occurrence of Estimates Data. If your relationship between Estimates and Estimates Data reads like this:

           

          Estimates::Estimate ID = Estimates Data::Estimates ID Match Field

           

          Make the relationship to the new occurence:

          Estimates::Estimate ID = Estimates Data|Selected::Estimates ID Match Field AND

          Esitimates::constSelected = Estimates Data|Selected::CheckBox

           

          Define constSElected as a calculation field with a number result type that always evaluates as the number 1.

           

          Now aggregate functions such as count, sum, max etc. can be set up like this:

          Sum ( Estimates Data|Selected::AmountField )

          or a summary field defined in Estimates Data can be added to your layout from the EstimatesData|Selected table occurrence to show the same value.

           

          Option 2:

          Don't modify your relationships, but add a one row filtered portal to Estimates Data to your layout. Use a portal filter such as: Estimates Data::CheckBox = 1 to filter only for selected items. Put a summary field from Estimates Data into this one row portal.

           

          Option 3:

          Use ExecuteSQL to get this same total. Both the match field and the check box field's selected value can be used in a Where clause with a Sum function after SELECT to get the needed total.

          • 2. Re: Use check box to create subtotal
            gofmp15

            A simple method when using a checkbox like this is to use a calculated field to hold/not hold the value and sum that field.

             

            In the line items table:

             

            CalcTTL: if (checkbox = YourValuie; totalfield; 0 )

             

            In the parent table or script you would sum(cacTTL)

             

            Note that this only works when the checkbox is checked and should only be used for that temporary use. You could capture the value and place it in a field in a permenant history record/table.

            • 3. Re: Use check box to create subtotal
              kjw282

              I set up field in “Estimates Data” table name cacTTL to read as follows:

               

               

               

              If (Select = "Yes"; Subtotal; 0)

               

               

               

              Then in the “Estimates” table, in the Subtotal field, I put in a calculation as follows:

               

               

               

              Subtotal = Sum(Estimate Data::cacTTL)

               

               

               

              It is not working.  What am I doing wrong?

              • 4. Re: Use check box to create subtotal
                gofmp15

                Did you check any of the checkboxes and is the valuelist set to "Yes"?

                 

                Open the layout and duplicate the checkbox field. Change it to an edit field and not a checkbox. That will tell you what the data is. Enter browse mode. Click in to the field. Type Command+I for a list of values in the field.

                 

                If yes isn't in there, that's the problem.

                • 5. Re: Use check box to create subtotal
                  kjw282

                  Yes to both questions

                   

                   

                   

                  Thank you!

                   

                   

                   

                  Inspire・Invest・Invigorate・Involve

                   

                   

                   

                  Karen Williams

                   

                  Executive Assistant to the Superintendent

                   

                  101 Twin Dolphin Drive, Redwood City CA 94065

                   

                  650.802.5554 | kwilliams@smcoe.org

                  • 6. Re: Use check box to create subtotal
                    kjw282

                    I am not as skilled as you in FileMaker to fully understand your solution.  To answer your first question, my checkbox value list is Yes

                     

                     

                     

                    In the option 1:  where does Estimates Data|Selected and Estimates::constSelected come from?  Are these new fields to create? 

                     

                     

                     

                    Thank you!

                     

                     

                     

                    Inspire・Invest・Invigorate・Involve

                     

                     

                     

                    Karen Williams

                     

                    Executive Assistant to the Superintendent

                     

                    101 Twin Dolphin Drive, Redwood City CA 94065

                     

                    650.802.5554 | kwilliams@smcoe.org

                    • 7. Re: Use check box to create subtotal
                      philmodjunk

                      In the option 1:  where does Estimates Data|Selected and Estimates::constSelected come from?  Are these new fields to create?

                      Estimates Data|Selected would be a new occurrence of the Estimates Data table. In other words, a new "box" in your relationships graph that also references the Estimates Data table.

                       

                      To repeat from my original post:

                      Define constSelected as a calculation field with a number result type that always evaluates as the number 1.

                       

                      This means that you create a new calculation field in the Estimates table. In my example, this would be a calculation field with a result type of number and where you simply type the number 1 in as your calculation.

                       

                      In your case, you have a check box field that enters the text "yes". Given that it's not working with gofmp15's suggestion, you have a problem there. either there is data in the field other than yes (which can be hidden by the check box format) or the field might be of type number instead of text.

                       

                      If you can be sure that "yes" is the only data in the field and it's defined to be of type "text", then you would make constSelected a calculation field with a text result and you would enter the quoted text "yes" as your calculation so that every record has the same text "yes" in this field to use to match records in the relationship.