Locating Matching Records, part 1

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.

Sometimes a seemingly-simple FileMaker challenge turns out to be more nuanced and educational than first impressions might indicate. This happened recently when I was asked to help make a scripted search behave properly. Most of the time, the existing routine worked correctly, but on certain records it would fail.

The challenge: Click a button to find all records with the same Note text as the current record.

No problem — how hard could that be? Any competent FileMaker developer can do this in his or her sleep, right? Well sometimes properly defining the problem turns out to be half the battle. Later, after the smoke had cleared, I built a demo to explore various approaches one might take…

…and we’ll get to that in a minute, but right now let’s look at the original script and the problem, or rather, series of problems, as they initially unfolded.

The Original Script

Since the solution was written for FileMaker 11, the middle two steps could have been eliminated, and the $variable could have been embedded directly in the Perform Find step, like so:

(For what it’s worth, in terms of backward compatability and readability I prefer the more verbose version.)

The Unfolding Series of Problems

Problem statement #1: When we search on a note containing “Prelude and Fugue”, we only want records matching that exact text to be returned. But here’s what we’re getting instead.

Of course — FileMaker is doing what it’s been instructed to do: locate all records that contain these three words: “prelude”, “and” & “fugue”, irrespective of order.

Suggested fix: prepend a double equal sign, to ensure an entire field match for your search results.

Problem statement #2: Your method doesn’t work on either of these records.

Indeed. These records contain meta-characters that FileMaker interprets as search operators. At right you can see the complete list for FileMaker 11.

I recall running into a similar search challenge back in the 1990s, when users were trying to locate a restaurant with the unusual name of “Hey! Juan”, and the exclamation point caused the search to fail.

We need these embedded search operators to be interpreted as literal text for the search to process them properly, and one way to accomplish this is by surrounding them with quotation marks.

Suggested fix: place the search text inside a Quote function.

Problem statement #3: Arggh. A search is broken that worked before you started suggesting so-called “fixes”… these two records contain hard returns and now I can’t locate either of them.

Oh yes, the “helpful” behavior where Quote converts hard returns to pilcrow (¶) characters. If we do a “click search” on the first record, we want the search to look like this…

…but instead we’re getting this…

…and of course since we don’t literally have a pilcrow character in the Note field, the search fails.

Suggested fix: first apply the Quote function, then transform any pilcrows back to true hard returns.

Problem statement #4: But what if I actually have a pilcrow character as a data element? And while we’re at it, what about the backslash character? Rather than attacking this piecemeal, can we try to anticipate every weird contingency?

Oh… all right. The Quote function takes care of most search operators and other meta-characters, but embedded hard returns and backslashes pose special challenges.

Suggested fix: temporarily encode hard returns and backslashes, then apply the Quote function, then decode hard returns and backslashes, by setting $searchString to…

Problem statement #5: Wow, fix #4 actually works… except… I just realized that we sometimes have blank Note entries. I want to be able to do single-click searches on them as well, but the above fix doesn’t work with them.

That’s correct: searching on == (or of course just plain =) will locate blank entries, but the above code searches on…

==""

…when you attempt to locate matching blank entries, and that won’t work.

Suggested fix: branch the search routine to test the entry for emptiness, like so:

The Demo

A few observations about today’s demo, single-click searches, v1:

1. The demo consists of 12 example records.
2. There are no duplicate note entries in the demo, therefore a method will be considered to “work” if it returns a found set a) containing one record, and b) if that record is the one that was originally clicked on.
3. Each button a) is color coded to indicate whether it will work correctly on a per-record basis, and b) features a tool tip showing the search code and the literal search that will be performed. For the color coding to make sense it is important that no records be added, deleted or modified.
4. All approaches except Method X involve grabbing the text, inserting it into a variable (typically transforming it in some manner), and then performing a find on that variable.
5. Method X performs a Go To Related Record via a self-join relationship on the Note field.

I don’t by any means consider this posting and demo to be the last word on the subject, but rather a work in progress, and welcome your comments and feedback.

In part 2, we’ll look at emulating what FileMaker does when you right-click in the field and choose “Find Matching Records”, and also touch on the question of case-sensitive searching.