3 Replies Latest reply on May 6, 2016 3:43 AM by beverly

    Checkbox portal logical AND function?

    filehelp

      Hi all,

       

      I recently “inherited” a FileMaker database at work, which I finally got mostly running despite being completely new to database building.

      There is just one part I simply can’t get to work.

       

      There are several records which use a checkbox. On a different layout, I linked this checkbox to a portal, so that it shows which records correspond to the checked boxes.

       

      This works just fine, except that it only uses the logical OR function, and I want it to show the logical AND function.

       

      So for instance, if my checkbox has Fish, Meat and Vegetables as options, and I click Meat and Vegetables, I want the portal to show only the names of people who clicked BOTH Meat and Vegetables, rather than just everyone who clicked Meat OR Vegetables.

       

      It sounds like this should be simple, but I haven’t been able to find the solution to this.

       

      Is there an easy way to get the portal to show the logical AND results of the checked boxes? I’m very, very new to Filemaker and not not good at all with at scripting, so please bear with me. Any help would be greatly appreciated, as this is essentially the final piece of the puzzle. I'm using FileMaker Pro 14.

       

      Kind regards,

       

      John

        • 1. Re: Checkbox portal logical AND function?
          erolst

          If your checkbox works via the data level (i.e. is part of the relationship definition), this isn't easy, because the so-called multi-line key technique is a logical OR.

           

          You can use a non-filtered relationship, plus a portal filter like

           

          Let ( [

            filter = YourOtherTable::checkboxSelector ;

            filterCount = ValueCount ( filter ) ;

           

            filteredPeopleChoice = FilterValues ( filter ; People::selectionField ) ;

            filteredPeopleChoiceCount = ValueCount ( filteredPeopleChoice )

            ] ;

            filterCount = filteredPeopleChoiceCount

            /*

            OR not filterCount

            add that predicate to filter down a full set, rather than filtering up an empty one

            */

          )

           

          and a script trigger for the filter field (e.g. OnObjectModify) to refresh the portal.

           

          Be aware that this works on the display level – so each and every related records need to be loaded in order to be evaluated against the filter condition. For larger related sets and/or slower connections, this can be very un-performant …

          • 2. Re: Checkbox portal logical AND function?
            beverly

            John, something with a FilterValues() may help.

             

            A global field (formatted with the same checkboxes) comparing the real field, perhaps?

            beverly

            • 3. Re: Checkbox portal logical AND function?
              beverly

              what erolst said.

              beverly