Genii Weblog


Civility in critiquing the ideas of others is no vice. Rudeness in defending your own ideas is no virtue.


Wed 6 Oct 2004, 09:06 AM
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 updating a short series of posts I first wrote last February 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 :

@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.

Wed 6 Oct 2004, 07:55 AM
This debate was much more evenly balanced than Thursday's, in which John Kerry beat George Bush so handily.  Both John Edwards and Dick Cheney were in good form, calm and serious.  Both got in some good digs at the other.  Actually, I would think that Cheney's performance would have a mixed reception in the Bush headquarters, because it tended to show up Bush's weak performance, but Cheney managed to convey the message he wanted to convey.  I would guess the Kerry people were very happy with John Edwards, simply because there had been numerous predictions that Cheney would "put Edwards away", which he completely failed to do.

All in all, a draw, but since a draw puts the pressure so squarely back on President Bush, it was perhaps a tactical win for the Kerry/Edwards ticket.  I know that anything can be spun either way, but short of getting Dick Cheney to lose it and start swearing at him during the debate, there was little John Edwards could have done to shake the veteran campaigner.  Dick Cheney's job was to make John Edwards look green and inexperienced, and he didn't get that job done.

True to form, the on-line polls show a huge win for Edwards (the Democrats clearly getting all their people out to stack the vote better than the Republicans), but the more believable polls show a split, with ABC saying Cheney won and CBS showing Edwards won.  Most of the media seem to think this was very big for the Democrats, since all Edwards had to do was tie.  I personally think it simply becomes a potential trap avoided, and signifying little else.  The focus is back on the head of each ticket for Friday's debate.

Copyright © 2004 Genii Software Ltd.