Lotus Forums

"I don't want you to book any more appointments with her."
"Why?"
"Because she's crazy!"
"You're a psychiatrist! All your patients are crazy!"

Before you point out the obvious, let me assure you that I know that many people who see psychiatrists are not really "crazy," and it is not a word I would choose to describe patients today. But I was very young at the time, and totally incredulous. Which brings me to my next point.

On occasion I have had the opportunity of asking end users if they find our Help useful. On more than one occasion, the conversation has gone like this:

"Do you use the online Help?"
"No, not often"        
"Why?"
"Because, there are too many words!"        

Now I don't know how you write help without using words, but I can appreciate the sheer volume and sensory overload when you open some (not all) of our Help topics. So I decided to take an objective look at some of my Help topics, and see if I could do away with lots of words, without impacting the information that was really needed. Looking around for a likely candidate, I decided it was time for an overhaul of my Domino Web Access Help.

I really like Domino Web Access. It is a quick way to get to your Notes mail and calendar using a browser. And now that there is a new Lite mode (introduced in Notes 8.0.1), it is a really  quick way to stay in touch on the road, without being bogged down by the full feature set you might need in your permanent office. Unfortunately, as the Full mode of Domino Web Access has grown and added features over the years, so has the Help. And now it does indeed appear that there are "too many words."

So I am reorganizing and reconstructing the Domino Web Access Help to provide users with a more streamlined set of topics that include some (but not a lot) overview information, a set of task topics that address the most frequently used tasks, and a set of frequently asked questions. Then, just for good measure I plan to throw in some reference topics (sort of like reference cards only in a Help topic). The end result, I hope, will be a new streamlined Help that provides a easy-to-find source of information--without using too many words.

It's kind of confusing how this stuff works, and I've been looking into it recently. I decided to write it down here so I can look it up when I forget. Here are some relevant facts:

