Using a Checkbox set to filter a portal result

[etsmarines]

I have a list of contacts. There is a field called constituencies that is a checkbox set of values. (Faculty, Staff, Student, Alumni, Parent, Donor etc...) The contacts may choose all that apply. Now I have another table to review these contacts with constituency as the primary key linked to the other table constituency field. This field is the same checkbox set. I have a portal that will display the contacts based on what checks are selected. The problem is that I cannot get the portal to display contacts exclusively. For example if i select alumni first in the checkbox I want the portal to display every contact that has the alumni attribute select. Then If i check another box in the field like parent I want it to only display the contacts that have alumni and parent checked.

Right now it is just adding to the list. So if i have alumni and parents checked it shows every contact with either or selection not just the ones with both selected.

What do I do?

THANKS!!

[mr_vodka]

You can change the auto-enter of your checkbox field ( most likely global field ) to only accept the last choice made.

Put an auto-enter w/ Do not replace unchecked:

GetValue ( Self; ValueCount ( Self ) )

[etsmarines]

Thanks for the response. I am not quite sure where you mean. I put the get function in the auto enter calculation part of that global field but it is not narrowing down the results still.

Thanks Again.

[mfrapp]

instead of a portal you could use a "go to related" script step using the same layout in list mode

[etsmarines]

im not sure what you mean. I have a bunch of contacts that have each uniquely filled out a constituencies checkbox set. then in another table with constituencies linked as the primary key to constituencies in the previous table. It is the same checkbox but when it is filled out it populates a portal with the contacts that correspond to the selections in the checkbox set. the problem is not that it is not working but that it doesnt narrow down the results. so If i check alumni, the portal shows all the alumni contacts (everyone that checked alumni in the constituency field) that is fine but then if i also check donor for example it does not get rid of the ones in the portal that arent donors. It just adds the donors to the list in the portal. This is actually decieving because it is displaying in the portal that these are all contacts that are both donors and alumni when really it is just a merged list of two sets. How do I get the portal to narrow its results based on the checkbox set selections.

[TheTominator]

How do I get the portal to narrow its results based on the checkbox set selections.

One way to get this behavior is to make a relationship using multiple keys.

Your current method connects the checkbox field directly to a data field in the other table occurrence. This gives the normal and correct behavior that you describe as letting the match happen when any of the checkboxes match. (This match is a logical OR.)

If you use this structure you need to make one connection for each of the independent ways you want to narrow your results (logical AND). In a perfect world you would make one connection for each of the checkboxes you have available. That could be way more than you need.

Create a new field in your target table (the one appearing in the portal) that is a calculation which copies your current Category for the record and appends a constant string that does not appear in a category, e.g.

Target Table

-----------

cCategoryToMatch=

Category & "¶" &

"Unchecked"

Add new calculated fields to the table which holds the checkbox field you are using to filter the portal. Add one for each of the checkboxes that you expect the user may need to narrow down the results. If you think they will check a maximum of 3 boxes, add three fields.

If your filter checkbox field is named gCategoryFilter, then add

Interface Table

------------

cgCategoryFilter1 =

Let(

[ filterSelection = GetValue(gCategoryFilter; 1)];

Case(

IsEmpty(gCategoryFilter); "";

IsEmpty(filterSelection); "Unchecked";

filterSelection

)

)

cgCategoryFilter2 =

Let(

[ filterSelection = GetValue(gCategoryFilter; 2)];

Case(

IsEmpty(gCategoryFilter); "";

IsEmpty(filterSelection); "Unchecked";

filterSelection

)

)

cgCategoryFilter3 =

Let(

[ filterSelection = GetValue(gCategoryFilter; 3)];

Case(

IsEmpty(gCategoryFilter); "";

IsEmpty(filterSelection); "Unchecked";

filterSelection

)

)

Note that the line

IsEmpty(gCategoryFilter); "";

is in there to make the portal display NO records when no checkboxes are checked in the filter. If you want ALL records to be displayed when no boxes are checked, remove that line from the Case statement.

Now wire up your relationship.

Connect each of the fields cgCategoryFilter1, cgCategoryFilter2, and cgCategoryFilter3 (and any more you created) to the field cCategoryToMatch.

[mr_vodka]

See if this helps.

PortalFilter_SingleValueCheckbox.zip

[etsmarines]

Thanks for all the responses I am still cannot get it. I attached the file if someone would like to look at it and tell me what I am doing wrong. The checkbox set that get filled out is under contact detail and other information tab.

The layout where I am filtering the portal result is called group find.

PMCA_CONTACT_MANAGEMENT.zip

[bruceR]

The filter works as is. What is the question?

[etsmarines]

I cannot get it to filter as a logical AND function. It only works as an OR function which is giving a merged list of two sets.

[TheTominator]

See if this attached file clarifies things.

CheckboxANDFilterPortal_fp7.zip

[TheTominator]

Another approach is not to use a portal at all. Instead use a scripted Find operation that will search for the appropriate matches in a new layout (as you are already doing) or a new window.

In FM 10 you can attach a script trigger to the checkbox filter field and have the search be automatically performed each time the user checks or unchecks a box so that it would update in real time just like the portal. Otherwise create a button to run the script called "Apply Filter" or something.

There are three conspicuous advantages to using a FileMaker Find instead of a portal.

(1) You don't need a bunch of extra calculated fields to support a relationship to another table.

(2) You are not limited by the number of checkbox values you can filter on due to the limited number of calculated fields you preconfigured.

(3) You are not limited by the number of rows in a portal for display. The results are a regular Layout List.

Oh (4).. there are (4) conspicuous advantages.

(4) You can easily add additional search criteria fields beyond the checkboxes, e.g. RecordStatus = Active or Archived, Sex = Male or Female, etc.

[etsmarines]

THANK YOU SO MUCH! IT WORKS!!!!!!!!!