@Midas for Domino Developers, Part 1
Thu 12 Feb 2004, 09:16 PMTweet
by Ben Langhinrichs
Using @DbColumn to Access Rich Text Tables
A couple of the new beta testers for our @Midas Formulas have asked for a "for Dummies" run down of how to use @Midas Formulas. I have never actually read one of the "... for Dummies" books, so instead I'll take my inspiration from Tom Duff and Joe Litton's excellent presentation at Lotusphere 2004, BP117 - Java for Lotus Domino Developers. One of the best things about the 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 how to use something you already know to do something new.
Notes Lookups vs. @Midas Lookups
The @DbColumn and @DbLookup you already know (Notes) are used to retrieve columns of data, or 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.
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.
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 :
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.
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.)
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?
Tomorrow, or when I get to it,, we will tackle @DbLookup in @Midas Formulas.
Copyright © 2004 Genii Software Ltd.
What has been said:
115.1. Duffbert (02/13/2004 05:28 AM)
OK... this is cool (on a couple of levels)...
First off, I love the style of learning something based on what you already know. It's something that I instinctively knew based on my own learning style. I also heard Ken Wax at a Lotusphere session describe that style of learning as essential to teaching people new concepts.
And I find it cool that you found inspiration from our session. To be serious for a moment, the whole "Team-TSG" concept was partially for fun, and partially an attempt to "brand" our style of presentation and writing should it turn out to be useful. I'm thrilled to see that people found the approach useful, and I look forward to seeing the rest of your "Team-OTG" (One Tall Guy) material on Midas!
115.2. Ramesh (08/25/2005 05:45 AM)
I got to know that We can access view of others database(pactop.nsf) from my sorce(licn.nsf). (both are in the same directory). If some one helps me to get it If possible with a sample databse because I am not an domino designer...
Your immediate help is very much appricaited.
115.3. Ben Langhinrichs (08/25/2005 06:32 AM)
You can certainly access another database in this way. In a server based agent, this would look like:
val := @DbColumn("Midas":"NoCache"; "":"pactop.nsf"; note_id; "Body"; 2)
which would get you the second column of the first table in the specified document's Body field. Let me know if you want to try out an evaluation license for the @Midas formulas, as this is a fairly easy thing to test.
115.4. rajamani sudhakar (01/05/2006 03:35 AM)
how do i populate a combo box in the lotus notes form with fields from an external database (Mysql database). i have established the ODBC connectivity but i have a few questions in this regard.
1) Can i use @dbcolumn to retrieve the fields in the combo box.
2) if i can use @dbcolumn, in which event should i write the formula.
3) lotus script for accessing external database in a combo box on a lotus form.
Kindly clarify these for me.