6 Replies Latest reply on Aug 17, 2010 9:04 AM by philmodjunk

    sorting with Checkbox Fields



      sorting with Checkbox Fields



      I'm using FMP 8.0 on a Mac.

      I have a checkbox field that contains 5 names, where I can check multiple names (let's call it "Name").

      I want to run a sort by Name.

      It doesn't work at all.

      why is that?

      Also i'm trying to total all amounts per a given name.

      I have a calculation that says:

      If (Name="John Smith; Field 1 + Field 2; 0)

      this doesn't work either.

      Any suggestions?

      thanks so much!


        • 1. Re: sorting with Checkbox Fields

          My main suggestion is to not use a checkbox formatted field for this. If you want subtotals for each name, that suggests you should use either radio buttons (if only one name should be selected) or a different table structure (If multiple names can be selected) that uses a related table to list all the specified names. It'll make life work a lot easier for you.

          To see why your sorts and calculations don't work. Put a copy of your check box field on your layout right next to the current checkbox field. Use the inspector to change the format of this field back to Edit box so you can see the actual data stored in the field.

          When you make multiple selections in your check box field, they are each stored in the field separated by a return in the order the check boxes where clicked by the user. Note that a field that contains two rows of text: John Smith <return> Jane Doe does not equal "John Smith". It contains John Smith along with the additional text of the return character and "Jane Doe". You'd have to use a function such as FilterValues to test to see if "John Smith" is one of the listed values in the field.

          • 2. Re: sorting with Checkbox Fields

            is there a way to do it without creating multiple tables and portals?

            the check box is perfect for what I want the user to do.

            I'm more than happy to create more fields to aid in calculations for proper filtering / summarizing.

            Is there a logic function that will help?

            i.e. - If (Field A="Expense" and FilterValues(Name;"John Smith")="John Smith; calculation; 0 )

            • 3. Re: sorting with Checkbox Fields

              is there a way to do it without creating multiple tables and portals?

              Not and do it properly which will make your life easier.  In fact, without using proper relational structure, you simply will hit dead-ends at every turn.

              the check box is perfect for what I want the user to do.

              But not for what needs to be done.  Number one mistake when starting design is to design by the outcome and what the user wants to see instead of proper design. 

              You need to create a good structure.  Once you have that in place, you can create the user interface from here and you can provide the user with (almost) whatever they need.  Don't duct-tape your solution ... it will come back and bite you.  And you'll then have to totally rebuild from scratch and it will cost you a lot of time and sanity.

              • 4. Re: sorting with Checkbox Fields

                Your final layout can even include check boxes for selecting names--they just wouldn't all be part of one field.

                • 5. Re: sorting with Checkbox Fields


                  I have to respectfully disagree.  it's not about making my life easier, it's about making life easier for the user.

                  Here's how I solved my problem.  Albeit pedestrian and long, it is functional.

                  Using @PhilModJunk's thought from earlier about FilterValues, I created fields for each name in the value list "Name" (i.e. Filter Smith = FilterValues (Name; "John Smith").

                  When I run my summaries or reports, I just add that IsValid(Filter Smith) as a qualifier.   Yes, it's longwinded and a lot of extra typing for me - but it works, and it's easy to track for other users.

                  Thanks so much for all your help.

                  • 6. Re: sorting with Checkbox Fields


                    I agree with LaRetta in large part here. You're not doing yourself any favors by setting up a solution that will simply become more complex as time goes by and your needs change. From what you describe, I suspect you can create the look and feel you want for your customer and still have the structure LaRetta and I have advised.

                    I feel my earilier post may have "helped" you into a less than optimum solution.