Genii Weblog

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

Thu 12 Feb 2004, 09:16 PM
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.

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 :


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?

Coming Soon
Tomorrow, or when I get to it,, we will tackle @DbLookup in @Midas Formulas.

Copyright 2004 Genii Software Ltd.

Thu 12 Feb 2004, 04:10 PM
responded to Rich Schwartz about this, but thought it deserved at least a quick mention here for those who don't know.  A certain amount of noise has been made about the 64KB limit being "fixed" or "eliminated" in formula language for ND6.  The bad news is, it isn't all the way gone.  The good news is, even part way is more useful than it seems.

Bad News First
In one of those odd concessions to the difficulty of changing "everything" and thus making do with changing what you can instead, Damien Katz (formerly of Iris/IBM) fixed the formula language for Notes/Domino 6 so that internal results can be greater than 64KB, but not final results. Thus, either 

@Unique(@DbColumn("", "", "Main View", 2))


@Trim(@Replace(@DbColumn("", "", "Main View", 2); to_eliminate; ""))

could easily have an interim result from the @DbColumn of greater than 64KB which would have blown through the limits in R5, but which will still be acceptable if the end result of the whole formula is less than 64KB. 

Good News
This is a much bigger gain than you might think, because you can always do something like:

total_count := @Elements(@DbColumn("", "", "Main View", 2));

and then use the new looping constructs to take smaller chunks of data at a time using @Subset.  Therefore, even though the bigger promise of not having to worry about the 64KB limits has not been realized, it is still possible to work with much larger result sets with a bit of effort - if you know the trick.

Copyright 2004 Genii Software Ltd.

Thu 12 Feb 2004, 02:44 AM
Follow up to Creating an effective sample post for those interested in what happened next...

This was originally started the next morning, but then the developer said we had a bit of a reprieve, so I didn't finish it right away.  When I did finish the demo, I didn't have time to document everything, but I do think the solution is pretty slick.

08:10am - The other shoe drops
Of course, one should never forget that no matter how carefully one thinks through these things, the developer will always have another surprise or two to spring on you.  This is not bad, it is the essence of how an effective demo develops.  You have to take each element and incorporate them together into a more fully conceived sample.  One of the differences between a consultant and a product developer is how you must approach such events.  As a consultant, you either have a fixed quote, in which case you curse the developer and argue against the changes, or you are paid by the hour, in which case you welcome the additions as additions to the kids college fund.  As a product developer, you should see these are key clues into how any new eyes will see the problem.  New people looking at the sample may start the same way, and evolve to the point where they ask the additional questions, so you want to make sure that the demo shows the evolution.

08:31am - Stop procrastinating and face the changes
My youngest, who managed to wake up and keep me up for another hour and a half after going to bed last night, is now safely fed and ready for school.  It is time for me to "finish" the sample.  Here is the additional information the developer sent:

<<For your blog here is a link to the comment in Notes.Net
I have a couple of quick questions:
1. Can this be extended to highlight words in any field on the document. I can see how it could be extended to other rich text fields, but all of the fields I have are text fields and the phrases could occur in a number of fields.
2. This only works if the document is being read. Is this just so that the changes do not get saved? If I had the doc in edit mode could I still highlight terms providing I write something that stops the highlight from being saved?

So, there are two new requirements, both of which Midas can handle, albeit with some tricky thinking.  The first requires some form of rendering of the document, which could work by creating a new, never-saved document and showing that (using the View events probably), or possibly by rendering the whole document back into the original rich text field.  The second is easy, but actually contradicts the first, in that if the document is in edit mode, you can't highlight anything but rich text.  Even if it is not what this developer needs, I'll treat these separately, then perhaps handle both at the end.

08:35am - Start with the easy problem
In life, I tend to tackle the difficult problems first, and save the easy ones for later.  In creating a sample, this is a mistake.  The supposedly easy problem may turn up issues with the supposedly difficult part, which you want to catch early.  Also, if your developer calls suddenly and lets you know he needs whatever you have ready, right away, it is better to have actually completed something.

So, I'll handle the editing issue for the first rendition of the problem.  I'm guessing that all I have to do is go to the QuerySave event and set Continue=False.

08:50am - Minor issues
I added the Continue=False, but of course that looks stupid since it simply silently prevents saving.  I added a message box, but now it is impossible to ever make changes to the document.  I wonder if I can check which view this came from and only prevent edits in the Search Results Folder, and then only show highlights from the Search Results Folder.

09:06am - Stupid Notes client
As usual, Notes makes you jump through hoops.  It is not particularly easy to determine from what view the document was opened, although Notes obviously knows.  So, I do what all good developers do, I called a Lotus Geek who might know the answer.  In this case, I am friends with the original Lotus Geek, Rocky Oliver, who told me that the view name is available from the ParentView property of the NotesDocument.  The Help db confirms this, but it still didn't work from the Source.Document in the QueryOpen event of the form, at least not in 5.0.10 where the customer needed it.  I threw in the towel and did what all good sample developers do after calling a Lotus Geek and having that not help.  I kludged an answer, since this is a sample, after all.  Specifically, I save an environment variable with the current view (or folder actually) from the PostOpen event of the view, then clear it again at the QueryClose event of the view.  Yuck!  Still, it works.

09:27am - Reprieve
The developer calls to say we don't need a final solution, just some assurance that this will work.  I give him assurance, because a true developer is always sure of himself(/herself), even in the face of adversity.

One Week Later - Cool Solution
I don't have time to go through the steps, but I found a pretty cool solution.  In the QueryOpenDocument event of the Search Results folder, I set Continue to False, then access a profile document as a scratch document and render the rich text item to that document, and open it in Read mode with all highlights created.  The highlights all of a sudden work for any field or value on the form.

I actually created two Search Results folders, and made the first operate the way I originally wrote it, and the second operate the new way.  An effective sample should always show as many alternative ways to do something as is possible without completely confusing the reader.

I plan to release this as a regular sample as soon as I can.  It is easy to understand and effective, although I will probably spend several more hours seeing if I can make it even simpler, because that kind of time is always well spent.  At the very least, I need to document it to make sure it is crystal clear what is happending and why.

Thank you for sticking through this long ramble.  I'll re-read it when it is a bit earlier an perhaps clean it up.  Now, lets see how the developer will react this time.

Copyright 2004 Genii Software Ltd.