FileMaker Quick Find Alternative

FileMaker is awesome but not in all aspects specially when it comes to FileMaker quick find. If you don’t believe me, just ask your end users how many times do they use FileMaker quick search in a day.

The biggest drawback of FileMaker quick find is that you can’t perform a wildcard search using it. Well, you can perform a wildcard search on text and number fields using FileMaker quick find if you add a prefix and a suffix asterisk (*) to your search criteria. Seems to be pretty easy but, how about searching on date, time or timestamp fields? Let’s say we want to find all records with a number “10” in a time stamp field. To perform a wildcard find on a timestamp field you need to add at least six find requests as bellow:

10/*/* *:*:*
*/10/* *:*:*
*/*/2010 *:*:*
*/*/* 10:*:*
*/*/* *:10:*
*/*/* *:*:10

This in not possible at all when it comes to quick find. Simply, you can’t have more than one request when using quick find. Therefore, your only option would be going to find mode, add above find requests plus any other find requests needed for other types of fields and then perform a find.

Also, you can not use FileMaker Quick Find on solutions that require hiding the status area or when you open your solution on FileMaker Go unless you put a global field on your layout with a script trigger to run a quick find based on content of the global field. But, still no wildcard searching is available.

In this blog post we will learn two alternative methods that give us more flexibility on performing quick find in FileMaker. Both methods can perform quick find in FileMaker on fields from table occurrence of the current layout and their child records but in slightly different ways.

The first method is more like a modular solution and can be easily added to any layouts without taking much of hassles once it is initially setup in your solution. Whilst, the other FileMaker quick find alternative method needs more work to be integrated into your solutions. It depends on your personal preferences and your file structure to decide which method would be the best fit.

Any find function in any application need a search criteria to search for it. Therefore, we need to add a global field on layout for users to type it in. Let us call it “QuickFind”. It is going to be a text field with global storage. It can be on current layout table occurrence or any other tables.

Now that we got the search criteria for from a user, let’s dig into details and futures used in these two methods:

Method 1)
Search using loop: This method loops through all fields on the layout and sets the search criteria on each of them while window is in find mode.
Obviously, it is a bit slow as it has to complete a loop through all fields.

First thing we need to perform a quick find in FileMaker using this method is creating a list of fields names on the target layout using FieldNames(fileName;layoutName) function. But, before looping through the fields, we need to substitute our “QuickFind” field name from this list. We don’t want to include it in the search.

Supertrim ( Substitute (FieldNames ( “”; Get(LayoutName));[“¶QuickFind¶”;”¶”];[“¶QuickFind”;”¶”];[“QuickFind¶”;””] ))

Note: Supertrim is a custom function written by Debi Fuchs to remove leading and trailing white space (including spacing, tabs, returns and line feeds) from a text string.

The next step would be setting user input as a variable so we can search for it. Let’s call it $search. We have to determine the fields types using FieldType(fileName;fieldName) before adding relevant number of find requests for each of them. If it is a text field, a number field or any calculation field returning a text or a number result, all we need is one request on it with a prefix and a suffix asterisk (*) like below:

*$search*

On date, time and timestamp fields, we would need to add multiple requests and set the filed to different search strings. Please refer to the sample file at the end off this blog post to see how many requests do we need for each field type.

Obviously container fields should be ignored as they are not searchable. If you really need to perform find on container file name or its content, you should extract them into a calculation field (with text result) and perform find on that.

After looping through all fields and adding relevant number of requests, we can simply add a perform find script step and we are done. This whole process can be done using on object exit or on object save script trigger.

Method 2)
Merge field: This method is super fast comparing to the method previously discussed but, it needs more work cause we need to add a text field on each table that we want to perform the quick find. It would be a text field with Auto Enter calculation to concatenate the content of the fields that we want to include in quick find. (fields from child tables can be added too but we would need a script trigger to update the content of this merged field because, auto enter calculation does not update on modifying child records. Thanks to Phil Hanson for bringing up this into my attention). This text filed has no negative impact on file performance because it is neither a stored nor an un-stored calculation field. The auto enter calculation would trigger whenever one of the referenced fields in auto enter calculation gets modified and stores the results as a text string.

Our quick find script would trigger on exiting or saving the “QuickFind” field to enter to find mode, set the merged field to $search variable and perform a find on the merge text field. As easy as that!
Note: No need to add the merged field on the layout. Set Field function works as long as the target field is part of direct or related context of the layout.

INSTALLATION

The file can easily integrate with your solution:

The loop method:

• Add a global text field (“QuickFind”) to your solution and use it as search box.
• Then import the script called “Trigger_QuickFindAltGeneral(Loop)” into your file and make sure that all references are valid in case you used different naming standards than mine.
• Set this script as on object exit script trigger to “QuickFind” field you just added to the file.
• You are all set; this script would trigger on exiting the search field and loop through all the fields on current layout to find matching records.

The text field method:

• Add a global text field (“QuickFind”) to your solution and use it as search box.
• Then import the script called “Trigger_QuickFindAltGeneral(TextField)” and “Trigger_UpdateSearchField” into your file and make sure that all references are valid in case you used different naming standards than mine.
• Set “Trigger_QuickFindAltGeneral(TextField)” script as on object exit script trigger to the global field you just added to the file.
• Set “Trigger_UpdateSearchField” script as on record commit script trigger on the search layout.
• You are all set; “Trigger_QuickFindAltGeneral(TextField)” script as on object exit script trigger to the global field you just added to the file. script would trigger on exiting the search field and search the merged text field to find matching records. And “Trigger_UpdateSearchField” script would update the content of the “SearchText” field on layout record commit.

DOWNLOAD LINK

You can download the FileMaker Quick Find alternative Methods sample file from here.
Username: admin
Password: admin

I hope these methods can help you with developing more robust and user friendly solutions. Please spread the word if you think it can help other developers too!

I am open to any comment or advise to improve the file.

Thank you.