Checkbox portal logical AND function?

[Filebreaker]

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

[comment]

8 minutes ago, Filebreaker said:

Is there an easy way to get the portal to show the logical AND results of the checked boxes?

No, I am afraid it's not easy. Note that there are two possible interpretations of your request:

• Show people who clicked at least Meat AND Vegetables (and possibly other choices as well);

or:

• Show people who clicked exactly Meat AND Vegetables (and nothing else).

Each one of these would have to be implemented differently.

How many people are there overall? The easiest way would probably be to filter the portal - but that will not work well with a large related set.

[Filebreaker]

Aw, shucks. I was afraid of that.

It's a checkbox set with 40 different options. There are +/- 300 records of people, all of whom selected a maximum of 5 options on the checklist.

So what I want to achieve is the first interpretation you listed:

• Show people who clicked at least Meat AND Vegetables (and possibly other choices as well

Ideally, I'd like to be able to click 1, 2, 3, 4 or 5 boxes in the checkbox, and see the results of those who match that selection (so people who list at least that 1 box, or those 2 boxes, etc.).

What would be the best way to achieve this for a layman? Thanks so much for your response.

Edited May 6, 2016 by Filebreaker

[comment]

7 minutes ago, Filebreaker said:

There are +/- 300 records of people

That's not a very large number. I would suggest you define your relationship to use the x relational operator (that will make every record in each table related to every record in the other table), then filter the portal by an expression such as:

ValueCount ( FilterValues ( Parent::SearchValues ; People::Choices )  ) = ValueCount ( Parent::SearchValues )

Note that filtering a portal affects the display only and will be ignored at the data layer (e.g. in a calculation that references the related set).

[Filebreaker]

Hi comment,

You're a lifesaver, that worked! Thanks so much, it's truly appreciated.