IBM®
Skip to main content
    Country/region select      Terms of use
 
 
   
     Home      Products      Services & solutions      Support & downloads      My account     

developerWorks  >  Lotus  >  Forums & community  >  Best Practice Makes Perfect

Best Practice Makes Perfect

A collaboration with Domino developers about how to do it and how to get it right in Domino

Yesterday I talked about reasons to use a separate, hidden view for @DbColumn and @DbLookup. But if you choose to risk the wrath of harkpabst, and use a single view (without re-sorts!) for both users and lookups, can you at least mitigate the adverse effect on maintainability?

There is one thing you can do. For @DbLookup, for the argument that specifies what column to get the results from, you can code either a column number, or a name. For instance, if column 5 displays the field DateDue, you could write either:

@DbLookup(""; ""; "lkByKey"; Key; 5)
or
@DbLookup(""; ""; "lkByKey"; Key; "DateDue")
and the result is the same.
(For @DbColumn, you must use a column number. Too bad.)

You might have been taught that it's more efficient to use the column number because the value is read directly from the view index, whereas using a fieldname requires accessing the document, which is slower. This is only approximately true. The Designer help actually says, "Lookups based on view columns are more efficient than those based on fields not included in the view." Because DueDate is included in the view -- there's a column that displays that exact value -- the two formulas above are equally efficient. There may be some tiny difference between them, but very small compared to the extra time it takes to "crack open" the document note to read an item value that's not in a column, and I'm not actually even sure which is faster.

So, all right; that's nice. If the above two formulas are equally fast, there are two good reasons to use the second one.

  • It's more readable.
  • It's more maintainable because it won't break if you edit the view design and rearrange the columns. Even if you delete the column, the lookup will still work. It just won't be as fast.
But wait! There's more! By successive approximations, we come ever closer to the truth. In fact, when you specify a name argument to @DbLookup, it's not a fieldname. It's really the column name. Only if there are no columns with the specified name, does the lookup code open the document note to look for items with that name.

"What!" (you might be saying to yourself) "Columns have names? Does he mean the column title?"

Column programmatic nameNo. I'm referring to the column "programmatic name" which appears on the Advanced tab of the column properties. If the column just refers to a field, it's automatically assigned a programmatic name which is the name of the field. That's why "DueDate" works in the above formula. You're not referring to the field named DueDate in that formula; you're referring to the column by that name. If you write a formula in the column instead of selecting a field, the column is assigned a unique programmatic name of the form $n where n is a number, but you can change it if you like.

Now here's a key point: you can use the column name to specify the data column for your lookup. So if you know a column is called "$4", you can write:

@DbLookup(""; ""; "lkByKey"; Key; "$4")
instead of using the column number. Once again it's just as efficient, but less likely to break when someone edits the view design.

Of course, $4 is not a very descriptive name to appear in your formula. If you want to use the column for lookups, I suggest entering a better name in the column properties.

One other fun thing you can do with column names (if you share my ideas of fun), is use them in the formulas of other columns. This is occasionally useful in avoiding a repetition of some complex calculation.

Andre Guirard | 26 March 2008 04:45:00 AM ET | Man-Cave, Plymouth, MN, USA | Comments (17)


 Comments

1) Thing Two of Three about @Dbfunctions
Bruce Currier | 3/26/2008 8:30:02 AM

The other thing you could do is actually to create a column with the field you want to use for lookups and then hide it so a person couldn't change the sort order on you.

2) Thing Two of Three about @Dbfunctions
Keith Strickland | 3/26/2008 8:45:23 AM

"One other fun thing you can do with column names (if you share my ideas of fun), is use them in the formulas of other columns. This is occasionally useful in avoiding a repetition of some complex calculation."

That's cool, I wasn't aware of that. So, do you get any performance gain for a complex formula in a column by using the value of a different column instead of using the value from a field in the document? (hope that makes sense :-S)

3) Thing Two of Three about @Dbfunctions
Timothy Briley | 3/26/2008 9:32:19 AM

