FilterList ( )

Function stats

Average user rating
5.0000
46
150
9999
Support
FileMaker 8.0 +
Date posted
17 December 2008
Last updated
10 May 2011
Version
Recursive function
No

Author Info
 Agnès

17 functions

Average Rating 4.6

author_avatar



 

Function overview

Prototype

FilterList  ( ListA;   Attribute;   ListB;   CaseSensitive )


Parameters

ListA  


Attribute  Equals - NotEquals - Contains - NotContains - BeginsWith - NotBeginsWith - EndsWith - NotEndsWith


ListB  


CaseSensitive   Optional parameters : CaseSensitive : Boolean


Description

Tags:  List   Filter   Compare  

to compare and filter values, lists with different attribute : Equals - NotEquals - Contains - NotContains - BeginsWith - NotBeginsWith - NotEndsWith - EndsWith
Case sensitive

Not recursive Function - Optional Parameters : Case Sensitiveness ( boolean format or null )

FilterList () requires CustomList ( Start ; End ; Function )

----------------------------------------- more examples

FilterList ( FieldNames ( Get ( FileName ) ; Get ( LayoutName ) ) ; "NotContains" ; "Z¶zkp¶_" ; 1 )
Result = FieldNamesList Without the field whose name contains Z, zkp or _

FilterList ( Texte ; "Contains" ; "$¶_" ; 0 )
Result = values list With contains $ or _

FilterList ( LayoutNames ( Get ( FileName ) ) ; "EndsWith" ; "Dev" ; 1 )
Result = LayoutNames list whose the end name is Dev

FilterList ( List ( Table::MyField ) ; "Contains" ; Left ( FieldX ; 1 ) & ¶ & Middle ( FieldY ; 3 ; 3 ) ; "" )

Examples

Sample input

FilterList ( "One¶Two¶three¶Four¶five" ; "BeginsWith" ; "F¶t" ; 1 )


Sample output

three¶Four

 

Function code

/* FilterList ( ListA ; Attribute ; ListB ; CaseSensitive ) .v2.0
FilterList () requires CustomList ( Start ; End ; Function )*/

// Limited => ListA < 18700 values and ListB < 18700 values too
// Attributes : Equals - NotEquals - Contains - NotContains - BeginsWith - NotBeginsWith - EndsWith - NotEndsWith
// Optional parameters : CaseSensitive : Boolean

// Result
/* --------- CaseSensitive = empty or 0
FilterList ( "One¶Two¶three¶Four¶five" ; "Equals" ; "One¶four" ; "" or 0 ) => "One¶Four"
FilterList ( "One¶Two¶three¶Four¶five" ; "NotEquals" ; "One¶four" ; "" or 0 ) => "Two¶three¶five"
FilterList ( "One¶Two¶three¶Four¶five" ; "Contains" ; "O¶t" ; "" or 0 ) => "One¶Two¶three¶Four"
FilterList ( "One¶Two¶three¶Four¶five" ; "NotContains" ; "O¶t" ; "" or 0 ) => "five"
FilterList ( "One¶Two¶three¶Four¶five" ; "BeginsWith" ; "F¶t" ; "" or 0 ) => "Two¶three¶Four¶five"
FilterList ( "One¶Two¶three¶Four¶five" ; "NotBeginsWith" ; "F¶t" ; "" or 0 ) => "One"
FilterList ( "One¶Two¶three¶Four¶five" ; "EndsWith" ; "o¶E" ; "" or 0 ) => "One¶Two¶three¶five"
FilterList ( "One¶Two¶three¶Four¶five" ; "NotEndsWith" ; "o¶E" ; "" or 0 ) => "Four"

*/
/* --------- CaseSensitive = 1
FilterList ( "One¶Two¶three¶Four¶five" ; "Equals" ; "One¶four" ; 1 ) => "One"
FilterList ( "One¶Two¶three¶Four¶five" ; "NotEquals" ; "One¶four" ; 1 ) => "Two¶three¶Four¶five"
FilterList ( "One¶Two¶three¶Four¶five" ; "Contains" ; "O¶t" ; 1 ) => "One¶three"
FilterList ( "One¶Two¶three¶Four¶five" ; "NotContains" ; "O¶t" ; 1 ) => "Two¶Four¶five"
FilterList ( "One¶Two¶three¶Four¶five" ; "BeginsWith" ; "F¶t" ; 1 ) => "three¶Four"
FilterList ( "One¶Two¶three¶Four¶five" ; "NotBeginsWith" ; "F¶t" ; 1 ) => "One¶Two¶five"
FilterList ( "One¶Two¶three¶Four¶five" ; "EndsWith" ; "o¶E" ; 1 ) => "Two"
FilterList ( "One¶Two¶three¶Four¶five" ; "NotEndsWith" ; "o¶E" ; 1 ) => "One¶three¶Four¶five"
*/

