Genii Weblog

@Midas for Domino Developers, Part 2

Fri 13 Feb 2004, 09:18 AM

by Ben Langhinrichs
Updated version of this post: here

Using @DbLookup to Access Rich Text Tables

Taking inspiration from Tom Duff and Joe Litton's excellent presentation at Lotusphere 2004, BP117 - Java for Lotus Domino Developers, I am writing a short series of posts showing how using @Midas Formulas is taking what you already know and extending it.  Yesterday, I wrote about Using @DbColumn 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 :


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.

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.

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.

What has been said:

116.1. Michael
(02/14/2004 02:48 AM)

Is it possible to not just bring content based on the table inside the RTF, but bring the entire RTF and convert it to HTML for ex...this could be tremendous feature for web development :-)

116.2. Ben Langhinrichs
(02/14/2004 06:09 AM)

Good question! Absolutely. This is actually the focus of my next blog post, but to give you a preview, you can either retrieve the entire in HTML or XHTML, or you can retrieve individual rows and put them together, which one of the cool samples for the new beta does. For that latter version, the code is basically:

@DbCommand("Midas":"NoCache"; "GenerateHTML"; ""; unid; "Body":"Table 1")

to get the whole table or

@DbCommand("Midas":"NoCache"; "GenerateHTML"; ""; unid; "Body":"Table 1; Row 1,3,7")

which would give you the table in HTML but with only rows 1, 3 and 7 included.

Have you joined the beta?

116.3. Michael
(02/14/2004 11:34 AM)

not sure you get my point. I don't want (or I would like should I say) to be limited to a table inside the RTF. I'd like to get the whole rich text field (including tables if they are some). You see ?

Yes joined the beta but only received the first one a while ago and no news since this.

Thanks anyway.

116.4. Ben Langhinrichs
(02/14/2004 12:01 PM)

Sorry if I wasn't clear. You can generate HTML for the whole field or any portion of it.

The new beta is coming out Tuesday, so watch for it and let me know if you don't see it.