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";
value := @DbLookup( class : cache ; server : database ; view ; key ; fieldName );
Update: I made a boo boo
is wrong, field is a keyword. It should have been
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
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.
See also Structured DbColumns in Lotus Notes.
value := @Unique(@trim(@DbLookup( class : cache ; server : database ; view ; key ; fieldName );));
since in R6.x+,Interim results in a formula can be greater than 64KB, but the end result of the formula cannot.