Genii Weblog

@Midas for Domino Developers, Part 1

Tue 5 Oct 2004, 02:43 PM



by Ben Langhinrichs
Using @DbColumn to Access Rich Text Tables

Back in February when our new @Midas Formulas was in early beta, I got some requests for a "for Dummies" version of how to use @Midas Formulas.  Since I have never actually read one of the "... for Dummies" books, I took my inspiration from Tom Duff and Joe Litton's excellent presentation at Lotusphere 2004, BP117 - Java for Lotus Domino Developers and I have decided to repeat the series, updating it where necessary, to provide a resource for new evaluators and users.  

One of the best things about the Lotusphere presentation was that it made LotusScript developers realize how much they already knew about Java development in Domino, because they already knew the object model.  Similarly, using @Midas Formulas isn't learning how to do something new, it is learning a new way to use something you already know.

Notes Lookups vs. @Midas Lookups
The @DbColumn and @DbLookup you already know (Notes) are used to retrieve columns of data, or filtered subsets of columns of data, from Notes views.  The @DbColumn and @DbLookup you will soon know (@Midas) are used to retrieve columns of data or subsets of columns of data 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 for Notes development.

The similarities are striking, but hardly surprising.  A Notes view, after all, is basically a table of data dynamically put together from a set of documents, but it is still a table.

Getting Started
So, you already know how to use @DbColumn, right?  The Notes Help shows this syntax:

@DbColumn( class : "NoCache" ; server : database ; view ; columnNumber )

and a simple example would be :

@DbColumn("Notes":"NoCache";"";"Inventory On Hand";2)

Looking from left to right, you start with the server and database, drill down to the view which acts as a table, then drill down to the specific column of data.

So, how different is the @DbColumn you use with @Midas Formulas?  Not much.  The syntax is:

@DbColumn(class : "NoCache" ; server : database ; doc-selection ; field ; columnNumber [; properties] )

and a simple example would be :

@DbColumn("Midas":"NoCache";"";@DocumentUniqueID;"Body";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, then drill down to the specific column of data.  Instead of the view, the @Midas format uses two parameters, the doc-selection and the fieldto determine the table of data, and then is back to the column, just like the Notes format.

Properties
There is one other difference between the Notes @DbColumn and the @Midas @DbColumn.  There is an optional properties string.  The reason for this parameter is due to the difference between the tables of data which make up Notes views and the tables of data which make up rich text tables.  View data is made up of fairly simple columns of data, even if the view is displayed in fancy ways.  You could say that the content and presentation are separate.  In a rich text table, the content and presentation are mingled, so you need properties such as SkipTitle=Yes, which says that the first row of data is a title.  (In a Notes view, the title row is separate from the data.)  While there are several properties which can be used, you will mostly only need to worry about three:

  • SkipTitle="Yes", "No" or number (to allow you to skip more than one row.  Defaults to "No")
  • SidewaysTable="Yes" or "No"  (defaults to "No")
  • 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.)

Conclusion
If you know how to use an @DbColumn now in Notes, you basically know how to use it in @Midas Formulas.  Drill down to the table of data, return it and work with it.  See, now wasn't that easy?

Sample databases
There is a sample database called @Midas Lookups which shows how an @DbColumn can be used.  In particular, look at the US Democratic Candidates document and see how the statement

states := @Trim(@DbColumn("Midas":"NoCache"; ""; @DocumentUniqueID; "Body"; 1; "SkipTitle=3"));

returns a column of data from a complex rich text table.  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 2 in the @Midas for Domino Developers will tackle @DbLookup in @Midas Formulas.

Copyright © 2004 Genii Software Ltd.

What has been said:


216.1. Duffbert (10/05/2004 06:08 PM)

Thanks for the kind words... About time to crank up the marketing machine so we can do that session again at LS 2005! :-)


216.2. DhanaSekaran (08/16/2005 04:26 AM)

How to create our own @functions?