RFC - Repeat Function Idea

[The Shadow]

I'm thinking about submitting a feature request for a new function that could expand the power of the existing calc-engine, I'd appreciate any comments anyone has about the idea, and once its polished up via some discussion, I'll submit it to FileMaker as a feature request.

The concept is a function that allows for a simple iteration without having to make a recursive custom function. While there are custom functions that allow something like this, I believe a powerful built-in function could do it better (and of course, faster).

I'm envisioning something like:

     Repeat( variable = ; )

Where the variable provided would take on each of the values in the list, and for each value the expression is evaluated and all the results are concatenated into a single string.

So, for example:

     Repeat( i = Range(1,6); "X" )

would result in:

     XXXXXX

as it would simply concatenate the expression for each value of the variable i.

The variable can be leveraged inside the expression also:

     Repeat( i = Range(2,5); i & ¶ )

would result in:

     2¶3¶4¶5¶

The Range() function I use above would provide a List of numbers as newline seperated, so:

     Range(2,5) -> "2¶3¶4¶5"

This means I could have supplied my own list to Repeat on instead:

     Repeat( i = List( "abc"; 17; 444 ); i & ¶ )

would result in:

     abc¶17¶444¶

Like Let(), if the variable portion of the Repeat was a list, multiple variable definitions would be allowed, and Repeat would move over each variable in turn (a full cross product, like a nested For loop in other languages)

     Repeat( [ i = List(1,0); j = List(4,5) ]; i & "," & j & ¶ )

would result in:

     1,4¶1,5¶0,4¶0,5¶

I believe this would be a powerful addition to the programming capabilities of the calc-engine. What do you think? See any areas for improvement?

[The Shadow]

The Range() function should allow the expansion of arbitrary ranges, if no increment is supplied it defaults to 1.

     Range( , [, ] )

So, numbers could be supplied and expanded into a range:

     Range( 2, 11, 2 ) -> "2¶4¶6¶8¶10"

A backward range would result in an empty list:

     Range( 11, 2 ) -> ""

if the increment is negative, the backword range is allowed:

     Range( 11, 5, -1 ) -> "11¶10¶9¶8¶7¶6¶5"

It would also work on arbitrary numbers, it just stops when the next value is > the end point:

     Range( 2.35; 6.47; 1.1 ) -> "2.35¶3.45¶4.55¶5.65"

[fabriceN]

Hi Shawn,

indeed, it would be a great improvment... if there was no limitation.

The CustomList cf by Agnès Barouh does exactly this, in a non recusive way. It's rather fast, but there is a limitation to the number of items in the list (18500, I think))

http://www.briandunning.com/cf/747

http://www.filemakermagazine.com/videos/customlist-custom-function.html

As to your first example, you can still use

http://www.briandunning.com/cf/776

(not recursive)

Edited November 22, 2007 by Guest

[Søren Dyhr]

But Fabrice it's not just the headroom which is important here, if you compare these two algorithms:

http://sixfriedrice.com/wp/deleting-duplicate-records-in-filemaker/

With this:

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000669