The answer to this should be "No", but I'll ask anyway. Does the same hold true for LotusScript? For example, if "City" is the 3rd column in a view and I use GetDocumentByKey to get the handle to the Notes document, is it just as fast to use doc.City(0) as doc.ColumnValues(2)?

4) Thing Two of Three about @Dbfunctions
Kevin Pettitt | 3/26/2008 11:08:26 AM

Andre, this is great. I was familiar with the column programmatic name but didn't know you could set your DbLookup key to it. I think I first discovered the programmatic name when I got bit by a potentially nasty side-effect of the ability to "reuse" that name in later columns. Specifically, if you copy columns from one view and paste them into another, they retain the programmatic names from the source view. If you already have a column with one of those names, then the column furthest to the right will display the same data as the first one, *regardless* of that column's actual formula.

For example, let's say you pay little or no attention to programmatic column names. In a typical application you'll have lots of columns in various views with identical names such as $6, $12, etc. Now let's say you want to copy a column you created in one view to all the others, perhaps an author name column with a formula like @Name([CN]; AuthorName). Being a formula, that column will have been assigned a name in $n format, we'll say $8 for the example.

So you copy that column and switch over to view #2, which happens to already have a $8 column with an entirely different formula, say @Trim(Subject). Your newly pasted column will now act as if it's formula is also @Trim(Subject), and you will spend the rest of day pulling your hair out trying to figure out why.

Lesson: ALWAYS give your formula columns meaningful and unique programmatic names. I generally keep the $ sign to minimize potential conflicts with actual form fields, so in this example my columns names might be $Subject and $Author.

5) Thing Two of Three about @Dbfunctions
Timothy Briley | 3/26/2008 12:14:10 PM

@5 - Thanks for the reply, but I think it goes with my comment found on "Thing 1" and this column is "Thing 2". Do you have an answer for my question in @3 on this column, restated below:

If "City" is the 3rd column in a view and I use GetDocumentByKey to get the handle to the Notes document, is it just as fast to use doc.City(0) as doc.ColumnValues(2)?

6) In my testing...
Kendall | 3/26/2008 12:54:08 PM

Timothy, I fine-tuned performance of some LS searches, a few years ago. I did a lot of testing of various ways of getting results and the doc info to display for the result list. Two things I found:

1. As I recall, doc.City(0) and doc.ColumnValues(2) performed similarly (leading me to believe this usage of ColumnValues still opens the document note).

2. NotesViewEntry's ColumnValues property performed significantly better! So for my search agent, I made one column with all the info I needed, and walked the search results using NotesViewEntry's ColumnValues property.

YMMV but I spent hours testing this stuff (local database, Island location, etc. to try to rule out server or network issues; quit Notes & deleted cache.ndk each time, to try to prevent caching from skewing results; etc.) and that was what I found.

7) responses to various comments
Andre Guirard | 3/26/2008 11:28:01 AM

Timothy, LotusScript pays no attention to the user's re-sort order when using back-end view-related functions. Of course, in a NotesUIView, positioning by key value and so on would have to take the current re-sort into account.

Keith, the summary item values have to be loaded in any case to feed into the selection and column formulas, so referring to a field value in the column shouldn't cost extra. I didn't test it, but I suspect there's no noticeable savings from using a column name versus a field name in a column formula -- only do it if you can have less complex formulas thereby.

Bruce, I'm not sure I understand your suggestion. Hiding a sorted column doesn't prevent the user from re-sorting by some other column, because they re-sort by clicking the column header of the other column, which is not hidden. If you hide all the columns, the user would have trouble re-sorting the view, but this has its own disadvantages.

8) more responses to comments
Andre Guirard | 3/26/2008 2:24:47 PM

Kevin, that's a bug that I think I've fixed. If you can reproduce it in 8.0.1 or 7.0.3 please send me the details.

