FORMULA LANGUAGE
Returns a column of values from a view or folder in a Domino database.
Syntax
@DbColumn( class : cache ; server : database ; view ; columnNumber )
Note The separator between the class and cache arguments as well as the server and database arguments is a colon; the rest of the separators are semi-colons.
Parameters
class
valuesFound
There are several ways to specify the server : database parameter:
Once a replica is located, it's added to your workspace to save time on future lookups.
You can specify a view (or folder) parameter using either the full name of the view or its synonym. For example, if your Last Name view is cascaded from By Author in the View menu, and has the synonym |LName, the name looks like this in the view InfoBox:
By Author\Last Name|LName
When you reference this view with @DbColumn, you can simply use the LName synonym, enclosed in quotation marks:
"LName"
If the view name doesn't have a synonym, you can use the By Author name plus the Last Name cascade, again enclosed in quotation marks (but without the synonym). And since the view name is used in a formula, the "\" must be preceded with an additional "\" to ensure that Lotus Domino interprets it correctly:
"By Author\\Last Name"
Specifying the column number
To specify a columnNumber parameter, you count the view columns from left to right, with the leftmost column being column number 1. Because of the way that Lotus Domino indexes views, however, not every column is counted for the lookup.
Use the following method to calculate the column number for lookup purposes:
1. Count the columns in the view, from left to right. Look at the view in design mode to make sure that you see all the columns, including columns used for sorting or categorizing the view.
2. Discount all columns that display a constant value, such as "Submitted by:" or 32. If a column contains a formula that happens to return the same result for every document, it is not considered a "constant", so be sure to include it in your column count.
3. Discount all columns that consist solely of the following @functions: @DocChildren, @DocDescendants, @DocLevel, @DocNumber, @DocParentNumber, @DocSiblings, @IsCategory, @IsExpandable.
4. Now recount the columns, working from left to right.
This revised column number is the value to specify in the lookup formula.
If you specify a nonexistent column, you don't get an error, but rather a null value.
Accessing the return values
If multiple values are returned by @DbColumn, they are formatted as a list and are separated with the multivalue separator designated for the current field in the field InfoBox.
@DbColumn can return no more than 64K bytes of data. Use the following equations to determine how much of your data can be returned using @DbColumn.
For lookups that return text:
2 + (2 * number of entries returned) + total text size of all entries
For lookups that return numbers or dates:
(10 * number of entries returned) + 6
Usage
@DbColumn is intended mainly for use with keyword formulas. Instead of hardcoding a list of keywords and then periodically updating that list by reediting the form containing the keyword field, @DbColumn allows you to dynamically retrieve a list of values from a database view or table.
This function does not work in column or selection formulas, or in mail agents.
Server agents and security
Consider the database containing @DbColumn as the source database, and the database being accessed as the target database.
When you use @DbColumn in an agent, it can access data in a target database that is running on either the same server as the one hosting the source database or another server. The agent signer must have at least Reader access to the target database.
Note Agents running on R5 or earlier servers can only access target databases stored on the same server as the source database. In addition, the agent signer must have at least Reader access to the target database. The use of a replica id in the acl is still supported in Release 6 and later. If the agent signer is not available in the acl of a pre-Release 6 database and the replica id is, the replica id is used instead. (You grant access to the source database by adding the replica id of the source database, for example 85255CEB:0032AC04, to the ACL of the target database and assigning it Reader access or higher.)
Other agents and security
When @DbColumn is used in any other type of formula or agent, it has unlimited access to any target database stored on the user's own workstation. If the target database is stored on another Domino server, the access for @DbColumn is determined by the user's own access level (based on the user's Notes ID).
@DbColumn is subject to the Read Access list for a view.
Language cross-reference
ColumnValues property of LotusScript NotesDocument class
GetColumn method of LotusScript NotesView class
ColumnValues property of Java Document class
getColumn method of Java View class
Example See Also