Notes Domino 6 Application Development 1
Notes Domino 6 Application Development 2
Notes Domino 7 Application Development 1


A More Efficient Data Lookup Approach

The @DBLookup function is a useful tool to return data from a document in a Domino database. However, performing multiple lookups to retrieve the values from several fields in the same document can be very slow. Consider a form which includes 5 fields whose values are retrieved using separate @DBLookup formulas. Every time a document is recalculated (when opened, refreshed or saved) each field's @DBLookup must access the database and then retrieve the field value from the view index or the source document. This happens 5 times! The following image shows the top portion of a purchase order. The user clicks the [Select a Customer] button and then selects a customer name in a dialog. This results in five separate @DBLookups to retrieve the selected customer's contact and shipping information. As you can see, this form does not even retrieve all the information that is required for a production application. If we were to continue with this approach, you would need to perform several more lookups to retrieve and complete all of the necessary field assignments for the order. Although this approach is easy and effective, it is not the most efficient.

The code for the Select a Customer action is shown below. Note that once the customer name is selected there are five lookups to retrieve information about this customer.

1.	server := @Subset( @DbName ; 1 ) ;
2.	sPath := @LeftBack(@LowerCase(@Subset(@DbName; - 1)); "7ad2wrkb.nsf" ) ;
3.	database := sPath + "7ad2cust.nsf" ;
4.	view := "CustomersByNameView";
5.	REM {Perform the @DbColumn and assign the result to an allClients temporary variable.};
6.	allClients := @DbColumn( class : "NoCache" ; server : database ; view ; 1 ) ;
7.	REM {Use @Prompt to prompt the user to select a client from allClients.};
8.	clientSelected := @Prompt([OkCancelList] ; "Client" ; "Select a client."; null ; allClients) ;
9.	FIELD Customer := clientSelected ;
10.	REM {Perform a series of @DBLookups using the selected client as a key.};
11.	REM {Lookup and assign values for these fields on the form: Contact, Phone, City and State.};
12.	FIELD Contact := @DBLookup( "Notes" : "NoCache" ; server : database ; view ; clientSelected ; "Contact");
13.	tmpAreaCode := @DBLookup( "Notes" : "NoCache" ; server : database ; view ; clientSelected ; "AreaCode");
14.	tmpPhone := @DBLookup( "Notes" : "NoCache" ; server : database ; view ; clientSelected ; "Phone" ) ;
15.	FIELD Phone := "(" + tmpAreaCode + ") " + tmpPhone ;
16.	FIELD City := @DBLookup( "Notes" : "NoCache" ; server : database ; view ; clientSelected ; "City" ) ;
17.	FIELD State := @DBLookup( "Notes" : "NoCache" ; server : database ; view ; clientSelected ; "State" ) ;
18.	@Command([ViewRefreshFields])

About the Fast Data Lookup Technique

A more efficient approach is to retrieve the entire set of customer data with a single @DBLookup. But, how can you do this when a single @DBLookup will return a single field or column value for each matching key document? The trick, or technique, is to return a column value that includes the entire customer record.

Examine the image below. The image shows the design of the CustomersByNameView, which is the lookup view in the Customer database. Hidden column five uses a string concatenation formula to build a complete record of customer information as a continuous text string.

This technique combines the power of @DBLookup with list manipulation techniques. It is very useful and has many applications.

Click here for more information on accessing lists via subscripts.

1.	server := @Subset( @DbName ; 1 ) ;
2.	sPath := @LeftBack(@LowerCase(@Subset(@DbName; - 1)); "7ad2wrkb.nsf" ) ;
3.	database := sPath + "7ad2cust.nsf" ;
4.	view := "CustomersByNameView";
5.	allClients := @DbColumn( class : "NoCache" ; server : database ; view ; 1 ) ;
6.	clientSelected := @Prompt([OkCancelList] ; "Client" ; "Select a client."; null ; allClients) ;
7.	@If( !clientSelected ; @Return("") ; @Success) ;
8.	custDetails := @DBLookup( "Notes" : "NoCache" ; server : database ; view ; clientSelected ; 5 ) ;
9.	custDetailsList := @Explode(custDetails;"¥");
10.	FIELD Customer := custDetailsList[2];
11.	FIELD Contact := custDetailsList[5];
12.	tmpAreaCode := custDetailsList[3];
13.	tmpPhone := custDetailsList[4];
14.	FIELD Phone := "(" + tmpAreaCode + ") " + tmpPhone ;
15.	FIELD City := custDetailsList[8];
16.	FIELD State := custDetailsList[9];
17.	@Command([ViewRefreshFields])

Line 1 through Line 4 set the server, database and view for the lookups. Line 5 executes @DBColumn to retrieve all the customer names from column 1 of the lookup view. Line 6 uses @Prompt to present all the customer names as a selection list of choices and then assigns the selected customer to the clientSelected temporary variable. Line 7 uses @Return to end the formula if the user cancels the dialog.

Line 8 performs the single @DBLookup, returning the all customer data as a continuous text string to the custDetails temporary variable.

Line 9 performs an @Explode on the text string, using the same separator character that the developer used to build the string in the column formula of the lookup view. The result is a list of customer values, the order of which is maintained and known from the column formula:

CustID+"¥"+Customer+"¥"+AreaCode+"¥"+Phone+"¥"+Contact+"¥"+Type+"¥"+Region+"¥"+City+"¥"+State

Because the substring to the first separator character is the CustID, the CustID is the first value in the exploded list. The next substring between the first and second separator character is the Customer; so the Customer name is the second value in the exploded list, and so on.

Line 10 through Line 16 parse out the customer data and assign the appropriate values to the fields on the form. The individual elements of the customer data are accessed using the list subscript operator. Line 16 refreshes the field formulas.

Tip Choosing and Creating a Separator Character You can use any character as your separator character for the string concatenation in the lookup column. Since you will be using @Explode to convert this string to a value list, it is important that you select a separator character that is not used in the actual field data. In this example, the yen ( ¥ ) character was chosen; but a tilde ( ~ ) would also effectively serve as the separator character. To produce special characters that are not readily available on your keyboard, you can use one of two techniques: copy and paste the special character from a character mapping utility like the Character Map accessory applet that comes with the Win32 operating systems, or, enter the correct character number on the your keyboard number pad while holding down the <ALT> key. When you release the <ALT> key, the numbered character will be produced. To do this, you will need to know the character numbers for the character code page that your system is currently using. For example, to produce the yen ( ¥ ) symbol when using the multilingual codepage, type <hold ALT> 157 <release ALT>. You must use the number keypad to enter the character number.

Tip Data Limits on @DBLookup Starting with Release 6, @DBLookup and @DBColumn can return data in excess of 64KB. However, the maximum size that can be assigned to a field (besides rich text) is 64KB. However, for a field to display in a view it must be smaller than 32KB. Therefore the 64KB limit is not really an issue when using this technique. Note that a regression bug was introduced in 7.0.1 that reintroduced the 64KB limit. This bug will be fixed in 7.0.2 and also in a fix pack for 7.0.1 due out in 2Q06.