FilterListItems_Filtrate.cf ( MyList ; FilterList )

Opposite of Filtervalues

Average rating: 4.5 (24 votes) Log in to vote

Jonathan Jeffery
igeek
http://www.igeek.co.uk

Sample input:

FilterListItems_Filtrate.cf ( "A¶B¶C¶D¶E¶F¶G¶H", "A¶E¶I¶O¶U" )

Sample output:

B
C
D
F
G
H

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

/*
Custom function to examine two lists, and return only the values from the first list (MyList) *not* found on the second list (FilterList)
i.e. the opposite of FM's FilterValues (  ) function.

jej@igeek.co.uk 30/7/12
 
*/

Let([

§Length = ValueCount(MyList ) ;
§ThisValue = GetValue( MyList ; 1 );

// Add the value to the output, if applicable
$$TEMPFILTERITEMLIST = 
If(
not PatternCount ( ¶ &  FilterList  & ¶ ; ¶ & §ThisValue & ¶ ) ; $$TEMPFILTERITEMLIST  & §ThisValue  & ¶;
$$TEMPFILTERITEMLIST  
);

// shorten the list
§NewList = 
If(
§Length > 1 ; RightValues ( MyList; §Length - 1) ;
"");

result = 
If(
// if there's still values to go
ValueCount ( §NewList ) > 0 ;  FilterListItems_Filtrate.cf( §NewList ; FilterList );

// else finish -- remove trailing ¶
Left( $$TEMPFILTERITEMLIST ; Length( $$TEMPFILTERITEMLIST ) - 1 )
);

// tidy up 
$$TEMPFILTERITEMLIST = ""
];

result

)

This is the opposite of FileMaker's FilterValues function. It will return only the items from the first list that are *not* on the second list.

Comments

		Malcolm Fitzgerald, Wellington Oct 7, 2016
This CF is Fastest of the "Opposite FilterValues" functions. I ran repeated tests ( dozens, not hundreds ) on each CF. I averaged the times and rounded down to the nearest 100ms. 1. FilterListItems_Filtrate.cf( MyList ; FilterList ) Jonathan Jeffery, igeek ~1400ms on 10,000 iterations 2. RemoveValue ( Values ; ValuesToRemove ) Koji Takeuchi, Freelance ~1700ms on 10,000 iterations 2. SubtractValues ( list ; list2 ) Jonathan Stark, Jonathan Stark Consulting ~1700ms on 10,000 iterations 3. AntiFilterValues( ListA; ListB ) Bruce Robertson, Concise Design ~5300ms on 10,000 iterations

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

FilterListItems_Filtrate.cf ( MyList ; FilterList )

Comments

Support this website.

Support this website.