FORMULA LANGUAGE

@DbLookup (Domino data source)
Example

Given a key value, looks in the specified view (or folder) and finds all documents containing the key value in the first sorted column within the view. For each selected document, @DbLookup returns either the contents of a specified column in the view, or the contents of a specified field.

Syntax

@DbLookup( class : cache ; server : database ; view ; key ; fieldName ; keywords ) or
@DbLookup( class : cache ; server : database ; view ; key ; columnNumber ; keywords)

Note The separator between the class and the cache string arguments as well as the server and database are colons; the rest of the separators are semicolons.

Parameters

class


cache server : database
view
key
fieldName
columnNumber
keywords Return value

valuesFound


Specifying the server and database

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


Notes
Specifying a view

You can specify a view parameter using either the full name of the view (or folder) or its synonym. For example, if your Last Name view is cascaded from By Author in the View menu, and has the synonym |LName, it looks like this in the view InfoBox:

By Author\Last Name|LName

When you reference this view with @DbLookup, you can just use the LName synonym, enclosed in quotation marks:

"LName"

If the view name doesn't have a synonym, you 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 a key

You can only test for values that match the key (equality); there is no way to specify a different operator such as < (less-than).

In addition to specifying a constant as the key to be matched, you can also use the value of an editable field. For example, you could create a ContactInfo form that contains two fields: a contactName field and a lookupComments field. You want a user to be able to enter a contact name in the contactName field and have the lookupComments field display a list of comments associated with the contact that the user supplied. To do so, you could make the contactName field an editable text field (or a choice list field such as a Dialog list field). The lookupComments field could contain the following code as its Input validation formula:

@DbLookup("":"NoCache";"Sales":"Customers.nsf";"ContactList";contactName;"Comments")

When a user enters or chooses the customer name, "Susie Queue," for instance, in the contactName field of the ContactInfo form and presses F9 to refresh the document, the formula in the lookupComments field performs these tasks:


By specifying the field contactName as the key, whenever the @DbLookup formula is executed, the current value of the contactName field is used as the lookup criterion.

The match between the lookup key and the value in the sort column must be exact -- capitalization doesn't matter, but spacing and punctuation must be precise. The match must be complete unless you specify the [PARTIALMATCH] keyword.

The view must contain a sorted column in order for the lookup to work; otherwise a null value is returned. Results are not accurate for a multi-value field that is sorted but not categorized. If the column can be re-sorted, results will depend upon the current sort order.

Specifying a field name

When you use a fieldName to perform a lookup, the value returned is the value that is actually stored in the field; it may be different from what displays in the view. Lotus Domino can retrieve data from any field in any document displayed in the specified view, but if the field isn't displayed as a view column, Lotus Domino must search the entire document to find the field, which may result in a slower lookup. You cannot retrieve data from a rich text field using @DbLookup.

Some of the documents matching the key may not even contain the specified field if they were created using different forms.

Specifying the column number

Lookups based on view columns are more efficient than those based on fields not included in the view. For best results, you should include the desired field in the view.

For example, if your view is categorized by product ID and you specify "01776" as the lookup key and 2 as the column, Lotus Domino returns whatever is displayed in column 2 for all documents categorized under product ID 01776.

To specify a columnNumber parameter, you count the view's columns from left to right, with the leftmost column being number 1. Because of the way Lotus Domino indexes views, however, not every column is counted for the lookup.

Use this method to calculate the column number for lookup purposes:

1. Count the columns in the view, from left to right.


2. Discount all columns that display a constant value, such as 32 or "Submitted by: ." 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.


Note If you choose to use a column number instead of a field name in an @DbLookup formula, you can only retrieve data that actually appears in the view.

Accessing the return values

If multiple values are returned by @DbLookup, they are formatted as a list and are separated with the multi-value separator designated in the current field's InfoBox.

@DbLookup can return no more than 64KB of data. Use the following equations to determine how much of your data can be returned with @DbLookup.

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

This function does not work in column or selection formulas, or in mail agents.

Server agents and security

Consider the database containing @DbLookup the source database, and the database being accessed the target database.

When you use @DbLookup 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. 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 @DbLookup 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, @DbLookup's access is determined by the agent signer's access level (based on the user's Notes ID).

@DbLookup is subject to the Read Access list for a view.

Example
See Also