Fun with @DBLookup
I thought as a long term (experienced?) Lotus Notes developer I knew everything about a basic function of Notes: @DBLookup
I admit: When I first had contact with this beast (in Lotus Notes 3.1, I think), I had some problems with the function. Mostly this was because of my missing knowledge about functions like @IsError, and my IT background (I learned "real" programming languages and "real" databases before starting to work with Notes). But finally I managed to work with it - and was a happy user of @DBLookup. Until today, when I lost some hours, and finally learned something new about it.
The setup: In the context of a complex web application I have to lookup some documents and display information about them within a web page. The keys for the documents to lookup are stored in a (multi-value) field in the document which is shown as web page. So I'm just passing the field as key into the @DBLookup, like this
@If (MarginalNoteKeys = ""; @Return (""); "");
_view := "vwCustomLookupMarginalNotes";
_column := 2;
_MarginalNoteList := @DbLookup ("Notes" : "NoCache"; ""; _view; MarginalNoteKeys ; _column);
@If (@IsError (_MarginalNoteList); @Return (""); "");
Everything worked fine. Until I started to test this functionality in the more complex part of the web app: Some of the documents will be copied over to another database. So it might be that only some of the documents to be looked up (the "marginal notes") are available there - so the key list will contain some values/keys where we won't find a document for.
My first test showed the error message "entry not found in index". But there were some documents which should have been found. Hmm.
Some more tests: sometimes some of the marginal notes were displayed - but not all that I expected.
After a lot of testing I found out, that my guess how @DBLookup worked was wrong. I thought, that it would return all the documents found for the keys which "worked", and ignore the keys which were not found.
@DBLookup will get all hits until the first key which is not found and will stop processing instantly.
I checked my Notes 5 client: same behaviour.
Solution (available with Notes 6):
Add the keyword [FailSilent] to the function call. Then @DBLookup will process all keys and will return just empty list elements for the keys which are not found in the view.
_MarginalNoteList := @DbLookup ("Notes" : "NoCache"; ""; _view; MarginalNoteKeys ; _column; [FailSilent]);
You still need the error handling (@IsError) to handle typos in the view name etc.