API
@-Formulas
JavaScript
LotusScript
Reg Exp
Web Design
Notes Client
XPages
 
Swapping Multi-Value Field Values
Let's say you have a field on a document that holds multiple values. And you want to swap two of the values. That seems like it should be pretty easy, but because @Subset triggers an error if the second parameter is zero, we have to check for lots of possibilities when doing the swapping. Here's some formula language that will prompt for the two values to be swapped, then preform the swap on a field called LastName.

First, we need to get the values from the user. Then we want to check the values (make sure they aren't swapping "-1" or something after the end of the list) and set up variables so we know that one is less than the other. Let's go over that code first:

FIELD LastName := LastName;
Inp1 := @TextToNumber(@Prompt([OkCancelEdit]; "Enter Number"; "Enter the Swap 1 Number"; ""));
Inp2 := @TextToNumber(@Prompt([OkCancelEdit]; "Enter Number"; "Enter the Swap 2 Number"; ""));
Swap1 := @If(Inp1 = Inp2; @Return(0); Inp1 <= 0 | Inp2 <= 0; @Return(0); Inp1 > @Elements(LastName) | Inp2 > @Elements(LastName); @Return(0); Inp1 < Inp2; Inp1; Inp2);
Swap2 := @If(Inp1 < Inp2; Inp2; Inp1);

First, prompt the users for the two values to swap. Then set the variables Swap1 and Swap2 so they are numbers (instead of the text that was entered) and Swap1 is always the lower number. The line to set Swap1 checks to make sure that both numbers are within the bounds of 1 to the total number of elements in our field LastName. It also checks to see if the values are equal. If so, there's nothing to swap.

Now, let's consider some of the possibilities for what values could be swapped. You could be swapping the first value in the list with the last value, the first value with the 2nd value, or the first value with some other value. You could be swapping something besides the first value with its neighbor. You also could be swapping something besides the first value with the last value. Finally, you could be swapping something besides the first value with something besides the last value. Let's take a look at these one at a time. We're going to set a temporary variable called NewLastName to hold the new value of the LastName field.

NewLastName := @If(@Elements(LastName) = 2;
    @Subset(LastName; -1) : @Subset(LastName; 1);

First, let's take care of the case where there's only 2 elements in the list. That means the 2nd should be 1st and the 1st should be 2nd. Just swap the two values. From this point on, we can make the assumption that there's at least 3 elements in the list.

Swap1 = 1 & Swap2 = @Elements(LastName);
    @Subset(LastName; -1) : @Subset(@Subset(LastName; Swap2-1); 2-Swap2) : @Subset(LastName; 1);

Here, we look at the case where we're swapping the first and last elements. So the new list should have the last element, then everything in the middle, then the first element. "Everything in the middle" is found by taking everything except the last element (Swap2-1) and then taking everything of that subset except the first element (2-Swap2).

Swap1 = 1 & Swap2 != @Elements(LastName) & Swap2-Swap1 != 1;
    @Subset(@Subset(LastName; Swap2); -1) : @Subset(@Subset(LastName; Swap2-1); 2-Swap2) : @Subset(LastName; 1) : @Subset(LastName; Swap2-@Elements(LastName));

Here, we look at the case where we're swapping the first element with something besides the 2nd element and the last element. Swapping with the last element was already handled, and swapping with the 2nd element will come next. For example, if there are 5 elements this case would handle swapping the first with the 3rd or 4th elements. In this case, we want the "Swap2" element first, then the elements between the first and element Swap2, then the 1st element, then all the elements after Swap2.

We've handled the cases where Swap1 was 1 and Swap2 was anywhere from 3 to "n" (the total number of elements). Here, we swap the first and second elements:

Swap1 = 1 & Swap2 = 2;
    @Subset(@Subset(LastName; 2); -1) : @Subset(LastName; 1) : @Subset(LastName; 2-@Elements(LastName));

The final list should have the 2nd element, then the 1st element, then the 3rd and beyond elements.

Next, let's take a look at the cases where Swap1 is not 1 (you're not swapping the first value in the list).

Swap1 != 1 & Swap2 = @Elements(LastName) & Swap2-Swap1 != 1;
    @Subset(LastName; Swap1-1) : @Subset(LastName; -1) : @Subset(@Subset(LastName; Swap2-1); Swap1-Swap2+1) : @Subset(@Subset(LastName; Swap1); -1);

This time, we're swapping something besides the 2nd to last element with the last element. (It also won't be the 1st element because that was handled earlier). Swapping the 2nd to last and last elements will be handled later. The new list will have everything before Swap1, followed by the last element, followed by everything between Swap1 and the last element (that is Swap2-Swap1-1 elements, but we want a negative number, so we do Swap1-Swap2+1), followed by element Swap1.

