I have a subform that uses the @DBlookup to return information from a view. The field it is accessing is a multi-value field. The lookup is looks like this: @DbLookup(“”:“NoCache”;“”;“ApproversNames”;Approver3;3));. Now there can be multiple results returned…so my question is how do I pare my results down? And how do I get to the value I want within the Multi-Value field? I am new to Lotus Notes so my knowledge of LotusScript and @Formula language is minimal ( a month). I need to do this without modifying the associated view. Thanks.
Subject: @DBLookup’s, multi-value fields, and paring down results.
what is returned initially and what do you want to pare the results down to?
Subject: RE: @DBLookup’s, multi-value fields, and paring down results.
Lets say the dblookup brings back 3 rows in the view I specified and the Approver3 fields in teh rows have the following values:
Approver3
_______________________________
row1: Rip VanWinkle;Jimmy Test;Sally Sue
row2: Tom Thumb;Jimmy Test
row3: Rip Torn;Jimmy Test;Sommer Days
I want to get the first row and get Jimmy Test.
Subject: RE: @DBLookup’s, multi-value fields, and paring down results.
And on what basis should “Jimmy Test” be the returned value?
Subject: RE: @DBLookup’s, multi-value fields, and paring down results.
Approver3 contains the value “Jimmy Test” of the name to retrieve from my form and the DBLookup would bring back the informaiton in from my view I am using. So my last post stating Approver3 as the column of the view should have just been Approvers. Sorry for any confusion.
Subject: RE: @DBLookup’s, multi-value fields, and paring down results.
That’s not what’s confusing. The lookup is bringing back nine (not necessarily unique) names based on the way the view is structured and the lookup key you are using. What makes “Jimmy Test” so special? How is the system supposed to pick that name from the list?
Subject: RE: @DBLookup’s, multi-value fields, and paring down results.
That’s a good question. The developers who setup this form wanted the users to be able to specify multiple staff, at the various organization levels, and that the staff member can be an approver at any level. The levels of approval are section manager/division manager/Bureau. The odd thing is they have this lookup that is grouped by these 3 levels (in a different view) and this drives a picklist to allow the user to specifiy the approver for the various organization levels. They use another view that is not broke down this way to extract the information in the DBLookup. Frustrating.
Subject: RE: @DBLookup’s, multi-value fields, and paring down results.
Then there’s not a whole lot you can do – unless you can do your lookup in a view that isn’t consolidated that way. You might have been able to grab, say, the second name in the list if there were values returned from only one document, but if you have an arbitrary number of names returned from an arbitrary number of documents, you’re essentially out of luck using the view you have. You can use a field name rather than a column for the lookup return (if that’s any help – it may not be, depending on how anti-lookup the database design is).