Reg Exp
Web Design
Notes Client
FailSilent Keyword In Lookups
We posted a tip a while back talking about looping in formula language to perform lookups. At that time, we weren't aware of the great new feature added to the @DbLookup function in Notes 6. The @DbLookup function now has an optional parameter at the end. This parameter takes one or more keywords in a list. I'll describe these by showing an example.

Let's say you have a view that translates letters into your "secret code". The letter A becomes a Z, B becomes a Y, and so on. (Yes, an easy code to break, but that's not the point). So, let's say you've been doing a lookup using the value from the "Source" field as a key:

@DbLookup(""; ""; "vwSecretCode"; Source; 2)

If the field has a value of "A", then the lookup results in "Z". But if the field has a value of "1", then this will cause an error because you're only translating letters. So you have to trap for this error. In 6 there are two ways to trap for this error. The first is with the @IfError function. The @IfError function is now obsolete; you should use the second method instead. The second is with the new keywords available to lookups:

@DbLookup(""; ""; "vwSecretCode"; Source; 2; [FailSilent])

The statement above will return an empty string if the field has a value of "1".

This keyword is great if you're looking up multiple values. Let's say your field has a list of three values: "A" : "1" : "B". If you do the normal error trapping, you'll end up with one value as a result. This is because "A" will be looked up and the result kept, then "1" will fail and no other lookups will be performed. But with the [FailSilent] keyword, the lookups will continue. So "A" would be looked up, "1" would fail, and "B" would be looked up.

Guess it pays to look not only at the newly introduced functions when a new release comes out, but look also at the enhanced functions. With keywords you can also perform a partial match lookup in formula language, and you can return the document unique ID (thus eliminating the @Text(@DocumentUniqueID) column that is used quite often).