Reg Exp
Web Design
Notes Client
Looping to perform lookups
One of the things the ND6 formula language now allows is looping. This has many, many uses and here we only talk about one. Let's say you want to perform a series of lookups. In R5, you could pass in an array to a @DbLookup function and each element of the array could be looked up to get a value. This works just fine in most situations. However, if one of the keys in your array wasn't found, the rest of the values are not looked up. Let's give an example.

Let's say you have an array of 5 elements:

Values := "Cancel" : "Completed" : "Copy" : "junk" : "Delete"

The first three values are going to be found in the lookup, as will the 5th one, but the 4th one ("junk") will not be found. (Yes, this is a poor example if all this is known beforehand, but just assume for a minute that the 5 values come from somewhere else and they could vary). Take a look at the R5 version doing the lookup:

Lookup := @DbLookup(""; ""; "By Title"; Values; 2);

We do not do any error checking here, but that actually isn't the point. Since the 4th element in the variable Values isn't found in the view, the result will only have 3 elements. If all 5 elements from Values were found in the view, the result would have 5 elements. Once an error happens in the lookup, the rest of the elements in the source list are ignored and the lookup quits.

Using looping, we can continue processing all the elements in the list. Here is the same lookup performed through looping in ND6:

Result := "";
@For(i := 1; i <= @Elements(Values); i := i+1;
   Lookup := @DbLookup(""; ""; "By Title"; Values[i]; 2);
   Result := Result : @Text(Lookup)

At this point, the variable Result has an array of 5 elements. It is guaranteed to have the same number of elements as the Values variable. The 4th element is empty because the lookup failed for that entry.

If you want to simply ignore errors, then you could @Trim the value of Result when all done.

A few things to note: First, we used the quick subset - Values[i] returns a single value. It is identical to @Subset(@Subset(Values; i); -1). But it's a lot quicker to write.

The last thing to point out is inside the for loop. You can read the Designer help for information on the syntax of @For itself, but this is more of a design idea than anything. We put the opening condition, continuing condition, and increment all on the same line. Then subsequent lines (the body executed during the loop) are indented one tab. After the last body line, the parenthesis to close the for loop is placed on its own line. We feel this makes things a little easier to follow from a support standpoint. Looking at the code, it's fairly easy (when you know this setup) to see that 2 lines are executed during the for loop - the lookup and then appending the result of the lookup to the temporary variable.