by John Mark Osborne, <jmo@filemakerpros.com>, http://www.filemakerpros.com
Highlight Find Criteria
BONUS: FINDMARK.FP3
PLATFORM: Mac/Win
About a year ago, I developed a solution for Scriptology - FileMaker Pro Demystified <http://www.scriptology.com/> that highlighted find criteria. What it does is mark your find criteria in red on every record in the found set. For example, let's say you performed a find on "Mary". Every occurrence of "Mary" in the resulting found set of records would be colored red so you could easily see it.
What This Does
This technique stores the find criteria in a global field where the text is colored red. A looping script cycles through the text on each record and places red text where it is needed using copy and paste. This sounds easy enough, but believe me when I say that I included it on Scriptology as an example of how to think out of the box. Just think of all the considerations you need to account for. For instance, what if the find criteria appears in the middle of a word. A standard FileMaker find will not locate this occurrence, but the Position function will. Needless to say, the looping script that does all of this is complicated given the approach taken.
If you have Scriptology and have seen this technique, you'll understand why I was so excited when I saw the new solution I am about to describe. A gentleman by the name of Thord Hertzberg sent me this new version of the highlighting find criteria solution. The advantages are clear. There is no need for copy and paste and the approach is easier to implement. I would even consider it a viable technique for your own FileMaker solutions. While the technique I am about to describe is not the ultimate answer, it does offer a very good alternative to my original solution.
OPEN FINDMARK.FP3
Before you continue, I would recommend testing the accompanying Bonus file titled FINDMARK.FP3. Launch this file and enter a one word find criteria into the Find Criteria field. While this technique will work with multiple find criteria, in order to explain it better, I have limited it. If you are interested in taking this technique a step further, I recommend contacting me directly for a personal chat.
Once you have entered your find criteria, click the Find button. It is very likely that you will find all the records since they all have the same data. If you like, spend some time entering different information on each record to test this technique better. Once your found set is located, notice that the first occurrence of the find criteria is already highlighted. To locate the next occurrence on the current record, click the Highlight Next button.
The Two Calc Fields
There are two calculation fields in this solution. One is called Black Text and the other Red Text. The Black text field contains all the text from the Text field you are searching up until the current occurrence of the find criteria. The Red Text calculation returns the find criteria as it appears in the Text field, but in a red color. The current occurrence of the find criteria is determined by the Counter field. When you run the Highlight Next script, here is what happens:
Set Field [“Counter”, “Case(PatternCount(Text, Find Criteria) = Counter, 1, Counter + 1)”]
That's right. Just a single script step. Essentially, all the script does is increment the Counter field. This Counter field is used in both the Black and Red Text calculations to determine which occurrence of the find criteria should be highlighted. Let's say the the Counter field was at "2". The Black Text calculation would result in everything up to the second occurrence of the find criteria. Take a look at the calculation to understand why:
Left(Text, Position(" " & Text, " " & Find Criteria, 1, Counter) - 1)
The Red Text
The Black Text calculation is a complex calculation. What I mean is it contains nested functions. The Position function is nested within the Left function. The Position function returns a dynamic value to the Left function, rather than a static parameter. This means that the Left function can grab variable text depending on the number that the Position function returns. Let's break this calculation down by entering numbers for some of the variables. We already decided the Counter was set to "2" and the Find Criteria was "Mary". Here is what the calculation looks like now:
Left(Text, Position(" " & Text, " " & "Mary", 1, 2) - 1)
Let's say the Text field contains the following text:
"Mary had a little lamb. Her fleece was white as snow. Mary had a little lamb. Her fleece was white as snow. Mary had a little lamb. Her fleece was white as snow."
Since the Counter field supplies a "2" to the occurrence parameter for the Position function, it skips over the first occurrence of "Mary" and locates the second. This occurrence of "Mary" is the 55th character in the string. That means the calculation now looks like this:
Left(Text, 55 - 1)
One (1) is subtracted from the location of the find criteria because you want all the characters up to the 55th character.
The Red Text
The Red Text calculation works in essentially the same way, except it uses the Middle function. It's purpose is to return the find criteria as it appears in the field you are searching:
Middle(Text, Position(" " & Text, " " & Find Criteria, 1, Counter), Length(" " & Find Criteria) - 1)
Pick this calculation apart in the same way as we did with the Black Text calculation to discover how it works.
While you are dissecting the calculation, notice that a space (" ") is concatenated at the beginning of the Text and the Find Criteria fields. This is done in order to locate the real occurrences of the find criteria. Let's take two examples to explain this. Let's say the find criteria you are using is "here". The Position function will locate words like "nowhere", "somewhere" and "everywhere" because these words contain the word "here". However, a FileMaker find will not locate these words because they do not begin with the word "here". A FileMaker find would locate "heretic" because it begins with "here". In order to use the Position function, we have to trick it into only finding what we want. Adding a space to the beginning of the Find Criteria field prevents the Position function from locating the find criteria in the middle of words. A second example explains why a space is added onto the beginning of the Text field. Let's say the first occurrence of the find criteria is the first word. The Position function won't locate it now because a space (" ") has been added to the beginning of the Find Criteria. The solution is to add a space at the beginning of the Text field.
There, That Wasn't So Bad...
Hopefully, you are not too confused as to how all these calculations allow you to highlight a word. It should be obvious what they do. But, how does it benefit you? Well, the feature that makes it all work is a merge field. A text block is placed directly over the field you are searching so that it has the same dimensions. In the text block are two merge fields. The Black Text field is first and is set to a black color or the same color as your text. The Red Text field is inserted right next to the Black Text merge field, but is colored red.
In Conclusion
When you take a look at this solution as a whole, it is really fairly simple. All that is needed is two calculations, some merge fields and a script. The calculation are the most complex aspect, but even they are not that difficult. So, implementing this technique in your own solution shouldn't be that hard and will greatly benefit your customers when they are trying to determine where the find criteria is in the text they just searched. Happy FileMaking!
## END ##