Thing Two of Three about @Dbfunctions

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?"

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:

@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.