Filter And FilterValues Explored

From Dwayne Wright PMP - Certified FileMaker Developer
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

Filter( textToFilter ; filterText), was introduced with FileMaker 7 but it didn't get that much attention becuase of the grander features in that release. Basically it asks you what text string or field you want to filter and then what text characters you want to see after the filtering process is complete. An example would be the filtering of a phone number field. You can set it up so only the number characters of zero through nine make it through the filter. Any other characters besides 0123456789, would make it to the final returned value.

Filter(”abc123xyz”, “abcdefghijklmnopqrstuvwxyz”) would return abcxyz
Filter(”abc123xyz”, “123456789”) would return 123

Some FileMaker functions will provide you with a return delimited result (DatabaseNames, WindowNames, etc...)

FilterValues( textToFilter ; filterValues) allows you to look at a string of return delimited text (textfilter) and say what values are ok to be passed on to the result (filtervalues).

This becomes very valuable when interacting with some FileMaker functions that provide you with a return delimited results such as DatabaseNames and WindowNames.

FYI... Return delimited text is a sting of text that is separated by carriage returns. That makes it easier to slice and dice that text later on for more complicated database procedures. Here is an examples of return delimited text,

Value 1
Value 2
Value 3
Value 4
Value 5

FYI... When looking at a calculations, the carriage returns are displayed with the character of ¶. So if you see a text string within a calculation that looks something like this ....


What you will see as it’s calculated text result will be ...


EXAMPLE: Let’s say we have an online application form for a position within our company. We allow people to check mark all the applications they are familiar with. If you use the application for many positions, some of the checked data may not be useful for a particular job. So when you do your printouts to take to a committee that does the hiring, you want to filter that data. It could look something like this...

FilterValues( Applications ; “FileMaker¶Microsoft Word¶Microsoft Excel) and the result will only contain those values, if the user clicked upon them.

You may want to nest one of these functions within the FilterValues function to see what values you have left over. From a string detection standpoint, you can almost do the same thing with the PatternCount function but filtervalue can allow you to have more elegant calculations.

In this example, we have a text field called values_to_filter and it has return delimited data. We also have a field called filtering, which we specify what we are looking for. The filtered_results calculation field uses the other two fields as its parameter and only returns the values we specified.

An example file can be downloaded by clicking (here)

More info about the author and FileMaker in general, contact me at

© 2008 - Dwayne Wright -

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.