Standardized and Simple-to-Use Keyword Formulas
Category Show-n-Tell ThursdayThere 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:
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.
- 
Full Posts
Comments
Comments
Posted by Sandra L. Montgomery-Wenz At 01:59:52 PM On 08/23/2006 | - Website - |
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";
SERVERDB := "";
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";
Lookup := @DbLookup(CLASS : NOCACHE; SERVERDB; VIEW; KEY;FIELDNAME);
@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.
Posted by Kevin Pettitt At 03:03:18 PM On 08/23/2006 | - Website - |