« OpenLog Jumpstart | Main| Validate a Field Value's Uniqueness Across All Documents »

Standardized and Simple-to-Use Keyword Formulas

QuickImage Category

There are probably as many ways to setup a basic Lotus Notes keyword lookup infrastructure as there are Notes developers, but I've employed a few features in mine that I think many of you will find useful.  I'll talk about one such feature today that can easily be added to whatever setup you're using now.  It may not seem like a big deal to actually write the DbLookup keyword formula for a given field, until you've spent several hours trying to figure out why one of them just won't work, and finally realize a tiny typo is to blame.  For this reason, I've taken all the fun out of the experience by building the actual formula on the keyword document itself.  All one has to do is copy and paste it into the field.  It looks like this:

A picture named M2

You'll notice that this formula assumes there is a view somewhere named ".SysKeywordView", and its only requirements are that it be sorted by the "Item Name" in the first column, and have the "Item Values" field populating the second column.  The structure of the formula is adaptable so that you can easily tweek details, for example changing the SERVERDB variable so you can use the formula in a separate database.  This would be an excellent approach for a multi-database application where one "Administration" database stores all kinds of global configuration settings like keyword lists that are used by the other databases.

But the real fun here and the point of this post is the dynamic building of the formula itself, allowing for easy deployment "in the field" (pun intended).  So without further ado, here is the code that does the job:

"KeywordName := \"" + ItemName + "\";" + @NewLine +
"CLASS := \"\";" + @NewLine +
"NOCACHE := \"\";" + @NewLine +
"SERVERDB := \"\";" + @NewLine +
"VIEW := \".SysKeywordView\";" + @NewLine +
"KEY := KeywordName;" + @NewLine +
"FIELDNAME := 2;" + @NewLine +
"Lookup := @DbLookup(CLASS : NOCACHE; SERVERDB; VIEW; KEY;FIELDNAME);" + @NewLine +
"@If(@IsError(LookUp); \"\"; Lookup)"

This is actually a simplified version of my actual approach, which utilizes profile documents and also allows for the setting of the field's default value in the configuration document.  If there is interest, I can post a sample database in the OpenNTF Code Bin with all the relevant code bits included (that would also provide me a good excuse to spruce up the form's cosmetics .  Let me know what you think.


1 - Help, I am new to notes design, and have been given the task of setting up a helpdesk application for the Court,I have a field that has (7) people that I would like to display, (tech support) I need to display their names, but I am not sure how to do a dblookup,can some one tell me how this is done , I do not understand it. I have everything done in my application except this. also is it possible to include the password of the users but not have it show up, (it will only display to the tech support if they roll over this field ) or something. This is all of the things that my boss wants to see. I was able to find a helpdesk application on the internet, that I have modified and changed to my use. -------- Thanks for you help. ------ Sandi

2 - Sandra, you should start by looking in the Domino Designer Help database (press F1 while in Domino Designer). Search for "DBLookup" and you should find plenty of documentation. The other place to look for various useful code snippets (and a much better place to ask questions like this), is in the Lotus Domino 6/7 Forum here: https://www-10.lotus.com/ldd/nd6forum.nsf/DateAllThreadedweb?openview

This might also help - if you plug relevant values into the following formula block you will avoid many of common typo/syntax errors:

CLASS := "";
NOCACHE := "NoCache";
VIEW := "Viewname displaying documents with value you want";
KEY := "text string corresponding to the sorted column value you want to find in the view (or field value from this document)";
FIELDNAME := "NameofField storing value you want";
@If(@IsError(LookUp); ""; Lookup)

As for the password display thing, I don't understand precisely what you mean or the need for it, but in any case its probably not possible or desirable.

Tools I Use

Idea Jam

Subscribe to This Blog

 Full Posts  Comments

netvibes Add to Netvibes


Hosted by



This site is in no way affiliated, endorsed, sanctioned, supported, nor blessed by Lotus Software nor IBM Corporation, nor any of my past or future clients (although they are welcome to do so). The opinions, theories, facts, etc. presented here are my own and in no way represent any official pronouncement by me on behalf of any other entity.

© 2005-2021 Kevin Pettitt - all rights reserved as listed below.

Creative Commons License
Unless otherwise labeled by its originating author, the content found on this site is made available under the terms of an Attribution / NonCommercial / ShareAlike