False Positives

Another Thursday, Another Show-n-Tell+Thursday (and the pressure is on after that link from Ed Brill to last weeks entry on Building a better “Save & Exit”)

Lets take a look at DbLookUps, and what can go wrong. Your typical DbLookUp (this example is from the Developer Help db) is :

@DbLookup("":"NoCache";"RESEARCH":"SMITH\\PROJECTS.NSF"; "In Progress";"Virus Check";"Status")

Which means, on the server “RESEARCH”, in the database Projects in the SMITH directory, used the view “In Progress”, with the key “Virus Check” and get the value in the “Status” field, and it works.

But in the real world database get moved around to different Servers and directories, and copied to be used for different things, and mistakes get made. Plus, hard coding values is bad form.

So many moons ago, I adopted (in others I can’t remember where I saw this, but thank you!) a more structured approach:
class := "Notes";
Cache := "NoCache";
server := @Subset(@DbName; 1);
database := @Subset(@DbName; -1);
view := "In Progress";
key :="Virus Check";
fieldName :="Status";
value := @DbLookup( class : cache ; server : database ; view ; key ; fieldName );
@If(@IsError(value);@Return(@Text(value));Value)


Update: I made a boo boo field :="Status"; is wrong, field is a keyword. It should have been fieldName :="Status"; Sorry folks, my bad!

A couple of things are different here:

It’s self-documenting what the lookup is doing. You can look at the code months later and read it, and so can you less experienced coworker. If you make a change to a value, you know where to do it, and you will not screw up the colons and semi-colons! That’s the power of the structure.

The cache value I rarely change to “Cache”, unless it was something that a Admin value you want to control without doing code (and since R5, profile doc’s are better for that).

For the server value, I’m assuming that the place I’m look for is on the same sever, and I’m using the @DbName rather than hard coding it.

For the database value, I’m looking it up in the current database, again using that @DbName function, whatever it’s name is! Handy when your copying code, or when it gets moved and renamed. The other thing you what to a lot is look on a value in a database in the same directory - my preference is that multi db applications are all in the same directory) - using

database :=@LeftBack(@Subset(@DbName;-1);"\\")+"\\"+"registry.nsf";
to get the path where the current db is.

The View, Key and Field values are straightforward.

The next line is a lifesaver. If the lookup fails the “@Return(@Text(value))” get the text message for the error, displays it and stops. If after debugging you don’t what user to see the error messages, or if no result found (”Entry not found in index”) is a valid result, you could @Return(”") instead. You could also replace it with @if(DebugFlag=”Y”;@Return(@Text(value));@Return(”")) and have DebugFlag set in a share field on all your forms! One place to turn on or off or debugging messages.

Thats all for now. Next week I’m thinking of a tip on now to use the DBlookup to better leverage the Name and Address book, and make your users happier, or a least quiet.

(Now Here : Using the Lotus DBlookup to better Leverage the Name and Address book. )

See also Structured DbColumns in Lotus Notes.

Update (Jan 3 2007) Based on this Notes Forum comment by Ben Langhinrichs, I would consider revising the last 2 lines to :

value := @Unique(@trim(@DbLookup( class : cache ; server : database ; view ; key ; fieldName );));
@If(@IsError(value);@Return(@Text(value));@Sort(Value));

since in R6.x+,Interim results in a formula can be greater than 64KB, but the end result of the formula cannot.

Share This

See also

• SNTT Structured DbColumn’s in Lotus Notes
• Using the Lotus DBlookup to better Leverage the Name and Address book
• Avoiding Hard Coding of Group Names.
• Lotus Formula Language > Developer tool: read/set document fields from view
• Computed and Structured Dblookup in Keyword Formulas to reduce simple mistakes.

This entry was posted on Thursday, February 23rd, 2006 at and is filed under Show-n-Tell+Thursday, Lotus Domino. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.