**Function stats**

FileMaker 8.0 +

06 December 2008

07 November 2012

No

** Function overview**

**Prototype**

**Parameters **

**Description**

Tags: Parsing List Interval Found Set Filter Extract Custom List

-----------------------------------------***

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

FileMaker GO Evaluate () is limited to 400 instead of 1700 for FileMaker.

Carreful : Limited

with Filemaker = End - Start ≤ 500000

with FilemakerGo = End - Start ≤ 150000

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

- 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.

----------------------------------------- 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.

**Examples**

**Sample input**

1/---------------

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

2/---------------

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

3/---------------

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

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

2/---------------

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

3/---------------

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

**Sample output**

1/---------------

returns "Not¶Recursive" if MyList is

"CustomList¶Is¶Not¶Recursive¶But¶Now¶Unlimited"

2/---------------

25000. 12/12/2076

25001. 13/12/2076

25002. 14/12/2076

25003. 15/12/2076[...]

3/---------------

FieldRecord2

FieldRecord3

FieldRecord4

FieldRecord5

returns "Not¶Recursive" if MyList is

"CustomList¶Is¶Not¶Recursive¶But¶Now¶Unlimited"

2/---------------

25000. 12/12/2076

25001. 13/12/2076

25002. 14/12/2076

25003. 15/12/2076[...]

3/---------------

FieldRecord2

FieldRecord3

FieldRecord4

FieldRecord5

**Function code**

/* 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

// [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

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

/* 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*/

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 !

*/

// ===================================

/*

This function is published on FileMaker Custom Functions

to check for updates and provide feedback and bug reports

please visit http://www.fmfunctions.com/fid/118

Prototype: CustomList( Start; End; Function )

Function Author: Agnès (http://www.fmfunctions.com/mid/46)

Last updated: 07 November 2012

Version: 2

*/

// ===================================

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

// [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

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

/* 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*/

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 !

*/

// ===================================

/*

This function is published on FileMaker Custom Functions

to check for updates and provide feedback and bug reports

please visit http://www.fmfunctions.com/fid/118

Prototype: CustomList( Start; End; Function )

Function Author: Agnès (http://www.fmfunctions.com/mid/46)

Last updated: 07 November 2012

Version: 2

*/

// ===================================

## Comments

FMForums09 December 2008

Genx10 December 2008

It's not really a huge issue because it's not likely to happen in real life - the only reason I mention it is because all other item's with invalid entries seem to return a nice error result, but this one just locks FM up.

Agnès10 December 2008

And thanks for your comments ! I can put "blusch", smiley with the red cheeks !

CustomList was difficult to explain but I believe that it is indeed useful thank you especially to used it and tested !

Alex,

I put calculation in data viewer and in a field unstored and field auto-enter :

CustomList ( 1 ; 5 ; "GetAsDate ( StartingDate ) + [n]" )

and here, I don't have crash,

I have just a message [for field, with format number, container or text]

[Error_CL] | Return error : 102 | Field is missing

Calculation ( for [n] = 1 ) : GetAsDate ( StartingDate ) + 1

or result = ? [for field with other format ]

test with 8, 8.5 and 9.03 Mac Tiger

Perhaps that only the 9.01 or win ? but, it is not normal and I do not see similar here

no crashes FM for me (with this formula ;) )

(Edited by Agnès on 10/12/08 )

Genx10 December 2008

(Edited by Genx on 10/12/08 )

Genx10 December 2008

FMForums18 July 2009

Agnès20 July 2009

no, it is not yet ok, it is too too long to calculate, I work on it from time to time but I have not found the right calculation. one day .... ( 500 values : 25 secondes, SortArray ( ) : 2 secondes .... I have work ! )

(Edited by Agnès on 22/09/15 )

Darren Lunn29 August 2009

I have been trying to build two custom functions for date projection (forwards & backwards) from a start/end date. I built the first function below which works perfect, but the second function returns a whacky error which looks incorrect. The only real difference is that I reversed the direction of the parsing i.e. -[n] as opposed to the normal +[n]. The really frustrating thing is that when I build the same calculation in a table field it works.. but not as a custom function..

FUNCTION 1: date.projected.end( startDate ; numDays ; KeepWeekend ) // This one works perfect

