Dynamic Portal Filters With Multiple Criteria

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.

Note: This article builds on a previous article, Dynamic Portal Filtering While You Type. It’s recommended that you read that article first if you haven’t already.

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:

Set Variable [ $cursorpos ; Get ( ActiveSelectionStart ) ]
Commit Records/Requests [ No dialog ]
Refresh Window [ Flush cached join results ]
If [ Get ( ScriptParameter ) = "Text1" ]
Set Selection [ Globals::Search_Text1 ; Start Position: $cursorpos ; End Position: 0 ]
Else If [ Get ( ScriptParameter ) = "Text2" ]
Set Selection [ Globals::Search_Text2 ; Start Position: $cursorpos ; End Position: 0 ]
End If

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.

Performance Consideration

Prior to FileMaker 11, portal filters were generally built using relationships (see Google-Like Searches Through Relationship Filtering). These tended to be more complex and difficult to build, and they had some serious limitations that can now be transcended with FileMaker 11 Portal Filters as outlined in this article. However, they had one advantage: they took advantage of FileMaker Pro’s built in indexes to retrieve the appropriate results.

With portal filters, FileMaker is checking each related record individually and applying the filter criteria to it. In cases where there are very large numbers of related records and/or the portal filter calculation is very complex, you may notice some performance issues.

Here are a couple of things you can do to maintain better performance:

  1. In your filter calculation, put the simpler conditional statements at the beginning of your “and” statements. Notice in the calculation examples I provide in Step Three above, that I first compare the Globals::Status field before checking the open text fields. This is because this is a very simple match statement. If this fails, the whole statement fails and FileMaker Pro moves on to the next record. (Note that this is based on speculation on my part. There are some programming languages that will evaluate an entire logic statement before determining its value; however, based on my experience, I am almost 100% certain that FileMaker Pro’s calculation engine does not.)
  2. Implement the Delayed Search For Fast Typing discussed in the previous article.

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

3 Comments

  1. fastserv
    Posted February 18, 2012 at 6:20 am | Permalink

    hi,
    I find these tutorials really helpful.
    Btw I have 2 question:
    this is really slow and unusable in my solution. As u said the fields will not be indexed in a portal. Any idea?
    More important, I need to filter the portal fields as follow:
    from minimum price to maximum price.
    For exempla: show all the record that costs more than €1,00 and less than €15,00.
    Any Idea?

    Thanks

  2. Posted February 20, 2012 at 11:32 am | Permalink

    @fastserv – Number ranges are actually easier to implement than the text filtering described in this article. You don’t need the custom function for that. You would need two global number fields. The filter calculation would be something like this: table1::cost ≥ globals::filter_minimum and table1::cost ≤ globals::filter_maximum.
    If you want it to show all records when the fields are empty, add an if statement that evaluates to true if the fields are empty.

  3. Eric
    Posted April 1, 2013 at 3:01 pm | Permalink

    The script is wonderful thanks Danny!

    Is wildcard search also applicable?

Post a Comment

Your email is never shared. Required fields are marked *

*
*