2 Replies Latest reply on Jul 21, 2013 11:25 AM by EasternDreamer

    Relational Database and checkbox set



      Relational Database and checkbox set



           I'm Newbie and begginer with filemaker.

           I have a question regarding checkbox sets.

           I have two related tables : Found cats and Lost cats.  I have a checkbox set with color choices for each table : grey, white, black etc.. I have set a portal that finds matches with selected colors.  The thing is if only one checkbox matches it'll show up in my portal, but I want the whole set to match.  Right now a lost grey and white cat will match a found black and white because both have white checked.

           How can I ask that all checkboxes match?





        • 1. Re: Relational Database and checkbox set

               You'll need to set up a different field as your match field. Checkbox formatted fields build a list of values where each checkbox value that is selected is stored in the field and a return character separates it from the other selected values in the field. And when your match field has a return separated list of values, it will match to records where any one of the values matches to the value in the other field. (and if both are return separated lists, any one value need match to only one value in the other field.) This makes for a nice OR type match of values, but you want the logic to be AND.

               One approach is to set up a calculation field that replaces the returns with another character, but you'll also need to make sure that selected values are specified in a consistent order as the values from a checkbox formatted field are ordinarily stored in the order that the boxes were clicked by the user.

               Step 1, get the values stored in consistent order.

               Open Field Options for your check box field and give it this auto-enter calculation:

               FilterValues ( ValueListItems ( Get ( FileName ) ; "YourValueListNameInQuotesHere" ) ; self )

               And be sure to clear the "do not replace existing values..." check box.

               Step 2, define a calculation field that replaces the returns with a different character:

               Define a new calculation field with this expression:

               Substitute (YourCheckBoxFieldHere ; ¶ ; "|" )

               Select Text as the result type for this calculation field and use it in place of your original checkbox formatted field as the match field in your relationship.

          • 2. Re: Relational Database and checkbox set

                 OMG!  If I could buy you a drink I would!

                 I've been beating my head over this for the past week.

                 It works like a charm.

                 Thank you so much!