-
Notifications
You must be signed in to change notification settings - Fork 16
/
list.filter.fmfn
67 lines (55 loc) · 4.12 KB
/
list.filter.fmfn
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
/* 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 = ""] ; "" )