Defining lookups
Important  Before you begin, be sure you understand how lookups differ from relational databases. See About lookups for more information.
To define a lookup:
1.
Define the relationship for the lookup between the match field of the current table and the match field of the related table. See Creating relationships for more information.
2.
With the database open, choose File menu > Manage > Database.
3.
In the Manage Database dialog box, click the Fields tab.
4.
If your database contains more than one table, select the appropriate table from the Table list.
5.
Select an existing text, number, date, time, timestamp, or container field from the list of existing fields, or define a new field.
6.
Click Options (or double-click the field name).
7.
In the Options for Field dialog box, click the Auto-Enter tab, then select Looked-up value.
8.
In the Lookup for Field dialog box, choose the table the lookup will start with, and the table from which the value will be copied.
 •
For Starting with table, choose the table that the lookup will use as its starting point in the relationships graph.
 •
For Lookup from related table choose the related table from which the related data will be looked up.
9.
Select the field from which the lookup value will be copied.
10.
Select options for the lookup.
 
To
Do this
Specify an action when values in the match fields do not match exactly
For If no exact match, then, select an option to copy no value, copy the next lower or next higher value that's in the lookup source field, or enter a fixed value to be used instead.
Prevent null (empty) data in the lookup source field from being copied to the lookup destination field
Select Don't copy contents if empty. (Clear this option to allow empty data to be copied.)
11.
Click OK.
12.
Repeat steps 5-11 for each additional field for which you want to define a lookup.
Notes about defining lookups
 •
You can define a relationship for a lookup when you define the database tables or the lookup.
 •
The relationship can sort the related records so that the first related record (the record the lookup will copy) is a particular record. For example, to look up the most recent check-out date for a particular piece of equipment, sort records in descending order by check-out date.
 •
Be sure the lookup source field and lookup target field are the same field type, otherwise you might get unexpected results.
 •
To change a lookup, be sure Looked-up value is selected in the Auto-Enter options, click Specify, then make your changes.
 •
To stop or suspend using a lookup, clear Looked-up value in the Auto-Enter options.
 •
When data in the related table changes and you want the current table to contain the new values, you can look up the data again. See Updating lookup values.
Important  When a lookup is based on a multi-criteria relationship, a lookup will be triggered each time a value is entered into any of the fields that are involved in the multi-criteria relationship. The lookup is being performed each time a value is entered into one of the required fields. When all of the requisite fields have values, the final lookup value will be copied into the lookup target field.
Related topics 
About lookups
About relationships
Updating lookup values
Defining automatic data entry
Was this topic helpful? Send feedback.