Working with @DBLookup

The @DBLookup function can be used to dynamically populate a field with values. In other words, the options displayed in a radio button, checkbox, listbox, prompt, or other design element can be dynamically managed without having to change the design of the database. Using this function, the values are retrieved from a single document, as opposed to @DBColumn, which retrieves a list of values from a column in a view.

How It Works

The @DBLookup function is used to return a field value from a specific database document. This approach can be used when you need to retrieve values from one document and utilize them in another document.

For instance, say you have a worldwide service request database with one or more project managers assigned to each country (or region, state, district, etc.). A view could be created to store the contact information for each country. As new document service requests are created, the fields on the form (such as the project manager's email address) could automatically populate based on the originating country of the request. In this scenario, the service request document queries the specified view for a document and retrieves the value(s) of a specific field.

Implementation

Step 1.	Create (or determine) the view that contains the target documents to be searched. Also identify the criteria, or key, that will determine which document will be accessed by the @DBLookup formula. In many cases, the "key" will probably be another field on the document, but it could also be a hard-coded value. For example, in Figure 14.15, the country field is used as the key. This value points to the North America document. The values in the Email field are then returned by the lookup.   Figure 14.15. Use @DBLookup to retrieve a field value from a specific document
Step 2.	Create a field. Using the properties dialog, set the field type (such as radio button, combobox, checkbox, or listbox) in tab 1. In tab 2, change the Choices option to Use formula for choices and insert the following formula in the dialog box. Be sure to adjust the formula to include the actual view name, key, and field to retrieve. Close the properties dialog when finished.   Class :=""; Cache := "NoCache"; Host := ""; Viewname := "VIEW"; Key := "KEY"; Fieldvalue := "FIELDNAME"; @DbLookup(Class : Cache; Host; Viewname; Key; Fieldvalue)

Note

Use the cache parameter to store the values returned in memory. This option is more efficient and should be used when the results from the lookup do not frequently change. Using the Nocache parameter forces the data retrieval every time the @DBLookup or @DBColumn function is called.

Parse a Text String