CustomList_BottomUp ( Start ; End ; Function )

customized lists - Reverse list - Not recursive

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

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

Sample input:

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

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

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

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

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

Sample output:

returns "Reverse¶you" if MyList is
"With¶CustomList_BottomUp¶you¶reverse¶your¶list"

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

85000. 11/04/2241
84999. 10/04/2241
84998. 09/04/2241
84997. 08/04/2241[...]

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

FieldRecord5
FieldRecord4
FieldRecord3
FieldRecord2

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

/*             Merci beaucoup à Fabrice Nordmann pour ses tests, son aide, ses corrections
                       et sa pugnacité, saupoudrée de ténacité à faire naitre cette fonction
===========================================================

//   Author: Agnès Barouh - barouh.agnes@wanadoo.fr

//   CustomList_BottomUp ( Start ; End ; Function )  v_1.1
// [please, do not used "CLNum" in your calculation with Let() ]

//   Objectives and examples  :

- Build a descending list [for an Ascending List, used CustomList ()]

- 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_BottomUp ( Get ( FoundCount ) ; 1 ; "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_BottomUp ( 5 ; 1 ; "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_BottomUp allows for any filtering or parsing process based on any condition you may need.
ex : CustomList_BottomUp ( 100 ; 10 ; "Let ( [ Value = GetValue ( MyList ; [n] ) ] ; Case ( PatternCount ( Value ; "X" ) ; Value ))" )
will parse any value containing a "X" in  the 'MyList' chain, in between the 100th and the 10th values.

See the 'Under the Hood' part at the end of the function to fully understand the process of this function

/* New Version */ July 2008
-----------------------------------
CustomList_BottomUp is based on a totally new algorithm, and is now volontarily bridled to a maximum range of 500,000 values,

CustomList_BottomUp () is faster and still is NOT recursive.

For Developper ease, the CustomList_BottomUp() 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 = Start - End + 1;

                         /*Check for a range higher than 500,000 values. CustomList_BottomUp () is volontarily restrained to this limit.*/

                         End =   Case ( Diff > 500000 or End > Start or IsEmpty ( Start ) or IsEmpty ( End ); "Error"; End );
                         $null = "\"\"";

                         /* CustomList_BottomUp () has its own recursion model. As CustomList_BottomUp () may be involved into the "function" argument, 
                         each CustomList_BottomUp() 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_BottomUp() 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_BottomUp() will return a descending 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 remainder 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_BottomUp () 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_BottomUp() does two things :
1/ Transform your formula in a litteral chain :

CustomList_BottomUp ( 4; 1; "GetNthRecord ( Field ; [n])")
therefore becomes
"Let ([ CLNum = 4 ] ; GetNthRecord ( Field ; CLNum )) & ¶ &
Let ([ CLNum = 3 ] ; GetNthRecord ( Field ; CLNum )) & ¶ &
Let ([ CLNum = 2 ] ; GetNthRecord ( Field ; CLNum )) & ¶ &
Let ([ CLNum = 1 ] ; 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 !

Merci pour tous les commentaires et la notice  ® Ugo Di Luca        */

-----------------------------------------***
Edit July-24 2008 :
v1.1 > Replace "Num" by "CLNum"
[please, do not used "CLNum" in your calculation with Let() ]
-----------------------------------------

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

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

- Build a descending list [for an Ascending List, used CustomList ()] - http://www.briandunning.com/cf/868

- 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_BottomUp ( Get ( FoundCount ) ; 1 ; "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_BottomUp ( 5 ; 1 ; "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_BottomUp allows for any filtering or parsing process based on any condition you may need.
ex : CustomList_BottomUp ( 100 ; 10 ; "Let ( [ Value = GetValue ( MyList ; [n] ) ] ; Case ( PatternCount ( Value ; "X" ) ; Value ))" )
will parse any value containing a "X" in the 'MyList' chain, in between the 100th and the 10th values.

More : http://www.briandunning.com/cf/868

----------------------------------------- more examples

Let ([
Start = NumberStart ;
End = NumberEnd ;
Function = "[n] & "". "“ & GetNthRecord ( Table::Name ;[n] ) & "". "“ & GetNthRecord ( Table::LastName ; [n] )"
];
Case (
End ≥ Start ;
CustomList ( Start ; End ; Function ) ;
CustomList_BottomUp ( Start ; End ; Function )
)
)

Comments

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

CustomList_BottomUp ( Start ; End ; Function )

Comments

Support this website.

Support this website.