Brian Dunning's FileMaker Custom Functions

ApplyToRange ( function ; start ; end ; step ; separator )

Speedily (and using limited recursion) apply an "inline" function across a range of up to one million numbers.

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

Debi Fuchs   Debi Fuchs
Aptworks Consulting
http://www.aptworks.com

Share on Facebook Share on Twitter

  Sample input:
ApplyToRange (
"[n]^2";
1;
100;
1;

)
  Sample output:
"1¶4¶9¶...¶10000"

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

CUSTOM FUNCTION: ApplyToRange ( function ; start ; end ; step ; separator )
© 2008 Debi Fuchs of Aptworks Consulting, debi@aptworks.com

Speedily (and using limited recursion) apply an "inline" function across a range of up to one million numbers.

EXAMPLES:

ApplyToRange ( "[n]"; 1; 1000; 1; " ")
// --> "1 2 3 ... 1000"

ApplyToRange ( "[n]"; 3; -3; -1; ";")
// --> "3;2;1;0;-1;-2;-3"

ApplyToRange ( "[n]^2"; 1; 4; 1; "¶")
// --> "1¶4¶9¶16"

ApplyToRange (
"[i]&\":\"&GetAsDate([n])";
GetAsNumber(Date(1;1;2008));
GetAsNumber(Date(12;31;2008));
7;
"; "
) // --> 1:1/1/2008; 2:1/8/2008; ...; 53:12/30/2008

ApplyToRange( "case(lg([n])=int(lg([n]));1)"; 1; 1024; 1; 0)
// --> "1101000100000001..."


NOTE: See "Apply" to apply a function to a single value. See "ApplyToList" to apply a function to the elements of a list rather that a range of numbers. For example, if you want to add 2 to every item in a list:

1) Don't do this (slower):
ApplyToRange( "GetValue(myList; [n])+2"; 1; ValueCount( myList ); 1; "¶")

2) Do this (faster):
ApplyToList( "[n]+2"; myList; "¶" )

CREDITS: Thanks to Agnès Barouh for the idea of the "[n]" style function call syntax, and to Ray Cologon for ideas about local variables within custom functions.

USAGE: ApplyToRange returns the results, delimited by "separator", of repeatedly evaluating the string "function", each time assigning to placeholder "[n]" one value in the range from "start" to "end", with a step between values as specified by the "step" parameter. For convenience in creating numbered lists, use placeholder "[i]" to represent the position of the element within the range, starting with an index of 1. Also, for compatibility with "Apply" and "ApplyToRange" the placeholder "[t]" is also provided, but there is no reason it should be needed in this function, as numbers and evaluated numbers are the same thing.

FYI: While their implementations are quite different, ApplyToRange is similar in functionality to the CustomList function by Agnès Barouh: Both functions apply a function to a range of numeric values. However, ApplyToRange is all about sets, whereas while CustomList is all about building a list, so they have different focus. For example, null elements are intentionally NOT filtered from the result set of ApplyToRange, as it represents the mapping of elements to individual elements, rather than to a list at large. (To cull null values from the result set of ApplyToRange, try the CullNulls CF.) Another notable difference is that ApplyToRange does recurse (unlike CustomList, which is brilliantly non-recursive), but only minimally (see below).

IMPLEMENTATION: ApplyToRange creates an "index list" of the values 0 through 1000, using it to create a string of 1000 Let statements, each representing one value in the range. After evaluating the result, it recurses in binary fashion (calling itself twice not once) on subsequent 1000-element batches of the range, such that the stack depth grows only logarithmically, not linearly. For example, when called on a range of one million values, ApplyToRange will be called 1000 times, but reach a stack depth of only 10 CF stack frames. This leaves room to use other custom functions (including ApplyToRange) within the "function" argument of ApplyToRange. ApplyToRange is maximized for speed. Suggestions for further optimization are welcome!

LAST MODIFIED: 13-AUG-2008 by Debi Fuchs of Aptworks Consulting

 

Comments

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.

Under construction. Email me your wish list for improvements.