CustomList ( Start ; End ; Function )

Update for FileMakerServer. Update for FileMakerGo. Update, new version - customized lists - not recursive - Unlimited

Average rating: 4.3 (131 votes) Log in to vote

Agnes Barouh - Show more from this author
Tic Tac
http://www.tictac.fr/CoinFileMaker/Page.html

Sample input:

CustomList ( 3 ; 4 ; "GetValue ( MyList ; [n] )" )

---------------

CustomList ( 25000; 85000; "[n] & "". "" & Get(CurrentDate ) + [n]")

---------------

CustomList ( 2; 5 ; "GetNthRecord ( FieldRecord ; [n])")

Sample output:

returns "Not¶Recursive" if MyList is
"CustomList¶Is¶Not¶Recursive¶But¶Now¶Unlimited"

---------------

25000. 12/12/2076
25001. 13/12/2076
25002. 14/12/2076
25003. 15/12/2076[...]

---------------

FieldRecord2
FieldRecord3
FieldRecord4
FieldRecord5

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

/*       Special Thanks to Ugo Di Luca - Grazie Mille, pour l'aiguillage qu'il m'a fait prendre,
                          Thank you for all his comments and his notice  ®  Ugo Di Luca 
===========================================================

//   Author: Agnès Barouh - filemaker@tictac.fr

//   CustomList ( Start ; End ; Function )  v_4.8.1
// [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 litteral 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

--------------------------------
/* Add Nota for variable "iter" */ 10/16/2015
/* MAJOR UPDATE */  Updated July'08
/* UPDATE */  Updated August'10 for adapte to FileMakerGo
/* UPDATE */  Updated November'11 for adapte to FileMakerServer
/* UPDATE */  Updated November'12 for adapte to FileMaker IWP


--------------------------------

