FileMaker Inspirations

Using FileMaker 11’s new Portal Filtering feature, you can now show filtered views of related records in portals without having to define new relationships. What’s better than that? How about dynamically filtering your portal data while you’re typing letters into your search field! With a very simple script (4 steps), a custom function and a script trigger, you can search data in your FileMaker portals the same way you search for music in iTunes!

Place a global search field above your portal. Add portal filtering that uses a custom function you will create. Add a script trigger calling a script that simply refreshes the page. And, you have a dynamically filtered portal that updates as you type.

The project consists of 5 basic steps:

1. Create a global search field.
2. Create the custom function (copy and paste).
3. Set the portal filter calculation (copy and paste with some modifications).
4. Create the script (4 script steps).
5. Set a script trigger.

Step One: Create a Global Search Field

Create a text field in any table. In the field options, set the new field to use global storage as shown in the figure below.

Under the storage tab in the field options, select the "Use global storage (one value for all records)" check box.

Step Two: Create the Custom Function

To achieve the affect we are looking for, we will set a portal filter which will use a custom function that we will write that will find records that have words that begin with the search criteria typed by the user. The reason we need a custom function is that we will need to iterate (loop) through each word in the fields we want to search and look for a match on any word entered as search criteria. For instance, if the user types “port art”, we want to find records with the text “article about portal filtering”, but not “airport art“.
Our custom function will take three parameters:

• needles: This is the search criteria (the needles we are looking for in the haystack).
• haystack: This is the text we are searching. It will most likely be the contents of one or more fields.
• start: This is a number representing the word within haystack where we will start searching. When calling this function, we would almost always pass the number 1. This will be used to iterate through all the words in the haystack.

The function will return true if, for each word in needles, there is at least one word in haystack that begins with that word. Otherwise, it will return false.

The function, which I am calling FindWordPartsInText, is as follows:

If (
	start > WordCount ( haystack ) ; 0 ;
	Let ( [
		needle = LeftWords ( needles ; 1 ) ;
		word = MiddleWords ( haystack ; start ; 1 )
		];

		Case (
			Left ( word ; Length ( needle ) ) = needle ;
			If (
				WordCount ( needles ) < 2 ; 1 ;
				FindWordPartsInText ( RightWords ( needles ; WordCount ( needles ) - 1 ) ; haystack ; 1 )
			) ;
			FindWordPartsInText ( needles ; haystack ; start + 1 )
		)
	)
)

At this point, you can copy and paste the above function and skip to step three if you wish. Don’t forget to add the three parameters listed above in the order listed and name the function FindWordPartsInText. If you give the function a different name, you will need to change the two calls to that function in the function text above.

If you are curious to know just how this function works, read on…

Function Details

In short, we start by comparing the first word in the needle with the first characters in the first word in haystack. If that match fails, we move on to the next word in haystack. Once a match is found, we move on to the next word in needle. If a match is not found and we reach the end of the haystack, we return 0 or false. If we reach the last word in needle and find a match, then we return 1 or true. This is done recursively by calling the function within itself.

Our function, FindWordPartsInText, is somewhat complex in that it has double recursion. It will loop through each word in haystack for each word in needles.

There are two base conditions:

• The first base condition is start > WordCount ( haystack ). Which tells us that we have reached the last word in haystack, and did not find a match, so we return false.
• The second base condition is when both Left ( word ; Length ( needle ) ) = needle and WordCount ( needles ) < 2 evaluate to true. This indicates that we have found a match and reached the last word in the search criteria (needle).

There are also two defining conditions:

• The first defining condition exists when we set our Let variables, needle (note needle is different than our parameter needles) and word. Then if a match is not found, we make the recursive call with the parameters needles, haystack, and start + 1. By adding 1 to the start parameter, we are telling the function to look at the next word and getting closer to the base condition of start > WordCount ( haystack ).
• The second defining condition exists when we find a match Left ( word ; Length ( needle ) ) = needle, which tells us that the first letters in the word we are searching are the same as the the search criteria. If this evaluates to true, and there are still words left in needle (WordCount ( needles ) < 2 evaluates to false), then we make the recursive call with the remaining words in needles, thus bringing us closer to our second base condition.

Step Three – Set the Portal Filter Calculation

To set up the portal filter, simply open the calculation window for your portal filter mentioned above and enter a simple calculation that calls the custom function we created in the previous step:

IsEmpty ( Globals::SearchField )
or
FindWordPartsInText ( Globals::SearchField ; RelatedTable::Field1 & ¶ & RelatedTable::Field2 ; 1 )

You will replace Globals::SearchField with your global search field that you created in Step One, and replace the RelatedTable::Fieldx text with fields from the table in your portal. You can have as many fields there as you want, just join them with & ¶ &.

Step Four – Create the Script

Our script needs to do three things: commit, refresh and put the cursor back where it was found. It will look something like this:

Of course, your global search field will take the place of Globals::SearchField.

In layout mode, right click on the search field and click “Set Script Triggers …” A dialog will appear:

In the script trigger dialog, select the action (or event) that you want to trigger the script, and select the script to be triggered.

Select the OnObjectModify action which will run the specified script any time text is entered in or deleted from the field. Click Select and choose the script that you created in Step Four.

…And You’re Done!

Now anyone can simply begin typing in the field and their portal contents will be refined as they type without even click a button.

Do you want to make it even better? . . . .

Enhancements

Delayed Search for Fast Typing

One of the drawbacks of having scripts and calculations run every time you type a character is that it can cause serious lag time while you are typing. Especially for fast typist, this can be very annoying, especially when doing searches on large data sets and/or working over a slow network connection.

This can be easily remedied using the Install OnTimer Script script step introduced in FileMaker 10. Simply create a new script that sets an OnTimer script to call the script you made in step four (we’ll call it “Task Filter Utility”). The script will simply have one step:

Install OnTimer Script [ “Task Filter Utility” ; Parameter: ; Interval: .3 ]

Now call this script from the script trigger instead of calling the Task Filter Utility script directly. Every time a user types another character, the script will be called again, thus resetting the interval. So the Task Filter Utility script will not be called until the user stops typing for 0.3 seconds. You can of course adjust that interval. I have found .3 and .4 to be good numbers.

One more thing!!! You must also add a script step to the Task Filter Utility script. Otherwise it will run over and over again every .3 seconds:

Install OnTimer Script [Interval: “” ]

Call the Install OnTimer Script step with no script or interval specified will halt the timer.

Multiple Search Criteria

What if you want to use multiple search fields? You can do that too!

To accomplish this, the filter calculation and script become slightly more complex. The calculation will need a condition for each field joined by the “and” operator, and you will want to set up the script to take a parameter indicating the field name. This way it will know which field to return to once it has committed and refreshed.

To continue reading about this, see Dynamic Portal Filters With Multiple Criteria where I walk through this in detail.

If you have any questions or comments feel free to post a comment below or contact me directly.