Kendall, Timothy, I would've expected ColumnValues to be faster than accessing the item, but I haven't tested it myself and based on Kendall's testing, it sounds like Notes is opening the note for access to all items when you get the NotesDocument object by whatever means. There's probably some room for optimization here; seems like the client could do a "lazy open" of the note where it doesn't assume you'll need information from the items until you ask for it. I'll suggest this to the people who work on that part of the code.

9) Thing Two of Three about @Dbfunctions
Stan Rogers | 3/26/2008 3:49:01 PM

One thing that's always bothered me is the warning text accompanying the column programmatic name setting -- many (if not most) of the developers I've run into over the years have taken it to mean "don't touch!". This, in turn, leads to multiple "$3" columns, all with different formulae but all displaying the same value, when columns are pasted between views. Might the text be altered in some future version(s) to something a little less foreboding, particularly where newly created columns are concerned? (Jamming this.)

10) Thing Two of Three about @Dbfunctions
Timothy Briley | 3/26/2008 3:59:38 PM

@6 Kendall -

Thanks for the info. I guess the lesson to be learned is that if you need to grab the value of "City" from a doc and the value appears in a view, use view.GetEntry followed by entry.ColumnValues(2) instead view.GetDoc followed by doc.ColumnValues(2) or doc.City(0).

Andre - Any chance of getting these tips into the Notes documentation?

11) Thing Two of Three about @Dbfunctions
Andre Guirard | 3/26/2008 4:35:20 PM

Stan, as I said, I think the duplicate name on paste issue is fixed -- if not please provide a reproducible scenario. Thanks.

12) Thing Two of Three about @Dbfunctions
Kevin Pettitt | 3/26/2008 9:14:29 PM

Andre - the issue with pasting columns between views seems to be fixed in 8.0.1. The programmatic name is reset to the next available $n value if it's current name is already used in that view. Basically just like if you created a new column from scratch. If the pasted column is not going to conflict with an existing one, the original name is preserved, which is the right way to do it because you might have other code that references that name.

Good stuff!

13) Thing Two of Three about @Dbfunctions
Rob Goudvis | 3/27/2008 1:06:53 AM

I found a strange thing with column names: when you have an icon-column you cannot use a meaningful column name. It must be in the format $n. There will be no icon shown when you use any other type of name.

I tested this in version 7.0.2.

14) Thing Two of Three about @Dbfunctions
Erik Brooks | 3/27/2008 8:05:19 AM

IIRC Kevin's column-duplicate-$ problem was fixed either late R5 or in ND6.

15) Thing Two of Three about @Dbfunctions
Charles Robinson | 3/27/2008 8:59:00 AM

@14 - I'm fairly certain I had that happen in 6.5.<something>, but I remember whooping with glee when it *didn't* happen in a later release. It could have been 6.5.<something-later> or 7.x. I can say with complete confidence it's been fixed by in 7.0.2.

16) Thing Two of Three about @Dbfunctions
Andrew Brew | 4/3/2008 6:20:20 PM

Andre - this is great! The best tip I have picked up in ages on Domino Dev. I knew about the problem with having duplicate column names from pasting, but could never figure out how to use the column names in a positive way.

Many, many thanks for sharing this.

17) Thing Two of Three about @Dbfunctions
Not sure it is all fixed in 7.03... | 4/21/2008 7:03:11 AM

Hmm.. i'm not sure it is al fixed

@4 @8, I have just had the problem. Server and client running 7.03

Copied and pasted a column which had a formular. Changed the formular in the pasted column, saved and viewed the view. Both columns had the same programmatic name and both showed the result of the original formular. I discovered they had the same name so I changed the pasted columns programmatic name and I worked correctly now.

So I do think something is not working the way it is supposed to.

brgd Jesper Kiaer

{ Link }

 Add a Comment
Subject:
   
Name:
Comment:  (No HTML - Links will be converted if prefixed http://)
 
Remember Me?     Cancel

Search this blog 

Disclaimer 

    About IBM Privacy Contact