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

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

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

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.
Note: "NoCache" ignores the lookup cache. If the same lookup was already cached, the cache is not updated. "ReCache" does not use the cached result, it stores its result in the cache when it is done. If you later do the same lookup with default caching, you get the result that was stored by "ReCache."

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 or folder in which to search. The view name must exactly match the view's full name as specified in the view InfoBox (you can omit any synonyms). If the view cascades from another name on the menu, include that name too. See "Specifying the view."

key

Text, number, date/time, or a list of any of these types. Determines which document is actually read in order to retrieve a value. A document's key is the value displayed in the first sorted column within the view. Note that if the column is re-sortable, results will depend on the current sort order. See "Specifying a key."

fieldName

Text. The name of the field from which the data will be retrieved, once the correct document has been identified. See "Specifying a field name."

columnNumber

Number. When you use a column number, Domino® finds all documents in the view that match the specified key, and returns whatever value is displayed in the indicated column for each of those documents, regardless of the formula used to display the data. See "Specifying the column number."

keywords

Note: This parameter is new with Release 6.

Keyword. Optional. Keywords can be concatenated.

  • [FAILSILENT] returns "" (null string) instead of an error if the key cannot be found.
  • [PARTIALMATCH] returns a match if the key matches the beginning characters of the column value.
  • [RETURNDOCUMENTUNIQUEID] returns the UNID of the document instead of a field or column value.

Return value

valuesFound

Text, numbers, date-time, or text-list. The values found in the fieldName or column you indicated, or the UNID of the document. See "Accessing the return values."

If no documents in the first sorted column match the key, @DbLookup returns an error, "Entry not found in index", which you can test for with @IsError or @IfError.

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 lets you 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. Then copy the replica ID from the synopsis and paste it into your formula.
  • If your database is located in DOS, put a double backslash between the directory and the database name, as in "MAIL\\MINE.NSF" because formulas treat single backslashes as escape characters.

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 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:

  • Finds the ContactList view of the Notes® database Customers.nsf on the Sales server.
  • Locates the first sorted column containing the key "Susie Queue."
  • Extracts the text strings displayed in the Comments column for each document containing the "Susie Queue" key.
  • Returns the extracted list of comments to the lookupComments field. If more than one document was accessed, the strings returned are separated by a semicolon.

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

The type of the key must match the type of the data in the sorted column, or no match will be found.

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.

When using a number or date/time key, the key value must match the column value exactly. Remember that the column "style" settings can be used to hide information; so for instance, the column might contain a date and time, but only display the date. To lookup in that column, you have to supply an exactly matching date/time value, not just the date that is visible.

When your key is a list of values, the result is the list-concatenation of all the matching row entries. For example, if lookup of key "Sam" returns 7 and "George" returns 19 : 4, then the key "Sam":"George" would return 7:19:4.

When using a list of keys, if the first key doesn't match a view entry, the lookup fails, even if other keys do match. If the first key finds a match, however, subsequent non-matching keys will just be ignored; they won't cause an error.

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

Note: If you supply a string value to the fieldname value, you are first looking for the column name (also known as the Programmatic Name) which you can find on the Advanced tab of the column properties. If there is no matching column, look for an item with the specified name. If you use the Advanced tab of the column properties to discover the name of the column you would like to read in your lookup, you can use that column name instead of the column number. To get the best performance from @DbLookup, change the programmatic name of the column to some unique name that describes its contents, then use that name instead of the column number as your lookup argument.

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

    Be sure you don't miss any columns, for example, a column used for sorting or categorizing the view may not show up. Look at the view in design mode to make sure you see all its columns.

  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.

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

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.

Examples

  1. Your organization maintains employee office location and department information in the Person documents in the public Name & Address Book.

    You might have a Purchasing application where employees fill out Purchase Requests for office supplies. You can have your Notes® application look up this information and automatically insert it into documents.

    Mary Tsen composes a Purchase Order. The P.O. Number, Date, and Requested By fields are filled in automatically by Notes®. Mary fills in the details of the purchase order: quantity, part number, and so on.

    When Mary saves the Purchase Order, the delivery information in the document is calculated using a series of @DbLookup formulas to retrieve information about that user from the public Name & Address Book:

    This is accomplished by using computed fields and writing a lookup formula for each field to be retrieved (Location and Telephone). For example, the formula for the Location field would be:

    @DbLookup("";"Purchasing":"Names.NSF";"People"; @Right(RequestedBy; "");"Location")

    This formula instructs Domino® to open the Name & Address Book (Names.NSF) on the Purchasing server, locate the People view, and then locate the person whose last name matches the last name in the purchase order's RequestedBy field. Once the correct document has been located, Domino® copies the information from the Person document's Location field into the purchase order Location field.

    A similar formula then copies Mary's telephone number from the Person record OfficePhoneNumber field into the purchase order Phone field.

    Note: For the DeliverTo field, Mary's name is determined when the document is composed, using @UserName.
  2. Using the Name & Address Book again, you want to retrieve a list of office telephone numbers for everyone in the Purchasing department.

    You could use @DbLookup with the key "Purchasing" to retrieve the OfficePhoneNumber field, and Notes® would return the telephone number for every employee with "Purchasing" entered in the Department field of their Person record. The phone numbers are returned as a text list, using the selected multivalue separator for the field.

  3. This formula returns the value stored in the Status field of the Virus Check document, which is accessed via the In Progress view of the PROJECTS.NSF database stored in the SMITH subdirectory on the RESEARCH server. The information will not be cached, so if this formula is evaluated again during the same Notes® session, a new lookup will be performed to ensure that the status retrieved is up to date.
    @DbLookup("":"NoCache";"RESEARCH":"SMITH\\PROJECTS.NSF"; "In Progress";"Virus Check";"Status")