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

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!
 

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.

Under construction. Email me your wish list for improvements.