AntiFilterValues ( ListA ; ListB )
Return values only contained in ListA
Average rating: 4.3 (46 votes) Log in to vote
|Bruce Robertson - Show more from this author|
Assuming you have archived a list of record ID values for a table:
List of deleted records
Function definition: (Copy & paste into FileMaker's Edit Custom Function window)
Updated 5/3/2009 - correct results if listB is empty (returns ListA)
Updated 10/13/2008 - now correctly fully matches list item
Another custom function that does the opposite of the built-in FilterValues function. It returns the list of items that are only contained in ListA.
This one has high performance across tens of thousands of records and in my testing, beat performance of some of the compile and calculate list methods.
Deals with recursion limit by using a hardwired multiline calculation, and by dwindling the list.
Christopher Bailey, Boston MA
Feb 4, 2015
The use of a variable in here is problematic if, as is the case here, the variable is a name that might well be used elsewhere in a solution (like in a script that calls this function.)
I would recommend changing $top to $toptoptoptop, or something even more obscure like $Ilovebaconbutihatefrenchtoast .
John Davis, Houston
Jul 4, 2015
Kudos! Doesn't fail from the "duplicate sequential values" bug like so many other list functions. And fantastic to code around the recursion limit!
Christopher Bailey: How about renaming the local variable to $AntiFilterValues_top or even $AFV_top ? Might be unique enough to avoid conflicts and yet easier to decipher if pops up in debugging.
Kevin Frank, Arcata, CA
Jan 10, 2018
|One of my "you can pry it from my cold dead fingers" CF's... it's a rare week that I don't find a use for this. Thank you Bruce.|
Jan 11, 2018
|And thank you, Frank, master of FileMaker techniques and wonderfully helpful example files!|
Jan 11, 2018
|Ack. Kevin Frank, that is!|
Jun 3, 2019
As far as I can tell this function does produce the "duplicate sequential values" bug, but only when there are more than 20 values. The input below:
"There's a problem with consecutive occurrences of";
"in ListA when ListB has more than 20 values"
There's a problem with consecutive occurrences of
in ListA when ListB has more than 20 values
Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.