//-------------------------------------------------------------------*/
// Agnès Barouh - Juillet 2007 - To report bugs : : barouh.agnes@wanadoo.fr
//-------------------------------------------------------------------*/

Case (
ValueCount ( ListA ) > 18700 or ValueCount ( ListB ) > 18700 ; "Too many Values" ;
IsEmpty ( ListA ) ; "" ;
IsEmpty ( ListB ) ; ListA ;
IsEmpty ( Attribute ) ; "Missing Attribute" ;
IsEmpty ( FilterValues ( Attribute ; "Equals¶NotEquals¶Contains¶NotContains¶BeginsWith¶NotBeginsWith¶EndsWith¶NotEndsWith" ) ) ; "Incorrect attribute" ;
not ( CaseSensitive = 1 or ( GetAsNumber ( CaseSensitive + 0 ) = 0 ) ) ; "Incorrect CaseSensitive" ;
Attribute = "Equals" and CaseSensitive < 1 ; Substitute ( FilterValues ( ListA ; ListB ) & "#|#" ; ["¶#|#" ; "" ]; ["#|#" ; "" ]) ;

Let ([

$TagB = Case ( IsEmpty ( FilterValues ( Attribute ; "Equals¶NotEquals¶BeginsWith¶NotBeginsWith" ) ) ; "" ; "#|#" ) ;
$TagE = Case ( IsEmpty ( FilterValues ( Attribute ; "Equals¶NotEquals¶EndsWith¶NotEndsWith" ) ) ; "" ; "#|#" ) ;

$MyFirstList = ListA ;
$MyList = $TagB & Substitute ( Choose ( CaseSensitive ; Upper ( $MyFirstList ) ; $MyFirstList ) ; [ ¶ ; $TagE & ¶ & $TagB ] ) & $TagE ;
$Values = Choose ( CaseSensitive ; Upper ( ListB ) ; ListB ) ;

Trigger = CustomList ( 1 ; ValueCount ( ListB ) ;
"Let ([Value = GetValue ( $Values ; [n] ) ; $MyList = case ( Not IsEmpty ( value ) ; Substitute ( $MyList ; $TagB & Value & $TagE ; \"X##X\" ) ; $MyList ) ] ; \"\" )" ) ;

$MyList = Substitute ( $MyList ; [ "[#|#]"; "" ] ; [ "#|#"; "" ] ) ;
$Test = Case ( Left ( Attribute ; 3 ) = "Not" ; "<1" ; ">0")
];

Case (
CaseSensitive < 1 and Left ( Attribute ; 3 ) = "Not" ; Substitute ( FilterValues ( $MyFirstList ; $MyList ) & "#|#" ; ["¶#|#" ; "" ] ; ["#|#" ; "" ]) ;

CustomList ( 1 ; ValueCount ( $MyList ) ;
"Let ([ Value = GetValue ( $MyList ; [n] ) ]; Case ( PatternCount ( Value ; \"X##X\")" & $Test & " ; GetValue ( $MyFirstList ; [n] )))" )
)
)
) & Let( [ $MyFirstList = "" ; $MyList = "" ; $Values = ""] ; "" )

