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

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, 200718 yr by Guest

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

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.

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.

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.

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.

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.

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

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, 200718 yr by Guest

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, 200718 yr by Guest
few corrections to theory

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.

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

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.

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?

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

No that made a lot more sense, thanks Shawn.

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; "" )))

        )

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.

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.

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.

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

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, 200718 yr by Guest

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

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, 200718 yr by Guest

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

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 :)

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.

    

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

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

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.

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.

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

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

Thanks Ugo, I'm sorry to disappoint you but the only answer the engineers can publicly provide is via a new release of the product - no one I know has enough power to be allowed to promise new features. :)

It's a tough one. What you suggest is mighty convenient, but slightly inconsistent. Logically, I would expect all ¶ to simply pass through, with all the processing done in-between them. It's too bad there's no way of knowing where in the list you are, because then I would say leave the separator entirely to the user. As it is, your way is probably the best compromise - and you could say it follows the logic of List(), which also skips empty values.

Oops. Another problem with the idea of automatic separators: what if you wanted to combine values, e.g. return each pair of consecutive values as a single value?

Yeah, maybe its not worth it to add this - we could just stick with letting the user do whatever they want with the newlines themselves.

It's a bit of a hassle that way, but its the most flexible, I suppose.

I was just thinking that if you have the other function (I think Counter() would be the best name for it), there's very little you couldn't do - even with a single iterator. So to solve Genx' problem:

Let ( [

v = ;

end = ValueCount ( v )

] ;

ForEach (

i = Counter ( 1 ; end ) ;

GetValue ( v ; i ) & Case ( i < end ; ¶ )

)

)

The counter is really the key here, because for anything a bit more clever you'd want i to be an index, rather than an actual value. Checking the previous/next value, for example.

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

Oh... well... a function that only works in auto-enter calcs and conditional formatting, dispalying an error message in every other case... that's a premiere !

Why can't I use it in a script, a custom function, or a Replace fied content... ? these are where it would be really useful.

Why can't I set a variable to Self + 1 ?

For the rest, you perfectly understood my meanning. So yes, ForEach() would be great, but needs a Repeat() little sister.

Before custom list, I used a recursive function that might be comparable to ForEach, which I put a link to here only to help others understand what we're talking about, if needed... There is also a Matt Petrowsky video article .

http://www.bh-a.com/downloads_1.1.html#DL04

Of course it is recursive, slow and limited to 10,000 values, but it saved me very often.

