Create a dynamically filtered portal with multiple filter criteria.
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:
- Create the global search fields.
- Create the custom function (copy and paste).
- Set the portal filter calculation (copy and paste with some modifications).
- Create the script (6 or more script steps depending on number of criteria).
- 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.
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:
Commit Records/Requests [ No dialog ]
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:
… And You’re Done
Now you can search for data in your portal using multiple criteria and have results appear while you are typing.
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:
- 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.)
- 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.