Locating Matching Records, part 2

Note: FileMaker 12, released a few months after this article was written, introduced a “Find Matching Records” step, effectively eliminating the need for the techniques explored below (except for the section on case-sensitive searching).

Have you ever right-clicked into a field (or Ctrl-clicked if on a Mac) and chosen Find Matching Records? It works remarkably well, but oddly enough, there is no scripted equivalent for this command.

Of course if we do a Find Matching Records and then invoke Modify Last Find…

…we can see what FileMaker is doing behind the scenes.

Holy guacamole, that’s a boatload of backslashes. FileMaker is escaping reserved search operators, such as ? and #, as well as spaces, periods and hard returns — let’s call them special characters — and one way to do this is by prepending each of these special characters with a backslash, which tells FileMaker “treat the next character as literal text.”

We can take a stab at emulating this behavior by identifying as many of these special characters as time, patience and practical considerations allow, and then invoking the Substitute function to escape each of these characters in the source field. Even better, we can write a custom function so we only have to do that work once. And if we do, the custom function may end up looking something like the one in today’s demo file: single-click searches, v2

And if you’re wondering, “How did he decide which codes to include?”, I will state my usual disclaimer that this is a work in progress, and I don’t claim that the above is comprehensive, only that so far it is working for me. But to answer the question, I wrote a couple other custom functions, which you can see below, and which are also included in the demo.

The demo includes four buttons, corresponding to four search methods.

The two “Unescaped” methods are what you would get if you copied the text from the source field, entered Find Mode, pasted, and then clicked Perform Find. The only difference between the two is that the second one prepends == to ensure an “entire field match”.

The “Escaped ==” method a) prepends == and b) transforms the text via the above-referenced EmulateFindMatching custom function, and the rightmost button does the same, but then invokes a scripted loop to whittle the found set down to just records that exactly match the original, which is done via a test using the Exact function.

A couple final thoughts:

a) This routine is aimed at searching text fields; there’s no reason to use it for other field types such as date or number. It specifically will not work as written for date fields (if you prepend a date field search with == FileMaker will automatically convert the double equal sign to a single equal sign).

b) A different option for case-sensitive searching would be to change the default language for the field in question to Unicode. This is rarely a good idea, because this change will cause case-insensitive (i.e., standard FileMaker searches) to fail.