February FileMaker Functions - Filter
The Filter function uses a character filter to determine which characters in the original text are returned.
-
Filter ( textToFilter; filterText )
- Input: text
- Output: text
Consider the filterText as a list of permissible characters. This list is applied to the textToFilter and only those from the list are returned. So the expression:
Filter ( "Filter this text now" ; "aeiou" )
will return the string:
ieieo
This is a string of all the vowels in the original text in the order that they originally existed.
In real world use, the textToFilter may be a field name so that the expression returns permitted characters from those entered in a field.
NOTE: an important point about the filter function is that it is case sensitive. This means that the filterText must include upper and lower case versions of permitted characters if required.
Examples of Use
1. Remove non-digits from a field
The Filter function is commonly used to clean up data that should only be entered with digits and spaces. For example, a tax file number has nine digits separated into three groups of three (e.g. 123 456 789). But what if users sometimes enter 123-456-798 or 123.456.789? We can clean this up using the filter function. This is the expression:
Filter ( taxFileNumber; "0123456789" )
Note that the filter contains all the digits 0-9 to be retained.
To complete the job and insert spaces as required, we include the above logic in a Let statement:
Let ([
field = taxFileNumber;
cleanup = Filter ( field ; "0123456789" )
];
Middle ( cleanup; 1; 3) & " " &
Middle ( cleanup; 4; 3) & " " &
Middle ( cleanup; 7; 3)
)
This expression may be used in a field option - an auto-entered calculation that replaces the value entered.
2. Build an acronym
Since the Filter function is case sensitive, it can be used to filter an organisation name to an acronym. Using the expression:
Filter ( orgName; "ABCDEFGHIJKLMNOPQRSTUVWXYZ" )
If the orgName field contained the words Australian Broadcasting Corporation, the expression would return ABC.
3. Check codes by type
Let's say you have product codes that are numeric with a letter appended that categorises the product. So for A to H, the product is consumer; J to N is retail; P to Z is trade. We can 'translate' these product codes using a calculation:
Case (
Length (Filter (productCode; "ABCDEFGH")); "consumer";
Length (Filter (productCode; "JKLMN")); "retail";
Length (Filter (productCode; "PQRSTUVWXYZ")); "trade";
"undefined" )
Each of the filters will return a single character if the product fits that category. The Length function will therefore evaluate as 1 if the a character is returned and 0 if no character is returned. Thus each is a Boolean test returning true or false.
4. Validate data
Let's say that the only valid characters in a field are digits and uppercase letters A-F. We can test the data using this expression:
Length ( dataField ) =
Length ( Filter ( dataField; "0123456789ABCDEF" ) )
The expression is comparing the length of data entered in the field with the filtered version. If the field contained any illegal characters, the length of the data would be greater than that of the filtered version and the expression would return 0 (false).
