AntiFilterValues ( ListA ; ListB )

Return values only contained in ListA

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

Bruce Robertson - Show more from this author

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)

/*
AntiFilterValues( ListA; ListB)
Finds values that are only contained in ListA

Example:

AntiFilterValues( "1¶2¶3¶4¶1¶"; "1¶3")
// "2¶4¶"

AntiFilter( ProductIDListFor_This_MonthOrders; ProductIDListFor_Last_MonthOrders)
// products ordered for first time

Bruce Robertson/Concise Design 
Updated 10/13/2008 - now correctly fully matches list item
Updated 5/3/2009 - correct results if listB is empty (returns ListA)
*/

Case( 
IsEmpty( ListA); "" ; 
IsEmpty( ListB & $top ); ListA;

ValueCount( ListB); 
Let( [
VCA = ValueCount( ListA);
VCB = ValueCount( ListB);
$Top = "##END##¶" ;
ListA = LeftValues( ListB; VCB) & $Top & LeftValues( ListA; VCA) ];
AntiFilterValues( ListA; "" )
);

Let([
VCA = ValueCount( ListA);
P = Position( ListA; $top; 0 ; 1 );
P  = PatternCount( Left( ListA; P); "¶");

L0 = MiddleValues( ListA; 1; 1);
L1 = ¶ & MiddleValues( ListA; 1; 1);
L2 = ¶ & MiddleValues( ListA; 2; 1);
L3 = ¶ & MiddleValues( ListA; 3; 1);
L4 = ¶ & MiddleValues( ListA; 4; 1);
L5 = ¶ & MiddleValues( ListA; 5; 1);

L6 = ¶ & MiddleValues( ListA; 6; 1);
L7 = ¶ & MiddleValues( ListA; 7; 1);
L8 = ¶ & MiddleValues( ListA; 8; 1);
L9 = ¶ & MiddleValues( ListA; 9; 1);
L10=¶ & MiddleValues( ListA; 10; 1);

L11 = ¶ & MiddleValues( ListA; 11; 1);
L12 = ¶ & MiddleValues( ListA; 12; 1);
L13 = ¶ & MiddleValues( ListA; 13; 1);
L14 = ¶ & MiddleValues( ListA; 14; 1);
L15 = ¶ & MiddleValues( ListA; 15; 1);

L16 = ¶ & MiddleValues( ListA; 16; 1);
L17 = ¶ & MiddleValues( ListA; 17; 1);
L18 = ¶ & MiddleValues( ListA; 18; 1);
L19 = ¶ & MiddleValues( ListA; 19; 1);
L20 = ¶ & MiddleValues( ListA; 20; 1)
];

Case( 
(L0 <> $Top) and (P > 20);
Let( R = Substitute( ¶ & ListA; // [¶ ; "¶¶"];
[L1; ¶ ]; [L2; ¶ ]; [L3; ¶ ]; [L4; ¶ ]; [L5; ¶ ]; 
[L6; ¶ ]; [L7; ¶ ]; [L8; ¶ ]; [L9; ¶ ]; [L10; ¶ ];
[L11; ¶ ]; [L12; ¶ ]; [L13; ¶ ]; [L14; ¶ ]; [L15; ¶ ]; 
[L16; ¶ ]; [L17; ¶]; [L18; ¶ ]; [L19; ¶ ]; [L20; ¶ ];["¶¶"; ¶]
); 
AntiFilterValues( MiddleValues( R; 2; ValueCount( R)-1) ;  "") ) ; 

L0 <> $Top ;
Let( R = Substitute( ¶ & ListA;  [¶; "¶¶"]; [ L1; ¶ ]; ["¶¶"; ¶] ); 
AntiFilterValues( MiddleValues( R; 2; ValueCount(R) -1);  "") ) ; 

MiddleValues( Substitute( ListA; ["¶¶"; ¶] ) ; 2; VCA-1 ) & Let ($top = ""; "")
)
)
)

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, 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, Kevin Frank & Associates 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 Jan 11, 2018
And thank you, Frank, master of FileMaker techniques and wonderfully helpful example files!

		Bruce Robertson Jan 11, 2018
Ack. Kevin Frank, that is!

		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 Mar 26, 2021
Are there any benefits to using this instead of the one I am currently using ?: https://www.briandunning.com/cf/2120

		Kevin Frank, Kevin Frank & Associates Apr 4, 2024
On large-ish lists AntiFilterValues is considerably faster than ExcludeValues.

		Kevin Frank, Kevin Frank & Associates Apr 4, 2024
For example, I tested this morning (FM 20/Windows 10) and removing 1000 values from a list with 50K items, AntiFilterValues takes 6.5 seconds; ExcludeValues takes 58 seconds.

		Kevin Frank, Kevin Frank & Associates Apr 6, 2024
Also a provisional simple fix for the bug Alec Gregory reported above... re-enable the commented-out portion in the first Substitute. I say "provisional" because I haven't tested extensively but it definitely fixes the bug in Alec's example.

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

AntiFilterValues ( ListA ; ListB )

Comments

Support this website.

Support this website.