The Original FileMaker Community
Business Templates - Demo Apps - Video Tutorials -Samples - Help - 46000 Member Forum

The Leading Filemaker Developer Tools

FM Function of the Week: Filter – FileMaker Today

Get real time updates directly on you device, subscribe now.

In keeping with a text parsing theme I seem to have going here, this week’s function is the fabulous Filter function. It’s a text strainer that only permits the characters you specify to come out the other end. But you must be cautious, Filter behaves in very specific ways that might trip you up. Here’s the basic format.

The Filter Function showing its two parameters, text and filterText.Here, our text to examine is a phone number and a name. Our characters to include are the ten digits zero through nine. Filter will examine each character in the text to the left and retain only those that appear in the filter text to the right. All other characters are caught in the filter and don’t get through. So, as you’ve probably worked out by now, the result of the above calculation is “8675309″.

Filter is particularly handy in cleaning up scenarios where numbers and text have been combined in a single field. When migrating old data and attempting to apply some degree of normalization, teasing out the phone type from the actual phone number is a pretty common task. The next example shows how the Filter function can be of assistance.

Some examples of the Filter functionHere we can see how Filter allows us to extract two pieces of information from a single field and move each into its own discrete field. To be fair, data cleanup is rarely as clean as you see here. In a more realistic scenario, you’ll be dealing with a hodgepodge of text before and after the phone numbers, leading ones before the area code, and highly inconsistent formatting. Filter is just one of the text functions you’ll use to clean up a mess like that.

Important things to keep in mind about Filter.

  • Filter is case sensitive. Filter ( “Hello hello” ; “hello ” ) will return “ello hello”.

  • The order of the characters to filter is irrelevant. Filter ( “Hello hello” ; “oh le” ) will still return “ello hello”.

  • Spaces and punctuation are treated just like letters and numerals. If you want punctuation in your results, you’ll have to include it in your characters to filter.

Bonus Frivolity

Inspired by something my friend Lynn spotted at the most recent DevCon, here’s a completely silly use of Filter. If you’re a Futurama fan, see if you can figure it out before you pop it into your Data Viewer.

Filter ( “You Bum, lend of her – wish – grow wheat” ; “daring-Bets” )


More at:

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More