Brian Dunning's FileMaker Custom Functions

AntiFilterValues ( ListA ; ListB )

Return values only contained in ListA

  Average rating: 4.3 (46 votes) Log in to vote

Bruce Robertson   Bruce Robertson - Show more from this author

Share on Facebook Share on Twitter

  Sample input:
AntiFilterValues( "1¶2¶3¶4¶1¶"; "1¶3")

Assuming you have archived a list of record ID values for a table:
AntiFilter(OldRecIDlist; newRecIDList)
  Sample output:
2
4

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.

 

Comments

Christopher Bailey   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   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   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.
 
Bruce Robertson   Bruce Robertson
Jan 11, 2018
And thank you, Frank, master of FileMaker techniques and wonderfully helpful example files!
 
Bruce Robertson   Bruce Robertson
Jan 11, 2018
Ack. Kevin Frank, that is!
 
Alec Gregory   Alec Gregory
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:

AntiFilterValues (
List (
"There's a problem with consecutive occurrences of";
"radical";
"radical";
"in ListA when ListB has more than 20 values"
);
List (
"radical";
"t1";
"t2";
"t3";
"t4";
"t5";
"t6";
"t7";
"t8";
"t9";
"t10";
"t11";
"t12";
"t13";
"t14";
"t15";
"t16";
"t17";
"t18";
"t19";
"t20"
)
)

Produces:

There's a problem with consecutive occurrences of
radical
in ListA when ListB has more than 20 values
 
Tobias Sjögren   Tobias Sjögren
Mar 26, 2021
Are there any benefits to using this instead of the one I am currently using ?: https://www.briandunning.com/cf/2120
 

Log in to post comments.

 

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

Support this website.

This library has been a free commmunity resource for FileMaker users and developers for 20 years. It receives no funding and has no advertisements. If it has helped you out, I'd really appreciate it if you could contribute whatever you think it's worth: