Structured DBlookup’s in Lotus Notes

Posted on by — 4 Comments ↓

Another Thursday, Another (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 :

1
@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:

1
2
3
4
5
6
7
8
9
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

1
field :="Status";

is wrong, field is a keyword. It should have been

1
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

1
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 :

1
2
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.

4 Replies to “Structured DBlookup’s in Lotus Notes”

  1. Woo Hoo! Great job. This is two weeks in a row that I’ve come here and said “DOH! Why wasn’t I doing that in the first place!?”

    Great, now I’m going to have to go back and change all of my @DbLookup code….

    Looking forward to next week…I like my users quiet, I don’t care if they’re happy or not. 😉

    Reply ↓

  2. Pingback: False Positives » Computed and Structured Dblookup in Keyword Formulas to reduce simple mistakes.

  3. Like your logic and approve !
    One thing has changed, the apperance of arrays in Formula Language and you can make the code even more readable.
    Just replace
    server := @Subset(@DbName; 1);
    by
    server:=@DbName[1]
    and
    database := @Subset(@DbName; -1);
    by
    database:=@DbName[2]

    Cheers

    Reply ↓

  4. i want to create a form where 3 fields are there, “employee id,name and age”. When user will enter employee id the other 2 fields will be automatically populated. Name,age-these 2 fields are computed fields and i want to use @DbLookup in formula language. I am nu to Lotus notes and cant understand the steps how to do this. Can anybody help me discussing the steps…. pls help

    Reply ↓

Leave a Reply