Using Lists In FormulasMany times developers don't realize the power of lists and formula language. Many @-functions will operate on all entries in a list, which saves coding lines if it's done right. For example, if you want an array of all numbers from 1 to 999, it just takes a few lines:
ZeroToNine := "0" : "1" : "2" : "3" : "4" : "5" : "6" : "7" : "8" : "9";
ZeroTo99 := ZeroToNine *+ ZeroToNine;
ZeroTo999 := ZeroTo99 *+ ZeroToNine;
OneTo999 := @TextToNumber(@Subset(ZeroTo999; -999));
The "*+" operator is an "all possible combinations" concatenation operator. Since there are 10 elements in each list (each side of the operator), then there will be 100 entries in the resulting list. The first entry is "00", the second is "01", etc. The order is built by taking the first entry in the first list and appending each of the entries in the second list in order. Then the second entry from the first list is used and the process is repeated.
After doing that a couple of times, we have an array of 1000 entries ranging from "000" through "999". Eliminating "000" from the list (keeping the last 999 entries), and then converting the text to numbers, gives us a list of numbers from 1 to 999. This code is useful for a computed for display field that shows numbered entries - like an edit history field or something.
The @Replace function is a great function to have in your tool bag when working with lists. You can use it to only keep certain values in a list, or use it to remove certain values in a list. For example, let's say we have a field called "Numbers" that contains a list of the first ten numbers spelled out:
"One" : "Two" : "Three" : "Four" : "Five" : "Six" : "Seven" : "Eight" : "Nine" : "Ten"
Now, if you want to remove all the entries that start with "S" from the "Numbers" field, that just takes one line of code:
@Trim(@Replace(Numbers; "S" + @Right(Numbers; "S"); ""))
Let's work from the inside-out. @Right works on every element of a list. So @Right(Numbers; "S") will give us a list of the text after the first capital S in each entry. Since most of the entries don't have "S", those will be null. Only the entries "Six" and "Seven" will not be null - those will be "ix" and "even" (the "S" is not retained). Since the "S" is not retained, we add it back on to the front. The + operator is a pair-wise operator (each entry in the first list is append with the corresponding entry in the second list). Since the first list has fewer entries (1) than the second list (10), the last entry ("S") in the first list is duplicated enough times to have the same number of elements as the second list.
So, "S" + @Right(Numbers; "S") gives us a list of 10 entries - five entries of "S", then "Six", then "Seven", then three more entries of "S". (Remember that "S" + null = "S"). Now the @Replace function takes a source list, a "from" list, and a "to" list. Each entry in the "from" list is evaluated to see if it's contained anywhere in the source list. If it's found, the value in the source list is replaced with the corresponding element in the"to" list. Again, if the number of entries in "from" list and "to" list are not equal, the shorter list has the last entry duplicated.
The first entry in the replace source list is "S". That entry isn't found in Numbers, so nothing happens. The same process is repeated four more times. Then "Six" is evaluated. That does exist in Numbers, so it's replaced with the corresponding value in the "to" list. The "to" list had its one value (null) duplicated 10 times, so "Six" in Numbers is replaced with null. Similarly, "Seven" is replaced with null. When the @Replace is finished, we have:
"One" : "Two" : "Three" : "Four" : "Five" : "" : "" : "Eight" : "Nine" : "Ten"
The @Trim function removes the null values and gives us a list of the entries that do not start with the letter "S".
What if we wanted the opposite - the entries that do start with "S"? Well, you just have to go about it the right way. Sometimes it's easier to eliminate what you want. So we take that list above that eliminated what we wanted. Let's call that Step1. If we replace the entries in Numbers that match Step1 with nulls, then we'll end up with the entries that do begin with "S". So, here are the two steps:
Step1 := @Trim(@Replace(Numbers; "S" + @Right(Numbers; "S"); ""));
@Trim(@Replace(Numbers; Step1; ""));