-
Notifications
You must be signed in to change notification settings - Fork 16
/
Copy pathlist.custom.fmfn
157 lines (114 loc) · 8.76 KB
/
list.custom.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
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
/* Special Thanks to Ugo Di Luca - Grazie Mille, pour
l'aiguillage qu'il m'a fait prendre, merci à lui pour son
implication afin de vous rendre l'utilisation de
CustomList() plus limpide Merci pour tous ses commentaires
et sa notice ® Ugo Di Luca
===========================================================
// Author: Agnès Barouh - [email protected]
// CustomList ( Start ; End ; Function ) v_4.5
// [please, do not used "CLNum" in your calculation with Let() ]
// Objectives and examples :
- Build any list based on all Native or Custom Functions involving a 'Number' value as a parameter, such as :
Left(), Middle(), GetValue(), GetRepetitionNumber (), GetNthRecord(), GetLayoutObjectAttribute () ...
ex : - CustomList ( 1 ; Get ( FoundCount ) ; "GetNthRecord ( FirstName ; [n] )" )
will return James¶Henry¶Susan if your foundset has 3 records.
- Build any range based on Dates, Times, TimeStamps, and obviously Numbers
ex : CustomList ( 1 ; 5 ; "GetAsDate ( StartingDate ) + [n]" )
will return a range of 5 dates starting from the specified StartingDate
The 'Function' Parameter is nothing else than a literal calculation expression.
Therefore, CustomList allows for any filtering or parsing
process based on any condition you may need.
ex : CustomList ( 10 ; 100 ; "Let ( [ Value = GetValue ( MyList ; [n] ) ] ; Case ( PatternCount ( Value ; "X" ) ; Value ))" )
will parse any value containing a "X" in the 'MyList' chain,
in between the 10th and the 100th values.
See the 'Under the Hood' part at the end of the function to fully understand the process of this function
--------------------------------
/* MAJOR UPDATE */ Updated July'08
--------------------------------
CustomList is based on a totally new algorithm, and is now
voluntarily bridled to a maximum range of 500,000 values,
where the first version was technically limited to a max of
18,700 values.
Previous version still available here : http://www.briandunning.com/cf/747
The new CustomList() is faster and still is NOT recursive.
The arguments are unchanged which makes it compatible with
all your previous developments involving CustomList().
For Developer ease, the new CustomList() includes a
debugging mode. find the "*****DEBUGGING MODE*****" tag in
the formula below to switch mode. When debug is set to 1,
any error will be returned with its appropriate explanatory
code, else the result will be set to "?"
-------------------------------- */
// ----------- FORMULA STARTS HERE -----------
Case (
/*This function will not evaluate if Invalid parameters were passed for Start and End.*/
IsEmpty ( Start ) or IsEmpty ( End ) or End < 1 or Start < 1; "";
Let ( [
Start = GetAsNumber ( Start );
End = GetAsNumber ( End );
Diff = End - Start + 1;
/*Check for a range higher than 500,000 values. CustomList() is voluntarily restrained to this limit.*/
End = Case ( Diff > 500000 or End < Start or IsEmpty ( Start ) or IsEmpty ( End ); "Error"; End );
$null = "\"\"";
/*CustomList has its own recursion model. As CustomList may be involved into the "function" argument,
each CustomList expression used is passed to a repeating variable for evaluation*/
iter = Let ( $CLExeCount = $CLExeCount + 1 ; $CLExeCount & PatternCount ( Function ; "CustomList" ) + 1 ) ;
$CLn[ iter ] = Start - 1;
Calc = Case ( Diff ≥ 1600; 169; Floor ( Diff / 10 ) + 1 );
/*Here starts the "magic" of the Substitutions and the whole mechanism.
CustomList() is set to evaluate stacks of 1,700 values at a time, which is the
current limit of FileMaker internal Evaluate function */
First = Substitute ( ( 10 ^ Calc ) - 1; 9; "__________" ) & "_________";
X = Floor ( Diff / 1700 );
$CLRemainder[ iter ] = Diff - ( X * 1700 );
/*When the "Function" argument is left empty, CustomList() will return a numeric list based on the range defined */
FunctionR = Case ( IsEmpty ( Function ); "CLNum"; Substitute ( Function; ["[n]"; "CLNum"] ; [¶ ; ""] ) );
/*Each repeating variable content is parsed in order to get our String ready for the last evaluation - Special care is made for
French users here, please substitute the "definir" below with your local translation of the "Let" function if you're not using an english
version. The use of "Let ([" is recommanded anyway */
$CLExecute[ iter ] = Case ( Left ( Substitute ( Lower ( Function ); ["definir"; "Let" ]; [" "; ""]; ["¶"; ""]); 5 ) = "Let([";
Substitute ( "Let([$CLn[" & iter & "] = $CLn[" & iter & "] + 1 ; CLNum = $CLn[" & iter & "]" & First & "|";
[ "_"; "|¶Let([$CLn[" & iter & "] = $CLn[" & iter & "] + 1 ; CLNum = $CLn[" & iter & "] "]; [ "|";";" &
Replace ( FunctionR; 1; Position ( FunctionR; "["; 1; 1 ); "" ) & "&\"#^#|#^#\"&"] );
Substitute ( "Let([$CLn[" & iter & "] = $CLn[" & iter & "] + 1 ; CLNum = $CLn[" & iter & "]" & First & "|";
[ "_"; "|¶Let([$CLn[" & iter & "] = $CLn[" & iter & "] + 1 ; CLNum = $CLn[" & iter & "] "]; [ "|";"];" & FunctionR & ")&\"#^#|#^#\"&"] ) );
/*Final compilation starts here. The reminder part above each 1,700 values is treated now. */
Final = Case ( X > 0; Substitute ( ( 10 ^ X ) - 1; 9; "Evaluate ( $CLExecute[" & iter & "] & $null ) & " ) ) &
"Evaluate( LeftValues ( $CLExecute[" & iter & "] ; $CLRemainder[" & iter & "] ) & $null ) & " & $null;
/*The Final variable can now be evaluated to get our List*/
Result = Case ( End <> "Error"; Substitute ( "#^#" & Evaluate ( Final ) & "#^#";
[ "#^#|#^#"; "¶" ]; [ "¶"; "¶#^#" ]; [ "#^#¶"; "" ]; [ "¶#^#"; "¶" ]; [ "¶#^#"; "" ]; [ "#^#"; "" ] ) ) ;
$CLExecute[ iter ] = ""
// ----------- FUNCTION RESULT BELOW -----------
];
/*CustomList returns either the valid result, or an error formatted according to the debugging mode chosen above*/
Case (
( Length ( Result ) and ( Result = Filter ( Result; "?" ))) or End = "Error";
Let ([
/*****DEBUGGING MODE*****/ // Case Debug = 1, returned error "[error_CL], Number, Name and Calculation error" ,if Debug <> 1, returned error is "?"
Debug = "1";
Write = Substitute ( Function; "[n]"; 1 ); NumError = EvaluationError ( Evaluate ( Write ) );
Error = "[" & NumError & "] " & "Unlisted error | Unknown error, check calculation or check \"Start\" and \"End\" ¶102 | Field is missing¶103 | Relationship is missing¶106 | Table is missing¶113 | Function is missing¶1204 | Number, text constant, field name or \"(\" expected¶1205 | Comment is not terminated with \"*/\"¶1206 | Text constant must end with a quotation mark¶1207 | Unbalanced parenthesis¶1208 | Operator or function missing or \"(\" not expected¶1211 | List usage is not allowed in this function¶1212 | An operator (for example, +, -, *,;) is expected here¶1215 | This parameter is an invalid Get function parameter";
Pos = ValueCount ( Left ( Error; Position ( Error; NumError & " "; 1; 1 ) ) )
];
Case ( Debug = 1; "[Error_CL] | Return error : " & GetValue ( Error; Case ( Pos = 0; 1; Pos ) ) & ¶ & TextStyleAdd ( "Calculation ( for [n] = 1 ) : "; Bold ) & Write; "?" ));
Result ))
)
// ----------- UNDER THE HOOD -----------
/* Not very much afterwards...
Basically, CustomList() does two things :
1/ Transform your formula in a litteral chain :
CustomList ( 1; 4; "GetNthRecord ( Field ; [n])")
therefore becomes
"Let ([ CLNum = 1 ] ; GetNthRecord ( Field ; CLNum )) & ¶ &
Let ([ CLNum = 2 ] ; GetNthRecord ( Field ; CLNum )) & ¶ &
Let ([ CLNum = 3 ] ; GetNthRecord ( Field ; CLNum )) & ¶ &
Let ([ CLNum = 4 ] ; GetNthRecord ( Field ; CLNum ))"
2/ Evaluates this chain.
Interrested in the mechanism ?
My advice then : dissect this function by escaping the
'Result' and placing one of the numerous intermediary
variables available.
Special attention should be paid to the 'First' Variable,
everything starts from there !
*/