Today we’re going to do some experiments with the FilterValues function, which takes two return delimited lists for input…
FilterValues ( textToFilter ; filterValues )…and produces the intersection of the two lists as output.
With the above in mind, when the function was first introduced back in 2004, I assumed that these two statements were equivalent:
FilterValues ( listA ; listB ) FilterValues ( listB ; listA )
But, as I was to realize in due course, they are not equivalent… for two reasons:
#1. As I would have known had I read the online help entry, the order of items in the first list determines the order of the items in the result, e.g.,
vs.
#2. When there is a significant disparity between the length of the two lists, deciding which list should come first will make a huge difference in how long it takes the function to return its result… and this happens to be the subject of today’s demo, which is based on a file Paul Jansen sent me several months ago and which I have modified in various ways and am sharing with his kind permission.
Instructions
1. Download and open the demo file: filtering-values-v1.zip
2. Click one of these two buttons
3. Enter some criteria, for example
4. Observe the results, e.g.,
Note: To eliminate the possibility of caching skewing the timings, data is generated fresh each time you click one of the “filtervalues” buttons.
Some Takeaways
- Unsurprisingly, the more work you ask FilterValues to do, the longer it will take to return a result.
- However, for a given pair of lists, Long/Short is significantly faster than Short/Long.
- For Short/Long, the amount of “overlap” between the two lists has an inverse effect on rendering time — the more overlap, the less time to render.
- Short/Long can take a painfully long time to return a result for larger list sizes, in which case the demo will attempt to prevent you from experiencing this pain by displaying a warning dialog and giving you an opportunity to bail out.
Note: Like all FileMaker functions ending in the word “Values” (plural), except for JSONListValues, FilterValues “helpfully” appends a trailing return to positive results. This has no significance for today’s demo, but can be disconcerting if you aren’t aware of the behavior.
Miscellaneous
Finally, there are a few other things going on in the demo that may merit a bit of exploration:
1. The use of NumToJText to ensure numbers appearing in the results dialog (and in #2 below) have commas as thousands separators.
2. The (mis-)use(?) of single segment button bars to produce dynamic labels.
3. Auto-enter calcs on the three global fields in the card window are an attempt to prevent clever users from engaging in Shift-key and/or cut-and-paste mischief…
- only items from the appropriate value list can be entered
- entry of multiple items is disallowed
- if field is cleared, the first item in the value list will be auto-entered
4. The use of the venerable and wonderful RandomNum_SFR custom function from Six Fried Rice (which they wrote about here and I wrote about here) to derive overlap values from the long list for the short list.