After a long time, I’m finally trying to find a bit more time again to continue this blog, and I’m starting with an article about an older custom function that I’ve been using regularly for years and that still impresses me with its capabilities.
This article is about the custom function “FilterList”, which allows you to filter one list with another list in many different ways while specifying an operator for the filtering.
The function was originally written (as far as I could trace) in 2013 (!) by Agnès Barouh, and to this day it remains one of my top ten custom functions.
It is simply excellent for working with virtual lists.
I decided to rewrite the function, but I did not reinvent it—I merely brought it up to a more modern standard.
There were two reasons why I revisited the function now:
When the function is used inside a loop to filter lists sequentially, new local variables are repeatedly created, which makes the Data Viewer harder to read.
The function uses a sub-function called CustomList (also a very clever function by Agnès Barouh). With the FileMaker WHILE function, this recursion is no longer necessary.
I am leaving out the “CaseSensitive” parameter here, as I have never needed it.
How do we proceed?
We build the function from the inside out and initially limit ourselves to just a single test parameter instead of a list, to keep things simple.
Let’s take a look at the existing filters and how we can implement them:
• Equals
• NotEquals
• Contains
• NotContains
• BeginsWith
• NotBeginsWith
• EndsWith
• NotEndsWith
We can implement the check using a Case statement:
Case (
operator = "Equals" and line = term ; line ;
operator = "NotEquals" and line ≠ term ; line ;
operator = "BeginsWith" and Left ( line ; Length ( term ) ) = term ; line ;
operator = "Contains" and PatternCount ( line ; term ) > 0 ; line ;
operator = "EndsWith" and Right ( line ; Length ( term ) ) = term ; line ;
operator = "NotBeginsWith" and Left ( line ; Length ( term ) ) ≠ term ; line ;
operator = "NotContains" and PatternCount ( line ; term ) = 0 ; line ;
operator = "NotEndsWith" and Right ( line ; Length ( term ) ) ≠ term ; line ;
""
)
Here, “line” represents the current element of the list being checked, and if the check is successful, this value is returned as the result.
So we process the list to be filtered line by line, which is very easy to do using a WHILE function in FileMaker, since we need exactly as many iterations as there are elements—i.e., lines—in the list.
Function with a single test parameter:
FilterListByElement (listToFilter, operator, element)
While (
[
max = ValueCount ( listToFilter ) ;
out = "" ;
i = 1
] ;
i ≤ max ;
[
line = GetValue ( listToFilter ; i ) ;
out = List ( out ; Case (
operator = "Equals" and line = element ; line ;
operator = "NotEquals" and line ≠ element ; line ;
operator = "BeginsWith" and Left ( line ; Length ( element ) ) = element ; line ;
operator = "Contains" and PatternCount ( line ; element ) > 0 ; line ;
operator = "EndsWith" and Right ( line ; Length ( element ) ) = element ; line ;
operator = "NotBeginsWith" and Left ( line ; Length ( element ) ) ≠ element ; line ;
operator = "NotContains" and PatternCount ( line ; element ) = 0 ; line ;
operator = "NotEndsWith" and Right ( line ; Length ( element ) ) ≠ element ; line ;
""
)
) ;
i = i + 1
] ;
out
)
Applying a list of filter elements
Now, instead of just a single element, we want to be able to apply a list of filter elements (listOfFilter).
To do this, we again need to process the filter elements one by one in a WHILE loop.
That means the Case statement itself is evaluated against each filter element:
List ( out ; While (
[
maxInner = ValueCount ( listOfFilter) ;
inner = "" ;
j = 1
] ;
j ≤ maxInner ;
[
innerlistOfFilter = GetValue ( listOfFilter ; j ) ;
inner = List ( inner ; Case (
operator = "Equals" and line = innerlistOfFilter ; line ;
operator = "NotEquals" and line ≠ innerlistOfFilter ; line ;
operator = "BeginsWith" and Left ( line ; Length ( innerlistOfFilter ) ) = innerlistOfFilter ; line ;
operator = "Contains" and PatternCount ( line ; innerlistOfFilter ) > 0 ; line ;
operator = "EndsWith" and Right ( line ; Length ( innerlistOfFilter ) ) = innerlistOfFilter ; line ;
operator = "NotBeginsWith" and Left ( line ; Length ( innerlistOfFilter ) ) ≠ innerlistOfFilter ; line ;
operator = "NotContains" and PatternCount ( line ; innerlistOfFilter ) = 0 ; line ;
operator = "NotEndsWith" and Right ( line ; Length ( innerlistOfFilter ) ) ≠ innerlistOfFilter ; line ;
""
)
) ;
j = j + 1
] ;
GetValue ( If ( Left ( operator ; 3 ) = "NOT" and ValueCount ( inner ) < maxInner ; "" ; inner ) ; 1 )
)
)
I’ll explain the line
“GetValue ( If ( Left ( operator ; 3 ) = “NOT” and ValueCount ( inner ) < maxInner ; “” ; inner ) ; 1 )”
and why it is structured this way in a moment.
First, let’s embed the inner WHILE loop into the existing custom function that handled only a single element.
FilterListByList (listToFilter, operator, listOfFilter)
While (
[
max = ValueCount ( listToFilter ) ;
out = "" ;
i = 1
] ;
i ≤ max ;
[
line = GetValue ( listToFilter ; i ) ;
out = List ( out ; While (
[
maxInner = ValueCount ( listOfFilter) ;
inner = "" ;
j = 1
] ;
j ≤ maxInner ;
[
innerlistOfFilter = GetValue ( listOfFilter ; j ) ;
inner = List ( inner ; Case (
operator = "Equals" and line = innerlistOfFilter ; line ;
operator = "NotEquals" and line ≠ innerlistOfFilter ; line ;
operator = "BeginsWith" and Left ( line ; Length ( innerlistOfFilter ) ) = innerlistOfFilter ; line ;
operator = "Contains" and PatternCount ( line ; innerlistOfFilter ) > 0 ; line ;
operator = "EndsWith" and Right ( line ; Length ( innerlistOfFilter ) ) = innerlistOfFilter ; line ;
operator = "NotBeginsWith" and Left ( line ; Length ( innerlistOfFilter ) ) ≠ innerlistOfFilter ; line ;
operator = "NotContains" and PatternCount ( line ; innerlistOfFilter ) = 0 ; line ;
operator = "NotEndsWith" and Right ( line ; Length ( innerlistOfFilter ) ) ≠ innerlistOfFilter ; line ;
""
)
) ;
j = j + 1
] ;
GetValue ( If ( Left ( operator ; 3 ) = "NOT" and ValueCount ( inner ) < maxInner ; "" ; inner ) ; 1 )
)
) ;
i = i + 1
] ;
out
)
OR vs. AND logic
The special behavior (which also caused me some headaches) has already been hinted at.
As you can easily imagine, the operators
• Equals
• Contains
• BeginsWith
• EndsWith
are all logically OR-connected.
If any filter element from listOfFilter satisfies the operator condition, the corresponding element from listToFilter is returned as a valid result.
For example:
FilterListByList (“Apple¶Banana¶Cherry¶Date¶Elderberry”, Equals, “Banana¶Date¶Fig”)
We expect the result to be:
“Banana¶Date”
because both elements are found.
For the operators
• NotEquals
• NotContains
• NotBeginsWith
• NotEndsWith
the filter must be applied as an AND, because all elements in listOfFilter must pass the check successfully.
Example:
FilterListByList (“Apple¶Banana¶Cherry¶Date¶Elderberry”, NotEquals, “Banana¶Date¶Fig”)
In this case, an element from listToFilter must not match any element in listOfFilter for the condition to be true.
Here, a (normally fatal) characteristic of the inner WHILE loop actually works in our favor.
The inner WHILE returns line as many times as the condition is satisfied, regardless of whether the element already exists in the result list or not. Each time, it is simply appended to the result list.
Now let’s take a closer look at the line:
GetValue ( If ( Left ( operator ; 3 ) = “NOT” and ValueCount ( inner ) < maxInner ; “” ; inner ) ; 1 )
This line always returns only the first element of the result list, thereby preventing duplicate result entries.
However, when the operator begins with NOT, the result list must contain exactly the same number of elements—ValueCount ( inner )—as there are elements in listOfFilter—maxInner.
Only then is the condition considered satisfied.
You have any questions? Feel free to send me an email, I’ll try to answer as soon as possible.