Let([

error.handler = Case( IsEmpty( startDate ) or IsEmpty( numDays ) or numDays ≤ 0 ; True ; False ) ;

//a long list of dates including weekends

$datelist.with.weekends = CustomList ( 1 ; numDays*10 ; "GetAsDate ( startDate-1 ) + [n]" ) ;

//a long list of dates excluding weekends

datelist.without.weekends = CustomList ( 1 ; numDays*10 ; "Let ( [ Value = GetValue ( $datelist.with.weekends ; [n] ) ] ; Case (

DayOfWeek( Value ) = 2 ; Value ;

DayOfWeek( Value ) = 3 ; Value ;

DayOfWeek( Value ) = 4 ; Value ;

DayOfWeek( Value ) = 5 ; Value ;

DayOfWeek( Value ) = 6 ; Value ))"

)//end customlist

];

Case(

error.handler = True ; "" ;

KeepWeekend = 1 ; GetValue( $datelist.with.weekends ; numDays ) ;

GetValue( datelist.without.weekends ; numDays )

)//end case

)//end let

FUNCTION 2: date.projected.start( endDate ; numDays ; KeepWeekend ) //this one returns an error - but why?

Let([

error.handler = Case( IsEmpty( endDate ) or IsEmpty( numDays ) or numDays ≤ 0 ; True ; False ) ;

//a long list of dates including weekends

$datelist.plus.weekends = CustomList ( 1 ; numDays*10 ; "GetAsDate ( endDate+1 ) - [n]" ) ;

//a long list of dates excluding weekends

$datelist.without.weekends = CustomList ( 1 ; numDays*10 ; "Let ( [ Value = GetValue ( $datelist.plus.weekends ; [n] ) ] ; Case (

DayOfWeek( Value ) = 2 ; Value ;

DayOfWeek( Value ) = 3 ; Value ;

DayOfWeek( Value ) = 4 ; Value ;

DayOfWeek( Value ) = 5 ; Value ;

DayOfWeek( Value ) = 6 ; Value ))"

)//end customlist

];

Case(

error.handler = True ; "" ;

KeepWeekend = 1 ; GetValue( $datelist.plus.weekends ; numDays ) ;

GetValue( $datelist.without.weekends ; numDays )

)//end case

)//end let

As I said when I build the second function above as a field calculation it works perfect??

Apologies if this is post is silly long but I couldn't think of another way to get the info in...

Agnès29 August 2009

If I look the result in "$datelist.plus.weekends" or in "$datelist.with.weekends" I obtain :

[Error_CL] | Return error : 102 | Field is missing

Calculation ( for [n] = 1 ) : GetAsDate ( endDate + 1 ) - 1

or

[Error_CL] | Return error : 102 | Field is missing

Calculation ( for [n] = 1 ) : GetAsDate ( startDate + 1 )+ 1

it's better if you write :

$endDate = endDate ;

(...)

CustomList ( 1 ; numDays*10 ; "GetAsDate ( $endDate-1 ) - [n]" ) [ the same for starDate ]

CustomList ( 1 ; numDays*10 ; "GetAsDate ( $starDate-1 ) + [n]" )

end or startDate with $ )

perhaps you don't have this error because you have a field name "endDate" or "startDate"

you said :

"when I build the same calculation in a table field it works.. but not as a custom function"

is not the same thing, if you have a field name startDate, customList take the field (not the cf'parameter) and the calc is ok

for this raison, when I make a calc, i pass only variable in customList or complet field name : table::fieldName

say me if it's ok or not, but -[n] or + [n] is not a problem

Thanks you

Agnès

(Edited by Agnès on 29/08/09 )

Darren Lunn30 August 2009

Thank you for the insight. I understand what's going now. I modified the cf and works perfectly now. I will bear this mind for future. Thank you very much.

.... in my opinion, CustomList() is the best function in FMWorld ... Brilliant^10^10

Jared01 December 2012

Let ( [ _number = 1 ] ; CustomList ( 1 ; 10 ; "_number * [n]" ) )

The error that comes back says that the field reference doesn't exist. Error 102. Is there a way around this? If so, it would be amazingly powerful. Thanks!

jared

Agnès14 March 2014

Yes, with:

Let ( [ $number = 1 //or $number = _FieldNumber

] ; CustomList ( 1 ; 10 ; "$number * [n]" ) )

it's powerfull ;)

Thanks

Agnès

(Edited by Agnès on 14/03/14 )