Using @DbLookup to Access Rich Text Tables
Notes Lookups vs. @Midas Lookups
The @DbLookup you already know (Notes) is used to retrieve subsets of columns of data based on key or position from Notes views. The @DbLookup you will soon know (@Midas) is used to retrieve subsets of columns of data based on key or position from Notes rich text tables. The result of the lookup in both cases is a list, whether a text or numeric list. The resulting lists can be used in the same way.
Getting Started
So, let's first take a look at the @DbColumn you know (Notes). The Notes Help shows these two syntaxes:
@DbLookup( class : "NoCache" ; server : database ; view ; key ; columnNumber )
@DbLookup( class : "NoCache" ; server : database ; view ; key ; fieldname )
and simple examples would be :
@DbLookup("Notes":"NoCache";"";"Inventory On Hand";"Hardware";2)
@DbLookup("Notes":"NoCache";"";"Inventory On Hand";"Hardware";"ComponentPrice")
Looking from left to right, you start with the server and database, drill down to the view which acts as a table, filter the results based on the key, then drill down to the specific column of data as seen through that filter.
So, how different is the @DbLookup you use with @Midas Formulas? Not much. The syntax is:
@DbColumn(class : "NoCache" ; server : database ; doc-selection ; field ; key; columnNumber [; properties] )
@DbColumn(class : "NoCache" ; server : database ; doc-selection ; field ; row; columnNumber [; properties] )
and simple examples would be :
@DbColumn("Midas":"NoCache";"";@DocumentUniqueID;"Body";"Hardware";2)
@DbColumn("Midas":"NoCache";"";@DocumentUniqueID;"Body";4;2)
Let's look at the differences. Again, looking from left to right, you start with the server and database, drill down to the document itself, usually identified by note id or unid, then drill down to the exact rich text field, filter the results based on the key or row, then drill down to the specific column of data. Instead of the view, the @Midas format uses two parameters, the doc-selection and the field to determine the table of data, and then is back to the column, just like the Notes format. Additionally, instead of specifying a key, one can specify a specific row, thus allowing you to specify a particular cell. The key, like that in Notes, is matched against the value in the first column.
If it isn't clear, the first example looks at the table in the rich text field (and, yes, you could specify a different table if you wanted), searches for each row whose first text string is "Hardware" and returns the first text string in the second column. Since there may be several matches, this is returned as a text list. The second example just returns the first text string the the fourth row, second column.
Properties
Like @DbColumn, the @DbLookup for @Midas Formulas has an optional properties string. While there are several properties which can be used, you will mostly only need to worry about four:
- SkipTitle="Yes", "No" or number (number allow you to skip more than one row. Defaults to "No")
- SidewaysTable="Yes" or "No" (defaults to "No")
- KeyColumn=column (if specified, uses that column to match key. Defaults to 1)
- Format="Currency", "Number" or "Text" (to allow you to determine the type of the result. Defaults to "Text". "Currency" returns a result which is a numeric list, but skips preceeding characters before the number starts.)
Note that these are the same properties which were important to @DbColumn for @Midas Formulas, with the addition of the KeyColumn which lets you filter based on a different column than the first (score one for @Midas over Notes). By the way, since it is possible for table cells to contain all sorts of data, there are ways to return just a specified part of that text, or key off a particular part, but those are beyond the scope of this article.
Conclusion
If you know how to use an @DbLookup now in Notes, you basically know how to use it in @Midas Formulas. Drill down to the table of data, filter it, return what is left and work with it. Not much of a learning curve, eh?
Sample databases
There is a sample database called @Midas Lookups which shows how an @DbLookup can be used. In particular, look at the Domino Express Offerings: Pseudo-Spreadsheet document and see how the statement
price := @DbLookup("Midas":"NoCache"; ""; @DocumentUniqueID; "Body":"Table 1"; "Collaboration Express"; 2; "Format=Currency");
returns a specific price from a cell of a rich text table. Also, see how the spreadsheet is implemented with a series of @DbLookups. Also, look at the Fix Lists: Nested Tables with Hyperlinks document uses a vastly more complex table and still treats it as simple data. Even before you get an evaluation license, you can download this and look at the table and the code, but why not get a free evaluation license and see it work for you.
Next step
Part 3 in the @Midas for Domino Developers will tackle @DbCommand in @Midas Formulas, which is different, but still pleasantly familiar.
Copyright © 2004 Genii Software Ltd.