If FileMaker functions had sleeper hits (as though FileMaker functions have hits at all), PatternCount would be surely be one of them. It’s not like PatternCount does anything particularly impressive. Give it some text and a search term and it gives back the number of times that term appears in the text.
To wit:
PatternCount ( “This is my example text” ; “i” ) will give back the number 2.
PatternCount ( “Thirty dirty birds type terribly” ; “ty” ) will return the number 3.
Not amazing, I’ll grant you. Not even impressive when you think about it. But like the humble paper clip or coffee mug, what PatternCount lacks in pizazz, it recoups in utility. It’s really really useful.
Consider this assignment. You’ve got a database with a “roles” field. Users have been permitted to simply type in roles however they wish. Some put each role on its own line, others separate them with commas. And there’s no standardization of what values are acceptable.
Clearly a developer/dba of your caliber would never have let this ill-conceived field with its haphazard usage see the light of day. It just makes for an easily understood example, OK?
The boss person in charge of this database has tasked you with making a person’s name appear in red if that individual has administrative privileges as one of her roles. You know how to apply Conditional Formatting to a field, but because we’re formatting the Name field based on a value in the Roles field, it’s going to require writing a calculation. Given the state of the Roles field, the calculation Roles = “Administrator” isn’t going to work.
Here is where PatternCount saves your bacon. We can’t rely on the Roles field having any particular value, but we can figure out if the text “admin” appears in there somewhere. If the resulting count is greater than zero, we know we’ve got an administrator and can display her name in red.
This example isn’t the most crafty or clever use of PatternCount, but it does illustrate how knowing the number of times a term appears in a block of text can help solve programming challenges in ways that aren’t always obvious. Now that you know how PatternCount works, you’re sure to find many other uses for it as you continue developing.
Subscribe to our blog feed