CustomListOLDVersion ( Start ; End ; Function )

[old version] Custom Lists - For all native functions with a number

Average rating: 4.7 (32 votes) Log in to vote

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

Sample input:

CustomList ( 2; 5; "[n] & "". "" & GetNthRecord ( Field ; [n])")

Sample output:

2. FieldRecord2
3. FieldRecord3
4. FieldRecord4
5. FieldRecord5

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

// Text lists from X to 18700 - if your data are sorted, for GetNthRecord and GetLayoutObjectAttribute don't overcome End - Start < +/-1500 , otherwise it takes a long time  to obtain the result
// Numeric lists from X to 20000
/* Examples : with [n] to define the numeric list
CustomList (  Start ; End ; "GetNthRecord ( Field ; [n] )" ) 
CustomList (  Start ; End ; """Name =>"" & GetNthRecord ( FieldName ; [n] ) & "" FirstName =>"" & GetNthRecord ( FirstFieldName ; [n] )"" )
CustomList (  Start ; End ; "[n] & ""."" & MiddleValues ( Field ; [n] ; 1 )") 
CustomList (  Start ; End ; "GetAsDate ( Date ) + [n]" )
CustomList (  Start ; End ; "GetLayoutObjectAttribute ( ""ObjectName"" ; ""Content"" ; 1 ; [n] )" )
CustomList (  Start ; End ; "Let ( [ Value = GetValue ( MyList ; [n] ) ] ; Case ( PatternCount ( Value ; "X" ) ; Value ))" )
CustomList (  Start ; End ; "Let ([ $$Var[[n]] = GetNthRecord ( Field ; [n] ) ] ; """" ) " ) // To charge N variable

------------------------------------------------------------------------------*/
/* Agnès - Juin 2007 - Thanks. - To report bugs : barouh.agnes@wanadoo.fr
CustomList ( Start ; End ; Function ) - _V.2.2
------------------------------------------------------------------------------*/
//Case : Control the not-found values
Case (
IsEmpty ( Start )  or IsEmpty ( End ) or End < 1 or Start < 1 ; "" ;

Let ( [

Start = GetAsNumber ( Start ) ;
End = GetAsNumber ( End ) ;
End = Case ( End > 20000 or End < Start ; "Error" ; End ) ;

//List : Prepare ------------------------ Creation dof the Numeric List -------------------
           NumCal =  Substitute ( ( 10 ^ 33 ) -1; "9"; "100100100" ) ;
           List1_99 = Substitute ( "_00" & NumToJText ( Left ( NumCal * NumCal ; 295 ) ; 1 ; 0 ) ; ["," ; "_"] ; ["." ; "_"] ) ;
           $List100_199 = "_100"  & Substitute (  List1_99 ; ["_0" ; "_1"] ) ;
           EndMax =   LeftWords ( List1_99 & $List100_199 & "_200" ; Int ( ( End - 100 ) / 100 ) + 1 ) ;
           FinalList =  MiddleWords ( List1_99 & 
                             Evaluate ( Substitute ( "(\"_" & EndMax ; 
                             [ "_"; ( "|Substitute( $List100_199 ; \"_1\"; \"_" ) ] ; [ "|"; "\") & "] )  & "\")" ) 
                             ; Start ; End - Start + 1 ) ;
//--------------------------------- End of Creation of the Numeric List -----------------

//FunctionR : Replace the [n] - preparation to the evaluation
          FunctionR = Substitute ( Function ; "[n]" ; "Num" ) ;

//Execute : Creation of the chains to be evaluated
          Execute =  Case ( not IsEmpty ( Function ) ; 
                            Case ( Left ( Substitute ( Lower ( Function ) ; ["definir" ; "Let" ] ; [" " ; ""] ; ["¶" ; ""]) ; 5 ) = "Let([" ; 
                            Substitute ( "Let([Num=" & FinalList & "|" ;  [ "_";  "|¶Let([Num="] ; [ "|";
                            ";" & Replace ( FunctionR ; 1 ; Position ( FunctionR ; "[" ; 1 ; 1 ) ; "" ) & "&\"#^#|#^#\"&"] ) ;
                            Substitute ( "Let([Num=" & FinalList & "|" ;  [ "_";  "|¶Let([Num="] ; [ "|";
                            "];" & FunctionR & ")&\"#^#|#^#\"&"] ) ) ) ;

//X - Determine the number of "blocks" to avoid the limit of the "Evaluate" Function
          X = ( End - Start + 1 ) /1700  ;

//Result : if "Function" is empty, Result will be the numeric list
           Result  =   Substitute ( "#^#" & Case ( IsEmpty ( Function ) ; Substitute ( "_" & FinalList ; ["_00" ; "¶"] ; [ "_0" ; "¶"] ; [ "_" ; "¶"] ) ;                            
                            Evaluate  ( MiddleValues ( Execute ; 1 ; 1700 )   & "\"\"")  & 
                            Case ( X  > 1 ; Evaluate ( MiddleValues ( Execute; 1701; 1700 ) & "\"\"" ) ; "" ) &
                            Case ( X  > 2 ; Evaluate ( MiddleValues ( Execute; 3401; 1700 ) & "\"\"" ) ; "" ) &
                            Case ( X  > 3 ; Evaluate ( MiddleValues ( Execute; 5101; 1700 ) & "\"\"" ) ; "" ) &
                            Case ( X  > 4 ; Evaluate ( MiddleValues ( Execute; 6801; 1700 ) & "\"\"" ) ; "" ) &
                            Case ( X  > 5 ; Evaluate ( MiddleValues ( Execute; 8501; 1700 ) & "\"\"" ) ; "" ) &
                            Case ( X  > 6 ; Evaluate ( MiddleValues ( Execute; 10201; 1700 ) & "\"\"" ) ; "" ) &
                            Case ( X  > 7 ; Evaluate ( MiddleValues ( Execute; 11901; 1700 ) & "\"\"" ) ; "" ) &
                            Case ( X  > 8 ; Evaluate ( MiddleValues ( Execute; 13601; 1700 ) & "\"\"" ) ; "" ) &
                            Case ( X  > 9 ; Evaluate ( MiddleValues ( Execute; 15301; 1700 ) & "\"\"" ) ; "" ) &
                            Case ( X  > 10 ; Evaluate ( MiddleValues ( Execute; 17001;1700 ) & "\"\"" ) ;  "" )) & "#^#"       
                            ; [ "#^#|#^#"; "¶" ] ; [ "¶"; "¶#^#" ] ; [ "#^#¶"; "" ] ; [ "¶#^#"; "¶" ] ; [ "¶#^#" ; "" ] ; [ "#^#" ; "" ] )  

] ;
Case ( 
Result = Substitute ( ( 10 ^ Ceiling ( X ) ) - 1 ; 9 ; "?" ) or End =  "Error"  ;
Let ([
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 ) ) )
];
"[Error_CL]  | Return error : " & GetValue ( Error ; Case ( Pos = 0 ; 1 ; Pos ) ) & ¶ & TextStyleAdd ( "Your calculation ( for [n] = 1 ) : " ; Bold ) & Write
) ;
Result ))) & Let ( $List100_199 = "" ; "" )