// ===================================
/*

    This function is published on FileMaker Custom Functions
    to check for updates and provide feedback and bug reports
    please visit http://www.fmfunctions.com/fid/150

    Prototype: FilterList( ListA; Attribute; ListB; CaseSensitive )
    Function Author: Agnès (http://www.fmfunctions.com/mid/46)
    Last updated: 10 May 2011
    Version: 2.1

*/
// ===================================

 

Comments

JeanLuc
29 November 2011



Milles mercis pour cette fonction, qui fonctionne à merveille, elle m'a considérablement simplifié la vie à maintes reprises. JL
(Edited by JeanLuc on 29/11/11 )
  General comment
Andries Heylen
20 August 2013



Hi Agnes

I don't understand why you would return listA if listB is empty. For example if I have the following calculation:

Let ( [
_a = List ( 1 ; 2 ; 3 );
_b = ""
];
FilterList ( _a ; "equals" ; _b ;1 )
)

I would expect it to return empty, as I can't find any equal value from listA in listB.

Let ( [
_a = List ( 1 ; 2 ; 3 );
_b = ""
];
FilterList ( _a ; "notequals" ; _b ;1 )
)

should return _a completely as none of the values of listA can be found in listB.

Do you have any particular reason to return listA when listB is empty?

Andries
(Edited by Andries Heylen on 20/08/13 )
  General comment
Agnès
20 August 2013



Hello Andries,

Yes, is my choice when I wrote this function : in the calc are : " IsEmpty ( ListB ) ; ListA ;"

There was a discussion, in 2008, about it on the forum French, "what is the logical result if ListB was empty"... and finally no precise answer !
You can not put "IsEmpty (ListB), "";" because the result must depend on the selected attribute and I did not want to burden the already complicated calculation.
For Equal : IsEmpty ( ListB ) ; ""
For Not Equal : IsEmpty ( ListB ) ; ListA ;
For Contains or Not Contains.... What should be the result... The same for the other.

The easiest way was for me to decide if ListB was empty, the result was ListA.
but as the function is written long time ago, some of manage:
Case (IsEmpty (ListB, "", filterlist ()) so I think I can not change the result now.

It may be necessary to discuss the expected results according to the attribute used, and if it is not too difficult to calculate, I could change.
There were actually there even reflection on the BrianDunning'site but then no discussion after !

But you may be right, I do not know....

Bonne journée !
Agnès
  General comment
Agnès
20 August 2013



ok...
I opened filterlist;)
in fact, you just disable / / IsEmpty (ListB) ListA;
and the results are good (I think)=>
For attribut begins with "Not...., ListA is return, for Other, EmptyResult )

in 2008, I was at the time obsessed with "timing" and to make work the function as the list was empty certainly were not my ideas !

Tell me if I should remove it and do and make an update !
(Edited by Agnès on 20/08/13 )
  General comment
Romain
13 September 2013



Edit : not a bug at all !
(Edited by Romain on 13/09/13 )
 Bug report
Romain
13 September 2013



Je viens de comprendre ce qu'il se passe, mais du coup je ne sais plus trop si on peut le considérer comme un bug ou non (en tout cas c'est un sacré piège !)

En fait le 93 étant contenu dans le 1593, le 1593 ne ressort plus
Si je change l'attribut en "notequals" ca fonctionne.
  General comment
Agnès
13 September 2013



Hello Romain,

Non, a priori pas un bug, il faut effectivement utiliser NotEquals, car Contains or NotContains est "si l'un des termes de ListB est dans l'une des valeurs de ListA"
Si tu mets tes 2 listes avec Contains, cela te donnera au moins 1593 et 93

PS, si tu relis les notes (exemple) dans la FP tu as :
(...)
FilterList ( "One¶Two¶three¶Four¶five" ; "NotContains" ; "O¶t" ; "" or 0 ) => "five"
(...)

Pas un piège ;)
(Edited by Agnès on 13/09/13 )
  General comment
Romain
13 September 2013



Tu as parfaitement raison, c'est moi qui ai fait une mauvaise utilisation de ta fonction (au combien pratique et puissante).
J'étais dans une journée bugs étranges hier, ca a faussé mon discernement ! :)
(Edited by Romain on 13/09/13 )
  General comment