As ValuePosition is concerned, I use a simple calculation (but true, it's part of my "template", so I wouldn't consider working without it

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

Why can't I set a variable to Self + 1 ?

Yeah, that too.

As ValuePosition is concerned, I use a simple calculation (but true, it's part of my "template", so I wouldn't consider working without it

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

That function you pointed to has some problems:

1. The start parameter operates in a text context - which is rather useless in a value list context. You could simply hardcode it to 1.

2. It's not thought out or written very well. If you're OK with giving up on the start parameter, you could write simply:

ValueCount ( Left ( listOfValues ; Position ( ¶ & listOfValues & ¶ ; ¶ & item & ¶ ; 1 ; occurrence ) ) )

But if you want to include a functioning startValue parameter, you need to do some extra work:

Let ( [

checkedList = RightValues ( listOfValues ; ValueCount (  listOfValues ) - startValue + 1 ) ;

pos = Position ( ¶ & checkedList ; ¶ & item & ¶ ; 1 ; occurrence ) ;

shortList = Left ( listOfValues ; pos ) 

] ;

Case ( pos ; ValueCount ( shortList ) + startValue - 1 ; 0 )

)

And that still isn't right, because it won't take negative occurrence. I believe you're better off going recursive for that. So it's a far cry from being as simple as Shawn would let us believe.

Now, how about a native SortValues() function?

Edited November 25, 200718 yr by Guest

True, I was just thinking of the basic Position functionality, if you wanted the start / occurrence thing also, it would be rather a bit more work. Still, I'd already capitulated that ValuePosition() was a good candidate for a builtin.

Now, how about a native SortValues() function?

Yes, that would be another nice one, the issue here I think is what datatype do you want to sort with, since a list of values no longer has type information for its component values.

Symbolic constants could be added, with a default of Text to allow this to be specified.

    SortValues( "12¶1¶2¶27¶23¶", Ascending, Number ) -> "1¶2¶12¶23¶27¶"

    SortValues( "12¶1¶2¶27¶23¶", Ascending, Text ) -> "1¶12¶2¶23¶27"

That would make a nice addition to the value-processing functions, anything else come to mind?

Well, CustomList allows sorting, de-duping, filtering a list with another one with several criteria (contains, starts with, ends with, does not contain... with a case sensitive option), give all values of current foundset...

I also use it verymuch as an audit tool all field values of current record (including repetitions), list of fields used as keys...

the issue here I think is what datatype do you want to sort with

Exactly. The data type parameter is a must.

anything else come to mind?

LOL, a lot of things, but not necessarily related to this...

I just keep thinking about another version that somehow combines the two functions together.

Let's say it has the syntax of:

For ( var ; counterStart ; counterEnd ; { increment ; } expression )

As you see, it doesn't take any "real" input. All it does is evaluate the expression, where you can use var as the current counter value. Any actual values to be processed would need to be referenced in the expression, e.g. GetValue ( yourList ; var ) or Left ( text ; i ).

Now, you may ask why bother with such a simple thing, when you can easily construct this with a custom function. But the thing is that nesting won't work with such a custom function, where the expression is passed as a parameter (or at least I haven't been able to make it work). But with this function, the var would be carried forward, as we discussed above, so that:

For ( i ; 1 ; 3 ; For ( j ; 1 ; 2 ; i & j & ¶ ) ) = "11¶12¶21¶22¶31¶32¶"

Granted, it's a little more work for the developer this way. For example, when processing a list, you'd have to pre-calculate the value count, rather than just let the list run out by itself. But at the price it's also more flexible (not limited to value lists), and I can't find anything that you cannot do with this one function that would be possible using ForEach() and Counter() together.

Hello !

Well, CustomList allows sorting, de-duping, filtering a list with another one with several criteria (contains, starts with, ends with, does not contain... with a case sensitive option), give all values of current foundset...

Fabrice is a Fan : ( me too )

But, no, CustomList doesn't do all that, these are calculations that we put inside who do it.

I try to sort data with this but I am to block because of "max", calculation is not yet ok.

For filter values, it's ok with FilterList ( ListA ; Attribut ; ListB ; CaseSensitive) (use CustomList )

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

you can write for exemple

FilterList ( List ( Table::MyField ) ; "Contains" ; Left ( FieldX ; 1 ) & ¶ & Middle ( FieldY ; 3 ; 3 ) ; "" )

it isn't too distant from Range() or ForEach().

we can indeed put 2 lists together or more, to make the processing which one wants

but :

both lists were iterated over at once, so for example:

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

would result in: "a2¶b3¶"

If you put List ( MyField1 ) for i and List ( MyField2 ) for j and if MyField2 contains values with ¶, the result will be false

With CustomList, I must write calculation like this to concatenate my 2 lists (or more) :

Let (

$Rc = ¶ ;

CustomList ( Start ; End ;

"Substitute ( GetNthRecord ( MyField1 ; [n] ) & "" "" & GetNthRecord ( MyField2 ; [n] ) ; $Rc ; "" "" ) " ))

CustomList has the advantage of treating the values or words or part of text one by one and not by group like list.

But is limited beacause I don't want to push the numerical list beyond 20000 nor lines of packages of evaluate beyond 18700, the result takes too a long time then to wait

please, I do not have the claim to have made the things which it is necessary, just to have tried to find answers to my problems of processing of the lists and if that can advance a little bit, I take part with pleasure.

my unhappy English does not allow me all to understand, sorry if I am out of the subject.

Thanks

Agnès

Two ideas here :

Shawn, am I wrong or once the calculation engine would be able to do such a thing, it wouldn't be so hard to upgrade the List() function to a version 2, that would allow to nest an expression

List ( this & that )

for this= "a¶b¶¶d" and that = "1¶¶3¶4", it would return "a1¶b¶3¶d4".

The reason why I'm thinking of this is that it would seem consistant that empty values are omitted as we are used to, by List function... so you could return them in ForEach.

I don't understand why List function behaves this way : empty values are interesting, and so easy to get rid of, but that's the way it works now. So obviously a new version of List should remain compatible...

Second idea : well, we have now FilterValues, that returns values found in two lists, but what about the opposit : find values that are not in the second list ?

I do this with CustomList, as I said before, but it could be a native function per se.

I don't understand why List function behaves this way : empty values are interesting, and so easy to get rid of, but that's the way it works now.

List() was written to be an aggregator, the same as Avg(), Sum(), Count(), etc. Thus, it pretty much works like they do, which is to drop empties. It wouldn't have had to, but that seemed like the right thing to do at the time.

Second idea : well, we have now FilterValues, that returns values found in two lists, but what about the opposite : find values that are not in the second list ?

Yes, sort of a list subtraction kinda thing, I've needed that a couple times myself.

Second idea well, we have now FilterValues, that returns values found in two lists, but what about the opposit find values that are not in the second list ?

I think with the additon of ValuePosition(), this becomes easy:

SubtractValues( values, minusValues ) =

ForEach( v = values; If( ValuePosition(minusValues; v) <> 0; v & ¶))

So, I think ValuePosition() is the thing to discuss next:

  ValuePosition( text; searchValue [; startIndex [; occurrence]] )

this would be like Position, but with optional arugments that default to one.

Also, I think it would be nice for the searchValue argument to acutally be multiple values, and ValuePosition() would only return the index if all of the values were present and in the same order:

  ValuePosition( "a¶b¶c"; "b¶c¶" ) returns 2

and:

  ValuePosition( "a¶c¶b¶d"; "b¶c" ) returns 0 (not found)

Any comments are appreciated.

Hi Shawn,

If you had ValuePosition(), you ALMOST wouldn't need the Counter() function, because - to generalize your example - you could use the processed values to count themselves:

ForEach ( v = listOfValues; Let ( i = ValuePosition ( listOfValues ; v ) ; ) )

I am saying 'almost', because with a real counter, the ForEach() function could process anything, not just a list of values - a text string, for example. Also, at the risk of repeating what I said earlier, I really think it would be more powerful to have the INDEX (i.e. the output of the counter) passed forward to a nested function, rather than an actual value.

Regarding ValuePosition()

Allowing searchValue to have multiple values (actually a block of values) would be a nice feature - although I don't see much use of it. That is, I'd rather have one without it sooner than with it later.