Using View Column Programmatic Names 

This article is about the view column property "Programmatic Name," shown here, when and how to use it, and what problems to watch out for. Some odd things can happen as a side effect of using this property, as suggested in the caution under the field. It's important to understand what those are.

Even if you don't intend to use programmatic names yourself, it's good to understand their use, since you might encounter them when maintaining designs created by others, and they can have some surprising effects.

Background

There are three styles of column Simple Function, Field, and Formula:

Simple Function and Formula columns are automatically given a programmatic name of the form "$" followed by a number. Columns that just refer to a field, are automatically assigned a name which is the same as the name of the field. Occasionally you will see a column where Formula is selected in the radio button, but the formula is still just a field name:

This is still treated as if it were a "Field" column; the reason for the difference in the UI is that the field isn't in the field table of the Notes application as yet -- it doesn't appear on forms. For the purposes of this article, though, this is still a "field column."

Similarly, there's no real difference between "Simple Function" and "Formula" columns; Simple Functions are just Formulas that are recognized as special by the UI and presented in a selection list. Behind the scenes, Designer stores them in the same way, as a formula. So I will refer to these collectively as "formula columns".

Best Practice Recommendations

We suggest the following practices for making best use of this option:

• Avoid changing the Programmatic Name value from its default without a specific reason.
• Never change the Programmatic Name of "field" columns; there's no benefit to doing so.
• When you do change the Programmatic Name, use a naming convention that will prevent any possible collision with field names. For instance, use an initial underscore ("_") on all column programmatic names (and never on field names).
• In cases where columns are behaving oddly, for instance if the column is showing a value that seems totally unrelated to the formula in the column, delete the programmatic name, leaving that field blank. Designer will assign it a new, unique default name, and that will probably correct the problem. These issues are often caused by two columns having the same programmatic name, or by a column having the same programmatic name as a field in your documents. NOTE: make sure the programmatic name you delete isn't referenced elsewhere in the design!

Uses Of Programmatic Name

Column programmatic names may be of use in the following situations (possibly among others):

• They can be used as the column number/name argument of @DbLookup (but not @DbColumn, unfortunately).
• They can be used in the column formulas of other columns to refer to the value in that column, improving view indexing performance by avoiding recalculating the same complex formula in two columns.
• When defining a column with "Names" styling to activate Sametime integration, you can use the programmatic name to refer to a different column that actually contains the name.
• NotesSQL uses them to refer to view columns in its SQL statements.

@DbLookup usage

As an example of this usage, you might write the following to look up view rows by key and read all the values of the column named $12 from the matching rows:

This is generally preferable to using the column number for the following reasons:

• The column name doesn't change if columns are added or removed from the view, whereas the column number obviously might, so this is more maintainable.
• By giving the column a meaningful name rather than accepting the default name, you can make your code more readable.

By the way, you may have seen a performance tip for @DbLookup that says that it's more efficient to use a column number rather than a fieldname for the final argument. This is a good "beginner story" but it's not the whole story. It would be more accurate to say that it's more efficient for the last argument to refer to a column -- by number or by name -- than to refer to a field name that doesn't correspond to a column. That's because if you use a name, the @DbLookup function will first scan through the column names; if the given name matches one of those column names, it returns the value from the view index. If it doesn't match one of those names, @DbLookup has to do the extra work of accessing each matching note to read the field value from the note. Because "field columns" have the same name as the field by default, often you may think you're using the field name when you're really using the column name.

Use in column formulas

A column can use the programmatic name of any formula column that appears to its left to refer to the value in that column (assuming left-to-right reading order). This is occasionally useful to avoid recalculation of a complicated value. For instance, suppose we have a column with programmatic name "_Dept", containing a complex @If formula:

Now, another column can use the name _Dept in its formula, to refer to the value in this column.

Incidentally, if you want to use an intermediate result in multiple columns, without having to have a column that contains just this value, you can do this using the FIELD statement in a formula column (or in the selection formula). So for instance, you might add this statement to the selection formula:

and then in all the column formulas, the name _dept is available for use in formulas. You must use the FIELD keyword for this; assigning a temporary variable will not work across multiple columns, only within the same column formula.

Note: The view indexer doesn't modify documents, so using FIELD here will not result in the value being stored in the document.

Issues/Surprises

The use of programmatic names can cause odd side effects in two cases:

• If the programmatic name is the same as the name of a field in your application.
• If there are two columns in the same view with the same programmatic name, and neither is a "field column."

In the first case, you will find that a column with a given programmatic name will always display the field value with the matching name, even if it's a formula column. In this example:

the column displays the value of the Author field, because the programmatic name of the column is Author. The formula is ignored except on one row, where the document doesn't contain an Author field.

This is perplexing for developers, who naturally expect that when they write a formula for a column, the formula is actually used to calculate the value for that column. Lotus is debating making a change here. Meanwhile, though, you can avoid the situation by adopting a naming convention that prevents having duplicate column and field names. This is a good idea in any case, so that people who maintain your applications can readily distinguish them when you use them in formulas.

Your column formula can also get ignored if there is another formula column with the same programmatic name. This occurs because the view indexer concatenates all the formulas into one long formula with temporary variable assignments, and then evaluates that formula. So say you have a column with a programmatic name of "_eep" and formula @AttachmentNames, and another column also named _eep with formula @Name([CN]; Owner). The two columns will display the same value (the one associated with the leftmost of the two, is what it looks like).

Bonus Capability

To reward those readers who have made it this far, I'll mention a technique based on the above for maintaining multiple hierarchical relationships of documents. One problem with main document/response hierarchies, is that you can only have one parent for each response. Often, you would like to build a view that lists documents in a different hierarchy based on which view it is; for instance, in one view you want to list Product as the main document, and under each Product document, you can expand to see the OrderLineItem documents for that product. In another view, however, you want to show the OrderLineItems as responses to Order documents.

You can do this by assigning the $REF field in the view selection formula. You would of course have to have other fields to assign them from, since it is a value of an unusual datatype. So you might use LotusScript when editing an OrderLineItem document to create a $RefOrder item linking it to the Order document, and a $RefProduct item linking it to the Product document. Then in the view of Products you might have:

and likewise in the Orders views, to create the desired relationship on the fly.

To create the $RefXXX items, use the NotesDocument.MakeResponse method to create a $REF item, then copy the item to the desired name and delete $REF.