#2: Using permutated addition.

Doing what?, you may already be asking. That's "permutated" as in "permutation." This is one of those things most people don't know is possible in the formula language. Most of the others who do know about it still don't have any clue why they'd use it.

Well, fear not; I'm here to tell you.

But before I tell you how to use it, let me spend a few paragraphs on what it is. You know what addition is. In Notes there are really two kinds of addition: addition with number (1 + 1 = 2), and addition with strings ("A" + "B" = "AB"). You already knew that.

There's also addition of sets of numbers or strings. List (or array) addition, in other words:

(1 : 2 : 3) + (1 : 2 : 3) = 2 : 4 : 6

("A" : "B" : "C") + ("A" : "B" : "C") = "AA" : "BB" : "CC"

You probably knew that. Permutated addition takes this to the next step by giving you all possible combinations of the numbers or strings being added together. Permutated addition uses the *+ sign instead of just the +. With permutation, you get all the combinations of answers:

(1 : 2 : 3) *+ (1 : 2 : 3) = 2 : 3 : 4 : 3 : 4 : 5 : 4 : 5 : 6

Start with the first number in the first list, 1, then add it to each of the second list's number (1, 2 and 3), then do the same with the 2 and the 3 from the first list. It's a little easier to see what comes from where if you do it with two different strings:

L1 := "A" : "B" : "C";
L2 := "D" : "E" : "F";
L1 *+ L2 = "AD" : "AE" : "AF" : "BD" : "BE" : "BF" : "CD" : "CE" : "CF"

OK, so how do you use it?

Did you ever need a list of sequential numbers? Let's say you need a list of the numbers 1 through 99. How do you get it in the formula language? You could simply type the numbers into a variable:

numList := 1 : 2 : 3 : 4 : 5 : 6 : 7 : 8 : 9 : 10 : 11 : 12 : 13 : 14 : 15 : 16 : 17 : 18 : 19 : 20 : 21 : 22 : 23 : 24 : 25 : 26 : 27 : 28 : 29 : 30 : 31 : 32 : 33 : 34 : 35 : 36 : 37 : 38 : 39 : 40 : 41 : 42 : 43 : 44 : 45 : 46 : 47 : 48 : 49 : 50 : 51 : 52 : 53 : 54 : 55 : 56 : 57 : 58 : 59 : 60 : 61 : 62 : 63 : 64 : 65 : 66 : 67 : 68 : 69 : 70 : 71 : 72 : 73 : 74 : 75 : 76 : 77 : 78 : 79 : 80 : 81 : 82 : 83 : 84 : 85 : 86 : 87 : 88 : 89 : 90 : 91 : 92 : 93 : 94 : 95 : 96 : 97 : 98 : 99;

That's exactly what you're trying to get but man it seems like a lot of work. If you're using ND6 you could write an @Formula loop:

@For(n := 1; n <= 99; n := n + 1;
    numList := @If(numList = ""; n; numList : n);
    @Return(numList);
)

I'll admit that works pretty well, but (a) it doesn't work in R5, and (b) I have to go look up the syntax to remember how to do it. You can do the same thing with permutated addition. The only catch here is you need to do it with strings and then convert to numbers if that's what you really need to have:

L1 := "0" : "1" : "2" : "3" : "4" : "5" : "6" : "7" : "8" : "9";
@TextToNumber(@Subset(L1 *+ L1; -99))

The "@Subset" was added because L1 *+ L1 actually returns the numbers zero through 99 and I wanted to remove the leading zero element. If you need the numbers 1 through 999, just do it one more time:

L1 := "0" : "1" : "2" : "3" : "4" : "5" : "6" : "7" : "8" : "9";
@TextToNumber(@Subset(L1 *+ L1 *+ L1; -999))

You say you need the numbers as text and padded with leading zeros (001, 002, 003...999) so they sort properly in a dialog box? That's even easier: Don't do the @TextToNumber conversion:

L1 := @Explode("0~1~2~3~4~5~6~7~8~9"; "~");
@Subset(L1 *+ L1 *+ L1; -999)

(Yes, I could just as easily have typed all ten digits as a colon-separated list like the other examples. This way is quicker...and cooler.)

Yeah, but where can I use this?

Have you ever built a dynamic table using multiple-value fields so you could add extra lines without needing extra fields (You haven't? You need to come to one of my presentations!)? Maybe you want a leading column with line numbers. Let's say one of the columns is really a field called "ProductName." Your number column could be this:

L1 := @Explode("0~1~2~3~4~5~6~7~8~9"; "~");
@TextToNumber(@Subset(@Subset(L1 *+ L1; -99); @Elements(ProductName))

The extra @Subset removes all the extra numbers so you don't get 99 line numbers for three items.

Another place we use this all the time, also associated with dynamic tables, is figuring out which item the user wants to edit. Let's say there are some duplications in the ProductNames field:

"Apples" : "Bananas" : "Carrots" : "Apples" : "Bananas"

You want to pop up a dialog to ask the user which one she wants to edit:

@Prompt([OKCancelList]; "Choose item"; "Which item would you like to edit?"; ""; ProductName);

The problem is, if she picks, say, "Apples," you won't know whether that means to edit line 1 or line 4. With a little string parsing and our new best friend permutation, though, you can know:

L1 := @Explode("0~1~2~3~4~5~6~7~8~9"; "~");
LeadingNums := @Subset(@Subset(L1 *+ L1; -99); @Elements(ProductName)) + ". ";
Choice := @Prompt([OKCancelList]; "Choose item"; "Which item would you like to edit?"; ""; LeadingNums + ProductName);
EditLineNum := @TextToNumber(@Left(Choice; "."));

A little convoluted? Maybe, but let's see you do it with less code. Permutation is surprisingly handy to know about. You watch: Sometime in the next week or so you'll be working on something or, better yet, looking over a co-worker's shoulder and say, "hey, all you need for that is a little permutated addition."

After you whip off a couple of lines of code to the amazement of everyone else on your team, smile a thankful smile at Bill playing golf up there with Saint Peter.

1. Scott Good05/07/2005 11:57:54 AM
Homepage: http://www.scottgood.com

A late addition: Reading through Damien Katz's blog this morning (http://damienkatz.net/2004/11/formula-languages-dirty-secret.html) I discovered that the methods I'm showing here are not only cool, they're the fast way to go.

One more reason to learn about permutated addition (and list management)!

Scott