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

Quick Find Loop

Find requests needed on a timpestamp field

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.


About Author

Siroos Jafary

Siroos is an experienced FileMaker Developer who have been successfully helping his clients with business automation and building custom apps. He offers best-fit solutions by combining native FileMaker techniques with modular programming methods, API integration, HTML and CSS, JavaScript, SQL, etc. His enthusiasm in learning and challenging his skills, helped him to be always up to date with new technologies in the industry. He is an active participant in FileMaker online forums to help other folks as best as he can while learning from them at the same time.

7 Comments

Sky

November 19, 2018 at 4:51 am

When you say:
“Also, you can not use FileMaker Quick Find on solutions that requires hiding the status area or when you open your solution on FileMaker go.”

I don’t think this is true as it is possible to use ‘Quick Find’ in these situations. All you need is a global text field for example, which the user enters their search text, and then run a script either using script trigger when user presses tab/enter etc. or simply a button next to the search field, that uses the ‘Perform Quick Find’ script step, specifying that global text field as the basis of the Quick Find.

For example, I might have a field called ‘myGlobalTextSearchField’ and then the Perform Quick Find script would look something like:

Perform Quick Find [ myTable::myGlobalTextSearchField ]

Though this doesn’t help with the wildcard searching of timestamp fields that you also discuss…, you could perhaps use a calculation field that returns a text result of the timestamp that strips out the / and : characters, substituting them with spaces, e.g.

’19/11/2018 07:45:06′ becomes ’19 11 2018 07 45 06′

This makes it possible to use Quick Find on the individual numbers in the timestamp. This calculated field can be positioned off-screen to the right of the layout.

The key thing to remember is that Quick Find only supports the match phrase operator, double quotation marks (” “), so you still cannot use * (zero or more characters wildcard) and the other Find operators such as range etc.

    Siroos Jafary

    November 22, 2018 at 11:35 am

    Thanks Sky,

    I re-phrased that sentence to reflect your suggestion.

    I really appreciate your input.

Quick Find Alternative, cURL, FMS Admin, more – FileMakerProGurus

December 4, 2018 at 8:10 am

[…] Source: FileMaker Quick Find Alternative – Intelligence Avenue Inc. […]

Phil Hanson

December 4, 2018 at 2:46 pm

Thanks for posting this Siroos.
In the merge field method, you mention that you can include fields from child tables. I might be wrong, but I don’t think it will trigger an auto enter recalculation if you change the content of a field in the child table. I think it only triggers if the fields in the calc are in the same table.
Unless you are using a script trigger on that field in the child table to recalculate the merge field back in the parent table?

    Siroos Jafary

    December 8, 2018 at 9:57 pm

    Thanks Phil for your comment. You are right and I did update the post and attached file to reflect that.

    Learning new things every day 😉

Peter Gort

December 4, 2018 at 5:32 pm

“neither a stored nor an unstored calculation” – it’s a stored calculation, and has the same overhead, since it is triggered whenever any of the contributing stored fields in the same table are changed. Note that that the auto enter calc will not fire on fields in child tables, only stored fields in the same table as itself. Having said all that, two very nice tools for the personal toolbox! My personal preference is for method 1 loop method, since it does not involve any extra database structure. Both methods, because they use actual find requests, are offloading the overhead to the server and getting the answers back. Therefore they perform much better than the native quickfind, especially over wan connections. Thanks for the article and the sample file!

    Siroos Jafary

    December 8, 2018 at 10:02 pm

    Peter,

    Thanks for bringing up this. Every object added to a database would take its share from file over head. Using this method would just have a significant less impact on file performance as you said comparing to stored calculations.

    I really appreciate your input.

Leave a Reply

Show Buttons
Hide Buttons