@DbColumn (Domino® data source) (Formula Language)

Syntax

@DbColumn( class : cache ; server : database ; view ; columnNumber )

Parameters

class

Text. Indicates what type of database you are accessing. You can indicate a Domino® database with either "Notes®" or "" (null string).

cache

String argument. Optional. In the initial lookup, specify either "" or "NoCache." If the former case, subsequent lookups to the same data source, you can specify "ReCache."

• "" (null string) caches the results of the lookup. Each subsequent lookup to the same location (within the same Domino® session and so long as the database executing this lookup remains open) reuses that data until you specify "ReCache." Cached data improves performance and may be a good choice for stable data.
• "ReCache" refreshes the cache with the latest data from the database. If you want to ensure that this lookup gets the latest information, specify this option.
• "NoCache" gets the results of the lookup from the database; no cache is used. If you want to ensure that Domino® retrieves the latest information for every lookup, specify this option.

server : database

Text list. The server location and file name of the database. See "Specifying the server and database."

view

Text. The name of the view in which to search. The view name must exactly match the full name of the view as specified in the View properties (you can omit the alias). If the view cascades from another name on the menu, include that name, too. See "Specifying the view name."

columnNumber

Number. The column number within the view. Because Notes/Domino looks up information in the view based on column numbers, you can only retrieve data that actually appears in the view. See "Specifying the column number."

Return value

valuesFound

Text, numbers, date-time, or text list. The values found in the view column that you indicated. See "Accessing the values found," later in this chapter. If the column is re-sortable, results will depend on the current sort order.

Specifying the server and database

There are several ways to specify the server : database parameter:

• To perform the lookup on the current database (the same database in which the formula is being evaluated), specify "" as the entire argument to the function. "" means the local Domino® directory where you are executing.
• To perform a lookup on a local database, use "" for the server name and specify the database name explicitly, such as "":"DATABASE.NSF."
• To perform a lookup (from the workstation) on a Domino® database that resides on a server, include the server plus the path and file name as a text list, as in "SERVER":"DATABASE.NSF."
• If there are multiple copies of the database located on various Domino® servers, using the database replica ID in place of both the server and database name allows you to access a replica copy of that database without having to specify either the server name or the database name. For example, if you use "85255CEB:0032AC04" (a database replica ID, found in the database InfoBox) as the database name, Domino® uses a replica of the database to retrieve the information.

Domino® searches for replicas in this order, using the first replica it encounters:

• Workspace
• If there is one replica on your workspace, Domino® uses it.
• If there are multiple, stacked replicas on your workspace, Domino® uses the first replica on the stack.
• If there are multiple, unstacked replicas on your workspace, Domino® looks for an icon matching your current server and uses that. If none of the icons matches your current server, Domino® uses the icon that was added to your workspace first.
• Current® server
• Locally (your hard disk)

  Once a replica is located, it's added to your workspace to save time on future lookups.

Notes®

• To avoid typing errors in the replica ID, choose File - Database - Design Synopsis and select Replication. You can then copy the replica ID from the synopsis and paste it into your formula.
• If your database is located in a DOS or OS/2 subdirectory, such as MAIL\MINE.NSf, put a double backslash between the directory and the database name, as in "MAIL\\MINE.NSF", because formulas treat backslashes as "quote" characters.

Specifying a view or folder

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 Domino® interprets it correctly:

"By Author\\Last Name"

Specifying the column number

To specify a columnNumber parameter, you count the view columns, with the first column being column number 1. Because of the way that 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. 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 first to last.

This revised column number is the value to specify in the lookup formula.

If you specify a non-existent 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 hard-coding a list of keywords and then periodically updating that list by re-editing 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.

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.

Examples

@DbColumn("";"":"INVENTRY.NSF";"Inventory On Hand";2)