User-defined columns can use any of the display options. If you read the Designer help you might come away thinking that you can only use them for color columns. In fact, they can also be icons, or just display characters like a "normal" column. They use a formula which is stored in text item (not formula datatype) in a shared profile document. Because of this, it is not possible to have different preferences for different users. It's user-defined in the sense that it's easy for an authorized end-user to edit the profile document and thus change the behavior for everyone -- not in the sense that one can potentially see a personalized value.
..............	The name of the profile document is a column property. All the user-defined columns in a view have to use the same profile document. If you enter different profile document names in different columns, you don't get an error message warning you of an incorrect configuration -- it just doesn't work. The name of the item in the profile document is the same as the column programmatic name (in the Advanced tab of the column properties). You don't have to accept the default column name (which is of the form "$" followed by a number), but if you change the name, the new name also has to start with "$". This is not a general rule about column names -- it only applies to user-defined columns. If you enter a name that doesn't start with $, you don't get a warning -- it just doesn't work.
The formula for the column must be non-constant, because the indexer ignores columns with constant values. IBM uses @UserName as the formula for such columns, and that's misleading because it implies some personalization is happening, which as we have seen is not the case. There's no reason you shouldn't use @UserName -- it's non-constant, which is all we require. If you do put in a formula that returns a constant value, you are not warned -- it just doesn't work. Some functions you would think would be considered variable, like @Unique, are treated as constant for this purpose. Bottom line -- use @UserName, it's safe, but remember that it's just a placeholder -- not actually used for anything. The value of the column is calculated by the server -- not by the user's workstation -- as part of the normal view indexing task (unless of course it's a local replica). Therefore, you can't do anything in the formula from the profile document, that you can't also do in a regular column formula. It's just more customizable -- not more capable in any way.
	In particular, @UserName and other user-based functions, don't work any better than they would in a "normal" column formula. Nor can you use @DbLookup or @GetProfileField or any of those other things people often want to do in a column formula and can't. You can use @Now and @Today in these functions without causing the view to rebuild every time you use it, but this is just like using @ToTime("Today") -- you are fooling the indexer so that the view also doesn't rebuild when the row values of previously indexed documents become obsolete. Result: your view contains out-of-date information. User-defined views do not help you do anything date-related that you couldn't do with normal columns.
The server (or your workstation if it's a local replica) does notice when the profile document has changed, and rebuilds the view index. So your changes get applied right away to documents that were previously indexed. I believe this also means that view indexes may be rebuilt unnecessarily if you modify the profile document for some other reason. I'm not sure whether the indexer pays attention to the item modification time. My guess is not. User-defined columns are slower to index than regular columns, but I think not very much slower. There's overhead in the setup for indexing, where the indexer has to read two notes (the view and the profile) instead of just the view note. But once it's got the formula in memory, I don't see why it should be slower to execute this formula on each document, than would a normal formula.

My first suggestion might seem a little obvious, but I mention it because it surprised someone I thought would find it "old hat." If you use @Unique to remove duplicate values from a list, that's always a danger signal. @Unique(@DbColumn...) in particular, can be improved on. Executing @Unique on a long list is expensive because it has to compare each element with each other element, with an execution time probably proportional to the square of the number of elements (O(n*log(n)) is possible in theory). Add to that the amount of time it takes @DbColumn to scan all the entries in a view, compile a list of values from a column, and (if using a server database) transmit information over the network to the client, and you'll see there's a lot of wasted time creating a list most of whose elements you're going to throw away anyway.

Far better if you can get a list whose elements are already unique. One way to do this is with a categorized view. Assuming there are far fewer categories than there are documents, it's a lot faster for @DbColumn to just scan through the category headings, compared to a regular view where it has to scan every row. Use @DbColumn to read a categorized column, and you don't need to use @Unique on it after.

One problem, though; categorized views are more expensive for the server to maintain, and their stored index is larger, than the same view without categories. We're only interested in the list of 100 unique values. Isn't there any way to get that without all the overhead of listing 30,000 documents in the index?

In fact, there is a way, or I probably wouldn't've brought it up. In the Advanced tab of the view properties, find the option "Generate unique keys in index". If you tick this box, Notes will show only one document per unique key value (the key consists of all the sorted columns). When a document is created or modified, if a document with the same values in the sorted columns is already present in the index, the document will not be added to the view. If there are many duplications, this saves a lot of space (though it's maybe only a little faster to index than a regular sorted view).

Using Domino Administrator, you can see the sizes of view indexes. A view with "normal" sorting, had a size of 1021KB. The same view with the sort column categorized instead, was 1297KB -- a little larger because it has to store the category rows and the document rows. But if instead of categorizing, I set the "unique keys" option, I can get the index size down to 118KB. Your mileage may vary, depending how many documents use the same key.

There are a few things to watch out for with this feature:

• You can't predict which document will end up in the view, among the ones with the same key. It's not necessarily the oldest, or the newest, or the most recently modified, or any such thing. If you're just reading the unique values off the sorted column, of course, it doesn't matter.
• If you have any replication/save conflicts, you must exclude them from the view using a selection formula such as SELECT Form = "Something" & @IsUnavailable($CONFLICT). Otherwise, if any of the conflict documents end up in the index, they will not be visible because they are response documents whose parents are not in the view. Setting the view to not display hierarchically should also do the trick.
• If a document is deleted, you might expect another document with the same key value to take its place in the view. Alas, this does not occur. The view indexer has already considered those existing documents for inclusion in the view, and rejected them because the key was already there. Now that the key is no longer there, Notes doesn't hunt through the already-rejected documents for a replacement. Documents are considered for inclusion only when they are modified or created. So, if in your application the documents you're looking up might be deleted or their keys changed, this isn't an appropriate solution for you -- unless you can arrange to have the index rebuilt each time this occurs. For instance, if the documents are deleted by an archiving agent, have that same agent issue a NotesView.Rebuild against the view, or schedule the index rebuild as a periodic server task timed for after the agent runs. A refresh isn't good enough -- it has to be a rebuild.

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:

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.

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

No. 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:

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.