Brian Dunning's FileMaker Custom Functions

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   Agnes Barouh - Show more from this author
Tic Tac
http://www.tictac.fr/CoinFileMaker/Page.html

Share on Facebook Share on Twitter

  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)

// 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   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   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   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   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   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   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   Erich, Rostock/Germany
Sep 4, 2015
That's exactly what I need.
Thank you
 
Erich   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   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   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   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   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   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 ?
 

Log in to post comments.

 

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

Support this website.

This library has been a free commmunity resource for FileMaker users and developers for 20 years. It receives no funding and has no advertisements. If it has helped you out, I'd really appreciate it if you could contribute whatever you think it's worth: