DBLookup using mulitple value field as key(s)

HCL Domino Domino Forum
1

HiI"m sorry if this has already been addressed but I cannot find information pertaining to my question. I would like to perform a DBLookup using a field that contains multiple values as the key(s). For each value in the field, use that as the key to lookup to a view and pull what is in column 2. Is this even possible in formula language, using @For perhaps?

Thank you

Diane

2

Subject: DBLookup using mulitple value field as key(s)

You can do it without @For, but the behaviour may not be what you want. If you feed a list in as the key value, the lookup will stop returning data when the first value fails, so if you feed:

“apples” : “bananas” : “canteloupes”

and “apples” is not found, you will get an error back. If “apples” is there, but “bananas” is not, your lookup will return values for “apples” only, and so forth. With @For, you can handle the failures separately:

keylist := “apples” : “bananas” : “canteloupes”;

outlist := “”;

@For(i := 1; i <= @Elements(keylist); i := i + 1;

outlist := outlist : @DbLookup(“”; “”; “ViewName”; keylist[i]; 2; [FailSilent])

)

@Trim(outlist)

3

Subject: RE: DBLookup using mulitple value field as key(s)

Thanks for your response, Stan. I actually tried a formula similar to this that you had posted before but my return value was “1”.

To give you some detail, I have a multivalue text field called ‘Reason’ that has a @picklist formula to prompt users to choose more than one item from a view. I have another field called Action that does a dblookup to the same view using what is in the Reason field as the key(s) and return what is in column 2. No chance for failure since they are choosing the keys that exist in the dblookup view. Are you confirming that is possible to lookup and return data for several keys in one same dblookup formula?

4

Subject: RE: DBLookup using mulitple value field as key(s)

Yes.

5

Subject: RE: DBLookup using mulitple value field as key(s)

hmm…then why would I be getting “1” as the return data?

Thanks.

6

Subject: RE: DBLookup using mulitple value field as key(s)

Thanks for your help Stan. I got it to work with a regular dblookup formula.