Working with Lookup Functions
Lookup functions are used to retrieve one or more data values from a Notes database. The data values can be retrieved from the current or another database. The database can reside on the current or a different Domino server. Lookup functions are quite frequently used to populate keyword fields or to default computed fields to a specific value.
|
A.5.1 |
|
Function |
Description |
Example |
|---|---|---|
|
@DBColumn |
Returns a list of values from a view or folder column. Syntax: @DBColumn( class : cache ; server : database ; view ; columnNumber ) classThe type of database being accessed. cacheSpecifies if the results should be cached. serverServer name. Defaults to current server if set to "" in the statement. databaseDatabase name. Defaults to current database if set to "" in the statement. viewThe view name or alias name. columnNumberThe column number to return values. |
The following returns a list of values from column number one for the "By Category" view. class := ""; cache := "NoCache"; server := ""; db := ""; view := "By Category"; colnum := 1; @DBColumn (class:cache; server:db; view; colnum); |
|
@DBLookup |
Returns a field value from a specified document in a view or folder. Syntax: @DBLookup (class: cache; server: database; view; key; fieldName; keywords) -or- @DBLookup (class: cache; server: database; view; key; columnNumber; keywords) classThe type of database being accessed. cacheSpecifies if the results should be cached. serverServer name. Defaults to current server if set to "" in the statement. databaseDatabase name. Defaults to current database if set to "" in the statement. viewThe view name. keyUnique key. fieldnameValue to return. columnNumberThe column number to return values. |
The following searches the view "By Last Name" for the key value. If found, it returns the first name for the document. class:= ""; cache:= "NoCache"; server:= ""; db:= ""; view:= "By Last Name"; key:= "Elliott"; fieldName:= "firstname"; @DBLookup (class:cache; server:db; view; key; fieldName); |
Tip
Best practices suggest that you should create temporary variables for functions that require multiple parameters. This helps with formula readability and helps ensure that you change the correct parameter if the formula needs updates. Otherwise, you'll need to remember (or look up) what each parameter means and count semicolons to find the parameter.
Where possible, you should limit the number of lookup functions included on a form. Multiple lookup functions can affect the overall performance of the form.
However, if you need to retrieve multiple field values, consider creating a hidden view. Set the first column to a unique key (such as a document number). In the second column, create a formula that contains all data fields separated by a delimiter (such as the tilde ~). Then using @DBLookup in conjunction with @Word, you retrieve multiple field values for a single document using a single lookup.
For example, let's say you have a service request form that requires the user to select their immediate manager. Then, after the user selects a name, the manager's email address, department name, and phone fields automatically populate with the correct information. In this scenario, you would do the following:
|
1. |
Create a form that contains manager name, email, department, and phone number.
|
|
2. |
Create a hidden view, such as (MgrLookup).
|
|
3. |
Set the view formula for column1 to MgrName.
|
|
4. |
Set the view formula for column2 to MgrEmail+"~"+MgrDept+"~"+ MgrPhone.
|
|
5. |
Next, add a hidden, computed field (such as MgrData) to the service request form. Set the field formula to the following @DBLookup formula. This field will store a "~" delimited list of values based on the selected manager name.
Class:="" Cache:="" Host:= ""; View:= "(MgrLookup)"; Key:= MgrName; Colnum:= 2; output:= @DbLookup(class: cache; host; view; key; colnum); @If(@IsError(output);"";@If (MgrName=""; ""; output)) |
|
6. |
Finally, create the Email, Department, and Phone fields on the service request form. Make each field computed. Using the @Word function, set the field formula for each respective field. Be sure the form property is set to Automatically refresh fields.
@Word (MgrData; "~"; 1); @Word (MgrData; "~"; 2); @Word (MgrData; "~"; 3); |
Using this approach, three individual field lookups were replaced with one @DBLookup function call. Note that Automatically refresh fields can also affect performance if there is a significant number of computed fields on the form.