Updated version of this post: here
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?
Coming Soon
Monday, or when I get to it,, I will present a very brief explanation of @DbCommand in @Midas Formulas, although it doesn't fit as well into this mode of explanation.
Copyright © 2004 Genii Software Ltd.