6 Replies Latest reply on May 14, 2012 4:41 PM by UnionGuy

    Calculation not using checkbox field?



      Calculation not using checkbox field?


      I have memberships in our organization separated into 3 categories, with checkboxes for each category. Also, I have another calculation field to look at the categories and assign them either Plan 1,3, or 4. When I change a member's category, the Plan field becomes empty. Why is that? Is it because the checkbox data is not text? How do I get the Plan calculation to use the checkbox fields to ascertain which Plan to use?

      Here is the Plan calculation formula:

      If  (CCFT Member = "CCFT Member"; 1; If (Fair Share Full Rate Payer = "Fair Share Fee Full Rate Payer"; 3; If (Fair Share Discounted Rate Payer = "Fair Share Discounted Rate Payer"; 4; "")))

      Again, the membership fields are set up as checkboxes.

      Thanks for the help.

        • 1. Re: Calculation not using checkbox field?

          Simplest solution would be to use a case statement: (And maybe a Radio Button Set instead of a checkbox set. Because with checkboxes you could accidentally check multiple boxes.)


          Case (
          Membership = "CCFT Member" ; "1" ;
          Membership = "Fair Share Full Rate Payer" ; "3" ;
          Membership = "Fair Share Discounted Rate Payer" ; "4" ;


          An other way to do this is to create a seperate table that holds the different kinds of Memberships and their plan.
          That way it's easier to make changes or addittions.

          I've made the two examples in a test file:

          • 2. Re: Calculation not using checkbox field?

            Both ideas seem like they might work. I had forgotten about tables; good idea. Thanks.

            • 3. Re: Calculation not using checkbox field?

              The fact that you are using checkboxes could be a problem. If it's possible to select more than one value in a checkbox group formatted field, then this expression:

              Membership = "CCFT Member"

              May not return true even though the "CCFT Member" check box has been selected. The data in the field may actually be:

              "CCFT Member"<Return character>"another value"

              If you have edited your value list at some point, you may even have data hidden in this field that does not appear as a selected checkbox.

              valueCount ( Filtervalues ( membership ; "CCFT Member" ) )

              will return a non-zero result (True) if the check box is selected even if there is other data in the field, separated by a return character.

              If you think that you may have data hidden in your field due to changes to the value list, I strongly recommend that you take the needed steps to clean up the data in this field. You may need to put a copy of the field without any checkbox formatting and inspect the data in the fields to see what's actually there.

              • 4. Re: Calculation not using checkbox field?

                PhilModJunk - Yes, I have had problems with other checkboxes in not clearing data out when the value lists are changed. I usually use only one value per checkbox, but still other data creeps in. To check for extraneous data in checkbox fields, I change the field to text and then use Table View to see what else is in there. In this case, I do not see any other data, but I will use your tip to see if anything else comes up. Thanks for the tip.

                I loved checkboxes in AppleWorksClarisWorks. They worked so elegantly, flawlessly, efficiently. I might have to give them up in FileMaker Pro.

                • 5. Re: Calculation not using checkbox field?

                  I use checkboxes both singly and as groups all the time with little trouble, but it's important to understand their strengths, weaknesses and potential pitfalls.

                  Non listed data should not "creep in" to a check box formatted field unless a layout's design permits it or you change a value list without also making matching changes to the actual data stored in the field.

                  • 6. Re: Calculation not using checkbox field?

                    You are right, PhilModJunk. The data did not just creep in mysteriously. I put it there inadvertently when i imported the data from AppleWorks. Checkboxes there use True and False to show a checked box or not, and the Falses were imported in. I should have cleaned those up first thing, but I did not, and now I am paying for it. Garbage in, garbage out.