CustomList is based on a totally new algorithm, and is now volontarily 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 Developper 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;
                         V =  Case ( Left ( Get ( ApplicationVersion ) ; 2 ) = "Go" ; 1 ; Left ( Get ( ApplicationVersion ) ; 2 ) = "Se" ; 2 ; Left ( Get ( ApplicationVersion ) ; 2 ) = "We" ; 3 ; 0 ) ;
                         PL = Choose ( V ; 1700  ; 400 ; 300 ; 900 ) ;

                         /*Check for a range higher than 500,000 values  ( max 150000 values for FmGo and 120000 for FmS and 250000 for IWP ).
                          For FMPro/Adv, CustomList() is volontarily restrained to 500 000 but you can but you can go beyond.*/

                         End =   Case ( ( Diff > Choose ( V ; 500000  ; 150000 ; 120000 ; 250000 ) ) 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*/

/****** NOTA : If you change the name of the CF, "CustomList", Change here too. ****/
                         iter = Let ( $CLExeCount = $CLExeCount + 1 ; $CLExeCount & PatternCount ( Function ; "CustomList" ) + 1 ) ; 
                         $CLn[ iter ]  = Start - 1;

                         Calc = Case ( Diff ≥ ( PL - 10 ) ; ( PL / 10 )  ; 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 ( 400 for FileMakerGo), which is the 
                         current limit of FileMaker internal Evaluate function */

                         First = Substitute ( ( 10 ^ Calc ) - 1; 9; "__________" ) ;  
                         X = Floor ( Diff / PL );
                         $CLRemainder[ iter ]  = Diff - ( X * PL );

                         /*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 (  First ; [ "_";  "Let([$CLn[" & iter & "]=$CLn[" & iter & "]+1;CLNum=$CLn[" & iter & "];" &
                                            Replace ( FunctionR; 1; Position ( FunctionR; "["; 1; 1 ); "" ) & "&\¶&¶"] );
                                            Substitute ( 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 !
*/

// Note : 10/16/2015
Carrefull : If you rename CustomList, you must also rename it in code / line "iter"

iter = Let ( $CLExeCount = $CLExeCount + 1 ; $CLExeCount & PatternCount ( Function ; "CustomList" ) + 1 ) ;
becomes
iter = Let ( $CLExeCount = $CLExeCount + 1 ; $CLExeCount & PatternCount ( Function ; "list.custom" ) + 1 ) ;
for example if your rename in list.function

is just a security far unic iter when you used many CustomList in CustomList
( is not really an real incidence but is better, it is a protection )

-----------------------------------------***
Edit November-6 2012 :
v4.8 > Adapte the calc for FileMaker IWP
Carreful : Limited
with FilemakerServer = End - Start ≤ 250000

-----------------------------------------***
Edit November-10 2011 :
v4.7 > Adapte the calc for FileMakerServeur
Carreful : Limited
with FilemakerServer = End - Start ≤ 120000

-----------------------------------------***
Edit August-24 2010 :
v4.6 > Adapte the calc for FileMakerGo
Carreful : Limited
with Filemaker = End - Start ≤ 500000
with FilemakerGo = End - Start ≤ 150000
-----------------------------------------
Edit July-24 2008 :
v4.5 > Replace "Num" by "CLNum"
[please, do not used "CLNum" or $CLn in your calculation with Let() ]
-----------------------------------------
v.4.4 - 5 July 2008 | Change Iter - Change if Result is Empty - And Ugo sign the Notice
v.4.3 - 3 July 2008 | Change FunctionR -> handles litteral carriage returns
-----------------------------------------
is an "Update", new version of CustomList () first version : [http://www.briandunning.com/cf/747]
-----------------------------------------

End or Start are no longer limited,
only the interval between End - Start is limited : ( End - Start ) ≤ 500,000
you can now target from the 50,000th to 100,000th records or more
-------------------------
New calculation, Faster version.
-------------------------
Bonus : For Developper ease, CustomList includes a debugging mode : find the "/*****DEBUGGING MODE*****/" tag in the calculation
if Debug = 1, return Error (formula or result is not correct), else, return "?"
-----------------------------------------

it is not a recursive function
For data processing, customized lists.

-----------------------------------------

For all native functions with a number
Left(), Middle(), GetValue(), GetNthRecord(), GetLayoutObjectAttribute () ...
or any range of numbers, dates, times and timestamps ...

CustomList can be a base for many other customs functions

----------------------------------------- *
CustomList ( 1 ; 1000 ; "Thanks Ugo and Fabrice !" )
----------------------------------------- *

----------------------------------------- more examples
The Argument "Function" can be :
Examples : with [n] to define the numeric list

- "GetNthRecord ( Field ; [n] )"
- """Name =>"" & GetNthRecord ( FieldName ; [n] ) & "" FirstName =>"" & GetNthRecord ( FirstFieldName ; [n] )""
- "[n] & ""."" & MiddleValues ( Field ; [n] ; 1 )"
- "GetAsDate ( Date ) + [n]"
- "GetLayoutObjectAttribute ( ""ObjectName"" ; ""Content"" ; 1 ; [n] )"
- "Let ( [ Value = GetValue ( MyList ; [n] ) ] ; Case ( PatternCount ( Value ; "X" ) ; Value ))"
- "Let ([ $$Var[[n]] = GetNthRecord ( Field ; [n] ) ] ; """" )" // To charge N variable
- "GetRepetition ( RepetitingField ; [n] )" [...]

All native functions including Let (), any Custom Function, including CustomList() itself can be used in a formula into the "Function" argument

------------------------- Under the hood :
Basically, CustomList() does two things :
1/ Transform your formula in litteral chain :

CustomList ( 1; 4; "GetNthRecord ( Field ; [n])")
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.

Agnès Barouh
Thanks to ® Ugo Di Luca

Comments

		Bruce Robertson Aug 20, 2010
It appears that Custom List does not work on FileMaker GO. I tried a simple script that collects 5,000 record values and it works on FileMaker Pro but not on FileMaker GO.

		Agnes Barouh, Tic Tac Aug 24, 2010
Yes. thanks ! is the current limit of FileMaker GO internal Evaluate function FileMaker GO Evaluate () is limited to 400 instead of 1700 for FileMaker. I corrected the code in CustomList() it's ok for FileMaker And FileMaker Go. Carreful, as the difference between FileMakerGo End - Start = 150000 max. ( instead of 500000 if it's FileMaker )

		Farooq, Hyderabad/India Nov 6, 2012
Have you tried this in IWP? It is not working for more than 1000 records. Do you know what is the reason? Regards, Farooq.

		AgnÃ¨s Barouh, Paris Nov 6, 2012
Hello, After GO and Server, I was watting for someone tell me the result for IWP It's ok now, I changed the calc for IWP (the same thing, limit of Evaluate Function.) -> difference between FileMaker IWP End - Start = 250000 max Thanks ï»¿ï»¿ï»¿ï»¿

		Erich, Rostock/Germany Sep 4, 2015
The function uses the iterator n: = n + 1 to calculate the term from one line to the next. I have some applications where I would like to specify a different iterator function. For example, n: = 2 * n + 1 or n: = n ^ 2 or more generally: n: = f (n). My question: Is it a difficult problem to provide a version of Custom List, which you can specify its own iterator function?

		Agnes Barouh, Tic Tac Sep 4, 2015
Hi, You can made your own iteration, for example, you can calculate the [n] : CustomList ( 1 ; 6 ; "[n] * 2 - 2" ) give 0 2 4 6 8 10 CustomList ( 1 ; 6 ; "Let ([ nb = [n] * 2 ; P = nb ^ 2 ]; P )" ) or CustomList ( 1 ; 10 ; "Get ( CurrentDate ) + ( [n] * 7 )" ) for liste 10 fryday ( today ) I hope this answers your question AgnÃ¨s

		Erich, Rostock/Germany Sep 4, 2015
That's exactly what I need. Thank you

		Erich, Rostock/Germany Sep 4, 2015
Sorry, there is still a problem. The method you described only works with an explicit iterator function. It doesn't work, if the iterator is a recursive function of the form n:=f(n-1), it means that the [n] in the current calculation only depends of the value used in the previous one. Is there any solution? The question is: Can the current calculation access the value used in the previous calculation? Regards,

		Agnes Barouh, Tic Tac Sep 4, 2015
Hi, for pass the [n] in the other calc, let or cf or CustomList in CustomList, you can need one $variable you can also not use the [n], instead, you use $var CustomList ( 1 ; 10 ; " Let ([ $Var = $Var ^ 2 ; Cf = CalcRecursive ( Reset/continu $var) .... ]; """" )" ) I do not know your result, and calc depends on the results you want may be if it's more simple if you send me by eMail, your calc and the result that you want

		Eric, SJSU Sep 13, 2022
What would be the function to explicitly break lines in the list? This doesn't work: CustomList ( 25000; 25050; "[n] & ""Â¶"" & Get(CurrentDate ) + [n]")

		Agnes Barouh, Tic Tac Sep 13, 2022
Hi Eric, you can write : CustomList ( 25000; 25050; "[n] & \Â¶ & Get(CurrentDate ) + [n]") or Let ([ $rc = Â¶ ]; CustomList ( 25000; 25050; "[n] & $rc & Get(CurrentDate ) + [n]") ) CustomList is not dead ;) Thanks to used it ! AgnÃ¨s

		Eric, SJSU Nov 22, 2023
Thanks. How do you create a function with related table fields? The first row works, but the following do not. Example: GetNthRecord(EMPLID;[n]) & Char(9) & GetNthRecord(app_NAMES::FIRST_NAME;[n]) = 997560885 Eric 997564726 ? 997562407 ?

		Eric, SJSU Nov 22, 2023
Thanks. How do you create a function with related table fields? The first row works, but the following do not. Example: GetNthRecord(EMPLID;[n]) & Char(9) & GetNthRecord(app_NAMES::FIRST_NAME;[n]) = 997560885 Eric 997564726 ? 997562407 ?

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

CustomList ( Start ; End ; Function )

Comments

Support this website.

Support this website.