FileMaker Inspirations

Create a dynamically filtered portal with multiple filter criteria.

With two (or more) global fields, portal filtering, a script trigger calling a simple script to refresh the page, you have a dynamically filtered portal with multiple filter criteria that updates as you type.

This project consists of 5 basic steps which are similar to those in the Dynamic Portal Filtering While You Type article. The main differences are that the filter calculation and script become slightly more complex. The steps are as follows:

The project consists of 5 basic steps:

1. Create the global search fields.
2. Create the custom function (copy and paste).
3. Set the portal filter calculation (copy and paste with some modifications).
4. Create the script (6 or more script steps depending on number of criteria).
5. Set a script trigger.

Step One: Create the Global Search Fields

This step is almost exactly the same as Step One in Dynamic Portal Filtering While You Type. Except that you will create one global search field for each filter criteria. Alternatively, you can create a single search field with multiple repetitions.

Step Two: Create the Custom Function

This step is exactly the same as Step Two in Dynamic Portal Filtering While You Type. If you have already created this function in your solution, you can skip this step.

Step Three: Set the portal filter calculation

The calculation will be an expanded version of the one in Step Three in Dynamic Portal Filtering While You Type. You will expand this calculation by adding a conditional statement for each of the filter fields and join all of these statements with the “and” operator. So, the entire expression will only evaluate to true if all filters contain a match.

This calculation will look different depending on the number of filters and the type of filters that make up your criteria. Here are a couple of examples:

Example 1: Calculation for One Pop-up field and one open text field:

The screen shot at the beginning of this article shows a portal of tasks with dynamic filter criteria to filter results by project status (a popup menu with fixed values) and an open text field which searches multiple fields. We will assume that the pop-up menu field is named Globals::Search_Status and the text field is named Globals::Search_Text.

In this case, we will want our filter calculation to evaluate to true if the Globals::Search_Status field exactly matches our status field in the task table (Task::Status), and our Globals::Search_Text field matches one of the words in the task title (Task::Title) or the task description (Task::Description).

Here’s the calculation:

( IsEmpty ( Globals::Search_Status )
or
Globals::SearchStatus = Task::Status )
and
( IsEmpty ( Globals::Search_Text )
or
FindWordPartsInText ( Globals::Search_Text; Task::Title & ¶ & Task::Description ; 1 ) )

Notice the use of the IsEmpty function to effectively ignore the filter if it is empty. The result is that if the filters are empty, we will see all of the records. Or if, for instance, Globals::Search_Status is set to “Open” and Globals::Search_Text is empty, we will see all task records with a status of open.

Example 2: One Pop-up and Two Open Text Searches

Taking the above example one step further, lets say we want to have a popup menu for status, and separate search fields for task name and task description.

With three filter criteria, we simply add another global field, and add another expression to our portal filter calculation.

In this case, we will have three conditional statements joined with the “and” operator:

( IsEmpty ( Globals::Search_Status )
or
Globals::SearchStatus = Task::Status )
and
( IsEmpty ( Globals::Search_Text1 )
or
FindWordPartsInText ( Globals::Search_Text1; Task::Title ; 1 ) )
and
( IsEmpty ( Globals::Search_Text2 )
or
FindWordPartsInText ( Globals::Search_Text2; Task::Description ; 1 ) )

Step Four: Create the script

Starting with the script we created in Step Four in Dynamic Portal Filtering While You Type, we will add support for a script parameter that indicates which field we are typing in. Then at the end of the script, we go back to the appropriate field.

Using Example 2 above, the script would look something like this:

Notice that we don’t do anything special if we’re editing the Status filter criteria. Since it is a pop-up menu, there is no cursor position to return to.

Step Five: Set a script trigger

As with all the other steps, we will start with the trigger we created in Step Five in Dynamic Portal Filtering While You Type. We will do this with each of the search criteria fields. In addition, we will include the script parameter indicating which field we are in.

Continuing to build on the example 2 above, we will add the parameter “Text1” to the Globals::Text1 script trigger:

Right click on the search field to get the “Set Script Triggers” window. Check the OnObject Modify and/or select it and click the “Select…” button, and the “Specify Script” dialog will appear. Select the script you created in Step Four and enter your script parameter in the “Optional script parameter:” field.

… And You’re Done

Now you can search for data in your portal using multiple criteria and have results appear while you are typing.

As always, if you have any questions or comments feel free to post a comment below or contact me directly.