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
*/
// ===================================
/* FilterList ( ListA ; Attribute ; ListB ; CaseSensitive ) .v2.0__LITBR__FilterList () requires CustomList ( Start ; End ; Function )*/__LITBR____LITBR__// Limited => ListA < 18700 values and ListB < 18700 values too__LITBR__// Attributes : Equals - NotEquals - Contains - NotContains - BeginsWith - NotBeginsWith - EndsWith - NotEndsWith__LITBR__// Optional parameters : CaseSensitive : Boolean__LITBR____LITBR__// Result__LITBR__/* --------- CaseSensitive = empty or 0__LITBR__FilterList ( "One¶Two¶three¶Four¶five" ; "Equals" ; "One¶four" ; "" or 0 ) => "One¶Four"__LITBR__FilterList ( "One¶Two¶three¶Four¶five" ; "NotEquals" ; "One¶four" ; "" or 0 ) => "Two¶three¶five"__LITBR__FilterList ( "One¶Two¶three¶Four¶five" ; "Contains" ; "O¶t" ; "" or 0 ) => "One¶Two¶three¶Four"__LITBR__FilterList ( "One¶Two¶three¶Four¶five" ; "NotContains" ; "O¶t" ; "" or 0 ) => "five"__LITBR__FilterList ( "One¶Two¶three¶Four¶five" ; "BeginsWith" ; "F¶t" ; "" or 0 ) => "Two¶three¶Four¶five"__LITBR__FilterList ( "One¶Two¶three¶Four¶five" ; "NotBeginsWith" ; "F¶t" ; "" or 0 ) => "One"__LITBR__FilterList ( "One¶Two¶three¶Four¶five" ; "EndsWith" ; "o¶E" ; "" or 0 ) => "One¶Two¶three¶five"__LITBR__FilterList ( "One¶Two¶three¶Four¶five" ; "NotEndsWith" ; "o¶E" ; "" or 0 ) => "Four"__LITBR____LITBR__*/__LITBR__/* --------- CaseSensitive = 1__LITBR__FilterList ( "One¶Two¶three¶Four¶five" ; "Equals" ; "One¶four" ; 1 ) => "One"__LITBR__FilterList ( "One¶Two¶three¶Four¶five" ; "NotEquals" ; "One¶four" ; 1 ) => "Two¶three¶Four¶five"__LITBR__FilterList ( "One¶Two¶three¶Four¶five" ; "Contains" ; "O¶t" ; 1 ) => "One¶three"__LITBR__FilterList ( "One¶Two¶three¶Four¶five" ; "NotContains" ; "O¶t" ; 1 ) => "Two¶Four¶five"__LITBR__FilterList ( "One¶Two¶three¶Four¶five" ; "BeginsWith" ; "F¶t" ; 1 ) => "three¶Four"__LITBR__FilterList ( "One¶Two¶three¶Four¶five" ; "NotBeginsWith" ; "F¶t" ; 1 ) => "One¶Two¶five"__LITBR__FilterList ( "One¶Two¶three¶Four¶five" ; "EndsWith" ; "o¶E" ; 1 ) => "Two"__LITBR__FilterList ( "One¶Two¶three¶Four¶five" ; "NotEndsWith" ; "o¶E" ; 1 ) => "One¶three¶Four¶five"__LITBR__*/__LITBR____LITBR__//-------------------------------------------------------------------*/__LITBR__// Agnès Barouh - Juillet 2007 - To report bugs : : barouh.agnes@wanadoo.fr__LITBR__//-------------------------------------------------------------------*/__LITBR____LITBR__Case (__LITBR__ ValueCount ( ListA ) > 18700 or ValueCount ( ListB ) > 18700 ; "Too many Values" ;__LITBR__ IsEmpty ( ListA ) ; "" ;__LITBR__ IsEmpty ( ListB ) ; ListA ;__LITBR__ IsEmpty ( Attribute ) ; "Missing Attribute" ;__LITBR__ IsEmpty ( FilterValues ( Attribute ; "Equals¶NotEquals¶Contains¶NotContains¶BeginsWith¶NotBeginsWith¶EndsWith¶NotEndsWith" ) ) ; "Incorrect attribute" ;__LITBR__ not ( CaseSensitive = 1 or ( GetAsNumber ( CaseSensitive + 0 ) = 0 ) ) ; "Incorrect CaseSensitive" ;__LITBR__ Attribute = "Equals" and CaseSensitive < 1 ; Substitute ( FilterValues ( ListA ; ListB ) & "#|#" ; ["¶#|#" ; "" ]; ["#|#" ; "" ]) ;__LITBR____LITBR__ Let ([__LITBR____LITBR__ $TagB = Case ( IsEmpty ( FilterValues ( Attribute ; "Equals¶NotEquals¶BeginsWith¶NotBeginsWith" ) ) ; "" ; "#|#" ) ;__LITBR__ $TagE = Case ( IsEmpty ( FilterValues ( Attribute ; "Equals¶NotEquals¶EndsWith¶NotEndsWith" ) ) ; "" ; "#|#" ) ;__LITBR____LITBR__ $MyFirstList = ListA ;__LITBR__ $MyList = $TagB & Substitute ( Choose ( CaseSensitive ; Upper ( $MyFirstList ) ; $MyFirstList ) ; [ ¶ ; $TagE & ¶ & $TagB ] ) & $TagE ;__LITBR__ $Values = Choose ( CaseSensitive ; Upper ( ListB ) ; ListB ) ;__LITBR____LITBR__ Trigger = CustomList ( 1 ; ValueCount ( ListB ) ;__LITBR__ "Let ([Value = GetValue ( $Values ; [n] ) ; $MyList = case ( Not IsEmpty ( value ) ; Substitute ( $MyList ; $TagB & Value & $TagE ; \"X##X\" ) ; $MyList ) ] ; \"\" )" ) ;__LITBR____LITBR__ $MyList = Substitute ( $MyList ; [ "[#|#]"; "" ] ; [ "#|#"; "" ] ) ;__LITBR__ $Test = Case ( Left ( Attribute ; 3 ) = "Not" ; "<1" ; ">0")__LITBR__ ];__LITBR____LITBR__ Case (__LITBR__ CaseSensitive < 1 and Left ( Attribute ; 3 ) = "Not" ; Substitute ( FilterValues ( $MyFirstList ; $MyList ) & "#|#" ; ["¶#|#" ; "" ] ; ["#|#" ; "" ]) ;__LITBR____LITBR__ CustomList ( 1 ; ValueCount ( $MyList ) ; __LITBR__ "Let ([ Value = GetValue ( $MyList ; [n] ) ]; Case ( PatternCount ( Value ; \"X##X\")" & $Test & " ; GetValue ( $MyFirstList ; [n] )))" ) __LITBR__ )__LITBR__ )__LITBR__) & Let( [ $MyFirstList = "" ; $MyList = "" ; $Values = ""] ; "" )__LITBR____LITBR__// ===================================__LITBR__/*__LITBR____LITBR__ This function is published on FileMaker Custom Functions__LITBR__ to check for updates and provide feedback and bug reports__LITBR__ please visit http://www.fmfunctions.com/fid/150__LITBR____LITBR__ Prototype: FilterList( ListA; Attribute; ListB; CaseSensitive )__LITBR__ Function Author: Agnès (http://www.fmfunctions.com/mid/46)__LITBR__ Last updated: 10 May 2011__LITBR__ Version: 2.1__LITBR____LITBR__*/__LITBR__// ===================================
Comments
29 November 2011
(Edited by JeanLuc on 29/11/11 )
20 August 2013
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 )
20 August 2013
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
20 August 2013
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 )
13 September 2013
(Edited by Romain on 13/09/13 )
13 September 2013
En fait le 93 étant contenu dans le 1593, le 1593 ne ressort plus
Si je change l'attribut en "notequals" ca fonctionne.
13 September 2013
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 )
13 September 2013
J'étais dans une journée bugs étranges hier, ca a faussé mon discernement ! :)
(Edited by Romain on 13/09/13 )