CustomList[Old] => [ you have now an Update version: Start and End Unlimited : http://www.briandunning.com/cf/868]

V.1.2 => Changed Tag "^" by "#^#"
April 04 - 2008. => V.2.3
=> Change alert error "Check Calculation"
=> result isEmpty if end or start is empty or < 1

it is not a recursive function (the recursive version is underneeth )
Carreful : Limited => End - Start < 18700 End < 18700
*this CustomList () version allow nesting of CustomList ( ) functions *

Thanks a lot to Ugo Di Luca and Fabrice Nordmann

For all native functions with a number
Left, Middle, GetValue, GetNthRecord, GetLayoutObjectAttribute (...)
or with interval CurrentDate, CurrentTime (...) or other.

CustomList can be a base for many other functions

-----------------------------------------
Nota : more calculation required in "Function" is complex, more evaluation is long

----------------------------------------- *
CustomList ( 1 ; 1000 ; "Thank you very much 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] )" [...]

----------------------------------------- Recursive Version
CustomList can also be recursive :
the calcul is simple but I prefer CustomList (), not recursive version is faster than CustomListRec ()

//------------------------------------------------------------------------------
/* Agnès - Juin 2007 - Thanks. - To report bugs : barouh.agnes@wanadoo.fr */
// Text lists from X to 10000

CustomListRec ( Start ; End ; Function )
Let ( [
Start = GetAsNumber ( Start ) ;
End = GetAsNumber ( End ) ;
FonctionPrepa = Substitute ( Function; "[n]"; Start );
Result = Evaluate ( FonctionPrepa )];
If ( Start < End ;
Case ( IsEmpty ( Function ) ; Start & ¶ ;
PatternCount ( Result; "?" ) > PatternCount ( Function; "?" )
or IsEmpty ( Result ) ; "" ;
Result & ¶ ) & CustomListRec ( Start + 1; End; Function ) ; Result ) )

----------------------------------------- more détails

CustomList will write your calculation like this (before evaluating it - Argument "Execute") :
CustomList ( 1; get(foundCount); "GetNthRecord ( Field ; [n])") :

Let ([ Num = 1 ] ; GetNthRecord ( Field ; Num )) & ¶ &
Let ([ Num = 2 ] ; GetNthRecord ( Field ; Num )) & ¶ &
Let ([ Num = 3 ] ; GetNthRecord ( Field ; Num )) [...]

or, when you write with "Let(["
CustomList ( 1; get(foundCount);
"Let ([ Value = GetNthRecord ( Field ; [n]) ] ; Case ( Value > 50 ; Value))") :

Let ([ Num = 1 ; Value = GetNthRecord ( Field ; Num) ] ; Case ( Value > 50 ; Value)) & ¶ &
Let ([ Num = 2 ; Value = GetNthRecord ( Field ; Num) ] ; Case ( Value > 50 ; Value)) & ¶ &
Let ([ Num = 3 ; Value = GetNthRecord ( Field ; Num) ] ; Case ( Value > 50 ; Value)) [...]

Comments

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

CustomListOLDVersion ( Start ; End ; Function )

Comments

Support this website.

Support this website.