@DBLookup with multivalue field as key

HCL Domino Domino Forum
1

I have a computed field on a form that should list descriptions of codes held in another field on the same form. The scenario is as follows:

“Codes” field is a multivalue editable text field.

“Descriptions” is a multivalue computed text field.

The codes in the “Codes” field are chosen from a picklist. The picklist is made up from documents that have a code and an associated description.

What I want to do is have the computed description field show the descriptions for the codes selected.

I would prefer to do this in formula.

Thanks in advance.

Rob

2

Subject: RE: @DBLookup with multivalue field as key

If you @DbLookup using a multivalue as a key, Notes will try to find a match for each value and return a list of the matching results. If a non-matching key is found, only the results for the previous keys are returned. If the first key is a no-match Notes returns an error. [thanks to Haydn Parker for the correction]

So, if you’re certain that there’s exactly one match for each key, you could do something like this:

@If(@Elements(Codes) = 0;

“”;

@Do(

  _tmp := @DbLookup(""; ""; "viewname"; Codes; 2);

  @If(@IsError(_tmp) | @Elements(_tmp) < @Elements(Codes); "Cannot locate code " + @Implode(Codes; ", ") + ": " + @Text(_tmp); _tmp)

)

)

You still need the error testing even if you’re sure of your data, because lookups can fail for other reasons, e.g. the user lost their connection to the server.

In situations where you’re not certain that there’s only one match for each code, you could use @Transform to lookup each element of Codes with a separate @DbLookup and use only the first return result.

@If(@Elements(Codes) = 0;

“”;

@Transform(Codes; “x”; @Subset(@IfError(@DbLookup(“”; “”; “viewname”; x; 2); "Cannot locate code " + x); 1))

)

3

Subject: RE: @DBLookup with multivalue field as key

"Only if no keys match will Notes return an error"but Notes will stop the lookup at the first key not found.

If the view contains a,b,d,e,f

and you do a lookup with

a,b,c,d,e

it returns

a,b

and doesnt return d & e