Will you learn that GetNth( isn't particular fast and the same goes with Evaluate( where this have been said at devcon:

Q. How does the Evaluate() function perform compared to other functions…if is slower, why?

A. Have to parse it…can be more expensive, but take some parsing time…great to be able to use it but it may be more expensive. (CC)

Said here: http://fmcollective.com/2007/08/09/devcon-2007-closing-session/#comment-559

I would welcome more machine near crunching! But when it comes to it are we usually using repeating calc'fields for such schemes via http://www.filemaker.com/help/FunctionsRef-215.html ... we usually get where we want, but with these two functions would we save a lot of the ingenuity for other purposes!

--sd

[comment]

It's a great idea, Shawn. I would suggest making it this way:

1. ForEach() function

ForEach ( listOfItems ; expression )

Use "i" in the expression to indicate current item.

Examples:

ForEach ( "a¶b¶c" ; i & "-" ) = "a-b-c-"

ForEach ( "1¶2¶3" ; i + 10 & ¶ ) = "11¶12¶13"

ForEach ( "1¶2¶3" ; "a" ) = "aaa"

2. Loop() function



Loop ( counter ; increment ; exitIf ; expression )

Use "i" in the expressions to indicate current value of counter.



Examples:

Loop ( 1 ; 1 ; i = 5 ; i ) = "12345"

Loop ( 100 ; -5 ; i < 90 ; i & " ; " ) = "100 ; 95 ; 90 ; "

Loop ( 1 ; 1 ; i = 4 ; "a" ) = "aaa"

Loop ( 1 ; 1 ; i > ValueCount ( "a¶b¶c" ) ; GetValue ( "a¶b¶c" ; i ) & "-" ) = "a-b-c-"

The last example does exactly what ForEach() does.

Having optional multiple counters would be great, but it might be too much for FMI to swallow at once? Meanwhile, I believe one could get by nesting one Loop() inside another.

[The Shadow]

Thanks to everyone for their comments, I appreciate it.

Comment, I like the name ForEach() better, that's a good idea, but I don't want to have an 'implied' variable name. If the variable name is implied, that makes nesting these things impossible, and in the "inner" ForEach how would you access the "outer" ForEach()'s i variable?

So, I see it more like:

  ForEach( i = "1¶2¶3"; i + 10 & ¶ )

That way, I can nest like:

  ForEach( i = "1¶2"; ForEach( j = "5¶6"; i + j & ¶ ) )

resulting in the same result as:

  ForEach( [ i = "1¶2"; j = "5¶6" ]; i + j & ¶ )

The reason I'm sticking with the "list" format, is this makes the ForEach() function have a signature exactly like the Let() function, which means it's special parsing can be reused, making this function extra-cheap to implement.

I think it might also be nice to be able to access the currently accumulated result, but I don't see a clean way that could be implemented without a magic variable name.

Your Loop function looks a lot like a for-loop from C (and other languages), they always look like:

   for( initialize; continueIf; increment ) { body }

so, for example:

   for ( i = 1; i <= 5; i = i+1 ) { whatever... }

So, if it were to be considered, I would suggest using continueIf rather than exitIf.

Also, since it serves the same purpose as ForEach, I question the wisdom of requesting *two* looping constructs, I'd rather we could come to agreement on a single one.

[The Shadow]

Yes, the CustomList() function was the one I was thinking of when I said there were some that were something like this.

I don't see Range() needing any limit outside the 2 Gig limit for a text buffer, that would limit any iteration to the 100's of millions range, I believe... (about 200 million would be my guess, each number taking about 9 digits plus a newline).

The nice thing about the ForEach() method is that FileMaker can be assured "forward progress" is being made, unlike Loop, there is no way you could get ForEach() to loop forever.

[comment]

I see what you mean about accessing the "outer" variable. So the syntax could be:

ForEach ( var = listOfItems ; calculation )

and the variable would be "live" for as long as the function runs, and available within the 'calculation' parameter - just like in Let().

Regarding the Loop() function:

I can't help it if there are various kind of loops: http://en.wikipedia.org/wiki/For_loop

There are things that you just cannot do with ForEach() - such as a simple countup to 100.

So while ForEach() may be simpler to implement (both for FMI and the user), if I had to choose between them, I would choose Loop() because it can do what ForEach() does - but not the other way round.

The reason why I suggested 'exitIf' instead of 'doWhile' is to match the logic of a scripted loop.

[The Shadow]

Yes, I that's the syntax I was thinking of, just like a Let().

The count up problem was why I also tossed in the Range() function - this extends the ForEach() power to allow moving over some arbitrary range you don't have a list of yet:

  Foreach( i = Range(1,100); i & ¶ )

And actually, if all you wanted was a list, Range() would be enough by itself. This function could have helped out the recent Bible search thread to help change 22-32 into a list.

The list of dates between two dates as a list would be:

  ForEach( d = Range( startDate, endDate ); GetAsDate(d) & ¶ )

I believe the ForEach() is actually the more common scenerio - iterating over items you already have. Also, the use of Loop() to implement ForEach() as you demonstrated is not efficient, and is not how ForEach() would be implemented internally (there are value iterators available internally).

I see the power of Loop(), I just think ForEach() is easier for most users to understand, and less importantly (but not irrelevent) easier for FMI to implement.

With the addition of Range() function, I believe ForEach() has *almost* as much power as the Loop() - the arbitrary calculation for the exit condition adds extra power, but this is also a problem for FileMaker, as how can we guarantee the exit condition will eventually return true (that's the halting problem, which is intractable.)

That's a big part of the reason custom functions have a recursion limit, as there is no way to examine the code to determine it will always stop - ForEach() avoids this, FileMaker can tell exactly how many loops are going to be performed before it starts working just by looking at the ForEach's variables.

[comment]

Well, I don't disagree with that - it's just that ForEach() is still rather limited compared to a true for-loop. But I understand your reservations, and having ForEach() would be a powerful addition to what we have now - even without Range(). With it, it would be almost as good.

BTW, I think a better name for Range() might be Enumerate().

there are value iterators available internally

It's too bad they are not exposed to the user. A lot of custom functions would be much easier to write, if there were a function like Get (CalculationRepetitionNumber).

[Ugo DI LUCA]

Great idea Shawn,

CustomList ( ) is only limited to the number of nested evaluation, am I wrong ? And we're not speaking about recursions as this function is a RF. At least, that's how I interpreted the sudden evaluation stop when I did some starting tests with it before Agnès brought it to this powerful final function.

How this wouldn't have any impact to the Range ( ) or ForEach ( ) future native functions ?

Whatever the answer, I really appreciate that you're still around with us, really !

Ugo

Edited November 22, 2007 by Guest

[Genx]

I wouldn't mind the Foreach function, or Michael's "loop"... though with the loop given the exit condition, i would probably give it a more standard name like While( init ; continuance condition ; increment ; expression )... or maybe LoopWhile... and while the Range / Enumerate function could be useful, if we had the While / loop statement it would be easy to replicate with a CF while the same can't be said in the reverse manner.

e.g.

While( i=10 ; i > 0 ; i=i-2 ; i & "X " );

"10X 8X 6X 4X 2X"

While( i=0 ; i < 10 ; i=i+1 ; "X" );

"XXXXXXXXXX"

The inited variable should have a scope of existing within any child While statements and itself.

Edited November 23, 2007 by Guest
few corrections to theory

[The Shadow]

Thanks Ugo.

I haven't looked into how CustomList() cf works enough to know what is limiting it. If it's using nested evaulate's that could well be it, that would use a lot of resources.

New native functions like Range() and ForEach() don't have this limitation. They are written in C/C++ and thus can be straight iteration (no recursion or evaluate needed) as well as leveraging non-functional concepts, like appending to the end of an existing string to perform these new operations efficiently.

It's not that these new functions would be cheap, but I would think it would be easy to be 50 times faster than anything that could be written it the calc-engine without a lot of effort. That's just the way it goes with interpreted verus compiled languages in general.

I hope that answered the question.

[Genx]

Surely C/C++ would have a while, do while, or for equiv?

[The Shadow]

c/c++ have 3 looping constructs:

  for( ; ; )

    {

}

  while( )

    {

}

  do {

} while ( )

    which ensures that body is executed at least once.

But C/C++ are also imperative languages, while FM calculations are functional (like Lisp), which just means there aren't any variables you can change during the course of evaluation - all values are returned back.

[Genx]

there aren't any variables you can change during the course of evaluation - all values are returned back.

Sorry, I'm a little lost, could you rephrase a bit?

[The Shadow]

In a language like C, you declare a variable and give it a value:

  int j = 2;

Now you can run other statements that change the value:

  j = j * 3;

  j++;

In Lisp / FileMaker calculations, variables are not "usually" like this, you assign them values once and use them. [This has actually changed in more recent versions via $-global variables that can be changed within a calculation.]

Languages that don't have side-effects are called functional, as each method is like a function, if you call it with the same arguments it will produce the same result.

I'm sure this explanation isn't that much better, you might want to try Wikipedia for a better attempt: Functional Programming

[Genx]

No that made a lot more sense, thanks Shawn.

[The Shadow]

I just wanted to point out some of the various uses that something like ForEach() could be put to, providing power that would currently require custom functions to accomplish:

Filtering:

    Filter a list of numbers to only those >= 10:

        ForEach( n = numList; If ( GetAsNumber(n) >= 10; n & ¶ ))

    Filter a list down to only those values starting with P:

        ForEach( v = valueList; If ( Left(v) = "P"; v & ¶ ))

Mapping:

    Add 2 to all numbers in a list:

        ForEach( n = "2¶7¶99¶"; (GetAsNumber(n) + 2) & ¶ ) -> "4¶9¶102¶"

    Extract first letter from a list of values:

        ForEach( v = "apple¶pear¶watermelon¶bannana¶"; Left(v,1)) -> "apwb"

Aggregate Operations:

    By leveraging a script local variable, we can keep track of additonal data while looping, in this case, find the maximum value in a list:

        Let( $maxValue = "";

            ForEach( v = values; If ( IsEmpty($maxValue) or v > $maxValue; Let( $maxValue = v; "" )))

        )

[comment]

Well, don't waste it on us - pitch it to FMI already. You're just making us drool here.

In my best scenario, they turn it down because they're replacing the entire calc engine.

[The Shadow]

This thread *is* part of the pitch, I've emailed a link to it to developers I know at FMI, though they may not look at it until after the break.

What I'm trying to accomplish here is to have a sort of "open" design process, to ensure what's being proposed is solid, and basically well-decided enough that its ready to be implemented.

I'm doing this rather than writing this up by myself because I think there is much valuable input to be had here. You guys live and breathe this stuff.

----

So, now I'm thinking about what happens with empty lists:

I think, for instance, this should return "", ie, don't iterate when there are no values:

    ForEach( v = ""; "Hello, " & v )

Also, I think now that I thought about it more, there is no purpose to allowing multiple value iterators in one ForEach statement the way I was thinking above - that case can just be handled (more clearly) by nesting the ForEach() statements.

So, is there any other reason to allow the multiple iterators? What if when multiple iterators were supplied, both lists were iterated over at once, so for example:

    ForEach( [ i = "a¶b¶"; j = "2¶3¶" ]; i & j & ¶ )

would result in: "a2¶b3¶"

But then what would make sense to do when the two lists had different numbers of elements? How about set those variables to empty for the remainder of the iterations?

So, for example:

    ForEach( [ i = "a"; j = "2¶3¶4¶" ]; i & j & ¶ )

would result in: "a2¶3¶4¶"

So, in a ForEach with multiple value-iterators, the one with the most values would determine how many iterations were performed, and the iterators would all move forward in-step until its list had no more values.

That change seems to add some more possibilites, I'm imagining a new list could be constructed from two source lists, where the larger item from one list or the other could be in the result list. I can't think of a realistic use for that off the top of my head, but it seems like a useful thing.

[Genx]

I can't think of a realistic use for that off the top of my head, but it seems like a useful thing.

Yeh i'm not really sure about the need for multiple iterators either - in other languages i'm guessing we would simply access the values in the second or third value sets / arrays by using the value of a single iterator using it as the key...

But i'm sure that someone could think of some creative use for it so, if it was to exist, I'd agree with the suggested behavior of continuing the loop through until the largest value set had been processed.

I think, for instance, this should return "", ie, don't iterate when there are no values:

ForEach( v = ""; "Hello, " & v )

Also makes sense.

One concern is your delimiting of the final value with a pilcrow - in truth it would seem to imply that there was an additional value to follow if you're working with a delimited list... though it could just be me.

[comment]

I think allowing multiple iterators the way you now describe (iterate over all lists at once, with the largest list determining the number of iterations) would be quite useful. It would allow you to construct something like an associative array.

Thus you could, for example, filter out values of j, based on criteria applied to i, such as if i = "taxable", return j (the price of an item).

Admittedly, the same functionality could be achieved by a single iterator - provided one had a function to generate a counter:

Let ( [

tax = List ( LineItems::IsTaxable ) ;

price = List ( LineItems::Price ) ;

counter = Enumerate ( 1 ; ValueCount ( price ) )

] ;

ForEach ( i = counter ; Case ( GetValue ( tax ; i ) ; GetValue ( price ; i ) & ¶ ) )

)

Genx has a good point regarding the separator. Perhaps the default output of the function should be also a value list. If the largest input list has a trailing ¶, so should the result. You can always substitute out the ¶ if you don't want them.

I too agree that in any case, ForEach ( i = "" ; anything ) should return empty.

Note also that no matter how this is implemented, it raises value lists to a new level of functionality - and some additional functions would be appropriate to take advantage of this, notably ValueSum() and ValuePosition().

[fabriceN]

Sorry, I'm lost,

but...

unless I misunderstood something, which is more than probable. ForEach would not be able to "build" its own lists, as Repeat (first Shawn's message), Range, or CustomList do / would do. It would process one or more lists, passed in parameters.

As I said before, I love this idea, being a daily user of CustomList. The problem with CustomList, from a usability point of view, is that it does two things at a time : building the list, and processing it (filtering, sorting...)

I would love to see ForEach() become a native function, but it should come together with the equivalent of the "building list" part of CustomList. Could be called "Range", indeed.

Again, I might be just unable to see how to do it with ForEach... sorry then :

Edited November 24, 2007 by Guest

[The Shadow]

One concern is your delimiting of the final value with a pilcrow - in truth it would seem to imply that there was an additional value to follow if you're working with a delimited list... though it could just be me.

That's just me - FileMaker currently supports it either way:

    ValueCount( "a¶b¶c¶" ) -> 3

    ValueCount( "a¶b¶c" ) -> 3

Currently, the "value" methods: LeftValues(), RightValues() etc always add the final newline:

    LeftValues( "a¶b¶c", 3 ) -> "a¶b¶c¶"

This was done so that working with a list of empty values can be self-consistent:

    ValueCount( "" ) -> 0

    ValueCount( "¶" ) -> 1

    ValueCount( "¶¶" ) -> 2

[Genx]

Fabrice you're right in the sense that you can't use Foreach to create lists - its designed to run through each individual item in an existing list and produce repeated output specific to each item in that list.

I still think a true loop would be useful in this situation rather than something that could only build lists.

i.e. you could easily build an efficient range function with a for loop:

Range(min;max)

For( i=min ; i <= max ; i=i+1 ; i & ¶ );

Range(1;10) should return: 1¶2¶3¶4¶5¶6¶7¶8¶9¶10¶ unless i've done something stupid.

Another unrelated thing that would be nice is the ability to increment or decrement values using ++ or --.

Edited November 24, 2007 by Guest

[The Shadow]

Genx has a good point regarding the separator. Perhaps the default output of the function should be also a value list. If the largest input list has a trailing ¶, so should the result. You can always substitute out the ¶ if you don't want them.

Well, so far, the ForEach() examples have all been adding their own newlines:

    ForEach( v = ; v & ¶ )

an alternative would be that ForEach handles the newlines itself, which it would append *except* when the value was empty - thus, if you actually wanted an empty value, you would need to return ¶ yourself.

So, the above would just be written:

    ForEach( v = ; v )

and all the newlines would be handled by ForEach, though you could also add your own (returning "a¶b") which would result in multiple values being added per iteration. Make sense?

Note also that no matter how this is implemented, it raises value lists to a new level of functionality - and some additional functions would be appropriate to take advantage of this, notably ValueSum() and ValuePosition().

I assume ValuePosition would find the index of a value in a list of values? Yes, I agree that would be a nice to have, but it can be done now also using Position() to find it and ValueCount() on what's before it to find the index.

ValueSum() seems a bit weak to me for a builtin - this functionality could be created by using ForEach() as I showed in an earlier post (finding the maximum).

[fabriceN]

Genx, I too would love to see a Loop function, but Shawn explained that ForEach was more affordable for FMI developers at this time.

I prefer a good function, well implemented, than a fantastic feature remaining a dream.

The worse being the good function badly implemented, namely Self :)

[The Shadow]

I still think a true loop would be useful in this situation rather than something that could only build lists.

Another unrelated thing that would be nice is the ability to increment or decrement values using ++ or --.

I don't disagree these would be powerful concepts, but they don't fix very well in the mostly functional way the calc-engine currently operates, which would mean a lot of additional work for someone at FMI, which would quite likely mean this feature doesn't get done.

The For() as you describe still has the problem of being potentially infinite, which is an issue. Also, there are two "special" positions in the calculation (arguments 1 and 3) where assignments are occurring, which is a headache in numerous ways.

If we were going to talk about power, I'd rather be able to inline Perl code to produce a cross-platform calc function, ie: a custom function who's implementation was written in perl.

    

[The Shadow]

What's wrong with Self? I hadn't noticed any complaints on the forums.

[Genx]

Right no loop...

In that case I guess range would be useful, and come to think of it we could technically simulate a for loop with a combination of Range and ForEach... that only just hit me for some reason.

I don't really get the self complaint...

[The Shadow]

The problem with CustomList, from a usability point of view, is that it does two things at a time building the list, and processing it (filtering, sorting...)

Yes, as you suggest, the feature would consist of these two functions:

    Range() [or Enumerate()] - for building lists

    ForEach() - for iterating over existing lists.

So that the two operations are split apart. You can always combine them again via:

    Foreach( v = Range(...); ... )

Or am I misunderstanding you?

[Ugo DI LUCA]

Right on target Shawn,

From the beginning, mainly on the French forums where this CustomList ( ) thing started, I advocated for a true separation of this function in a dual system, BuildMyList ( ) and ManageMyList ( ).

Where finally ManageMyList ( ) can do either Filtering, Comparison, Assemblies, IntraEvaluations, etc within a natural loop.

Range ( ) and ForEach ( ) therefore seems the natural way to go, and I'm now just waiting to see what FMI engineers would answer.

Thanks, again.

[comment]

an alternative would be that ForEach handles the newlines itself, which it would append *except* when the value was empty

I don't know about this exception - it seems inconsistent. I believe an empty value should count, just as it counts in ValueCount(). I would expect:

ForEach ( v = "" ; "a" ) = ""

ForEach ( v = "¶" ; "a" ) = "a¶"

ForEach ( v = "1¶" ; "a" ) = "a¶"

ForEach ( v = "1" ; "a" ) = "a"

I assume ValuePosition would find the index of a value in a list of values? Yes, I agree that would be a nice to have, but it can be done now also using Position() to find it and ValueCount() on what's before it to find the index.

Yes, it can be done, but it's rather awkward. You actually need to find the position of "¶value¶" in "¶list¶", then cut the list by position, then count.

The argument that it can be done doesn't hold anyway - even ForEach() can be done, esp. if you hardcode the calculation part. The question is how many resources you have to throw at it. All the basic text functions, such as Left(), Right(), Middle(), Length() and Filter() have their value equivalents, and GetValue() simply begs for a counterpart.

What's wrong with Self?

Nothing's wrong with it, except that it's rather useless, because it returns the field's contents instead of its name. So you still have to hardcode:

Case ( Get(ActiveFieldName) = "ThisFieldName" ; ... )

and remember to modify the formula if the fieldname is changed.

[The Shadow]

I don't know about this exception - it seems inconsistent. I believe an empty value should count, just as it counts in ValueCount().

I wasn't clear - I meant the computed value of empty wouldn't count, so lists could be filtered down, ie:

    ForEach( v = "a¶b¶a¶b¶3"; if (v = "a"; ""; v ))

I would like this result to be: "b¶b¶3"

as returning an empty value in the ForEach() expression would be considered to have no effect. If the empties were desired you would need to do:

    ForEach( v = "a¶b¶a¶b¶3"; if (v="a"; ¶; v))

which would result in: "¶b¶¶b¶3"

So, I guess a trivial ForEach would automatically filter out empty values:

    ForEach( v = "¶a¶¶¶b¶¶c"; v )

would return: "a¶b¶c"

So, you would still get an iteration occurring for empty values in the source list, but if you returned empty nothing would be placed into the result list.

------

I agree that implementing a ValuePosition() is rather tricky, I didn't mean to imply that it wasn't a good idea as a builtin, I think that makes sense.

Nothing's wrong with it, except that it's rather useless, because it returns the field's contents instead of its name.

Oh. Yes, I've heard that complaint, but had forgotten. :)