Swap2-Swap1 = 1 & Swap2 = @Elements(LastName);
    @Subset(LastName; Swap2-2) : @Subset(LastName; -1) : @Subset(@Subset(LastName; -2); 1);

Here, we swap the 2nd to last and last elements. The new list will have everything except the last 2 elements, followed by the last element, followed by the 2nd to last element.

Swap1 != 1 & Swap2 != @Elements(LastName) & Swap2-Swap1 != 1;
    @Subset(LastName; Swap1-1) : @Subset(@Subset(LastName; Swap2); -1) : @Subset(@Subset(LastName; Swap2-1); Swap1-Swap2+1) : @Subset(@Subset(LastName; Swap1); -1) : @Subset(LastName; Swap2-@Elements(LastName));

This is the most complex case. We're working with 2 elements in the middle (neither is the first nor the last) and they are not neighbors. Everything before Swap1 stays put. Next comes the element at position Swap2. Next are the elements between Swap1 and Swap2. There are Swap2-Swap1-1 elements between, but again we want a negative number. Next comes the element at position Swap1. Finally, all the elements after Swap2 stay put.

The final case to look at is the one where Swap1 and Swap2 are neighbors. If either one is the first or last element, those cases were handled above. So we know that both are somewhere in the middle and that both are next to each other.

Swap2-Swap1 = 1;
    @Subset(LastName; Swap1-1) : @Subset(@Subset(LastName; Swap2); -1) : @Subset(@Subset(LastName; Swap1); -1) : @Subset(LastName; Swap2-@Elements(LastName));

In this case, the elements before Swap1 stay put. Then comes the element at position Swap2 followed by the element at position Swap1. Then all the elements after Swap2 stay put.

We need to close out our @If statement and put the results back into the field called LastName. This will end our formula:

"");
@SetField("LastName"; NewLastName)

Can this be simplified?

Yes, it can. But I wanted you to understand all the different scenarios before just giving you a formula. If you take that most complex case, use it as a model, and make sure that you don't do any @Subset statements where the number of elements would be zero, then you can simplify the code quite a bit. Here's our new function:

FIELD LastName := LastName;
Inp1 := @TextToNumber(@Prompt([OkCancelEdit]; "Enter Number"; "Enter the Swap 1 Number"; ""));
Inp2 := @TextToNumber(@Prompt([OkCancelEdit]; "Enter Number"; "Enter the Swap 2 Number"; ""));
Swap1 := @If(Inp1 = Inp2; @Return(0); Inp1 <= 0 | Inp2 <= 0; @Return(0); Inp1 > @Elements(LastName) | Inp2 > @Elements(LastName); @Return(0); Inp1 < Inp2; Inp1; Inp2);
Swap2 := @If(Inp1 < Inp2; Inp2; Inp1);
NewLastName := @Trim(
@If(Swap1 = 1; ""; @Subset(LastName; Swap1-1)) : @Subset(@Subset(LastName; Swap2); -1) : @If(Swap2-Swap1 = 1; ""; @Subset(@Subset(LastName; Swap2-1); Swap1-Swap2+1)) : @Subset(@Subset(LastName; Swap1); -1) : @If(Swap2 = @Elements(LastName); ""; @Subset(LastName; Swap2-@Elements(LastName)))
);
@SetField("LastName"; NewLastName)

The variables Swap1 and Swap2 are created in the same way through user input and the same tests are made. The new code starts with setting the NewLastName variable. We are going to need to @Trim the result because some of the pieces are going to be blank and we want to make sure that no blanks get into the final list.

The first piece is everything before Swap1. If Swap1 is 1, then there's nothing before it so an empty string is used. The second piece is the element at position Swap2. The third piece is all the elements between Swap1 and Swap2. If the difference between Swap1 and Swap2 is 1, then there won't be any elements between. Otherwise, take all the elements up to the one before Swap2 and then take the right-most "Swap2-Swap1-1" elements from that shorter list. The fourth piece is the element at position Swap1. And the fifth and final piece is all the elements after Swap2. If Swap2 is the last element, there is nothing after it.

The right parentheses by itself closes out the @Trim function, and then the field is set again like before.

So, that's how you swap two elements of a formula language array. Note that if you're using Notes/Domino 6, you have some shortcuts available to you to eliminate some of the @Subset formulas.