To create grids that contain data from two different tables, create a lookup
column in one of the tables: a column that pulls in data from the other table. Once you have a lookup column established, you can display values from any column in the target table (the "looked up" table).
For example, suppose you want to display values from the Languages
table, such as the translator information, alongside data from the Demographics
table. You would add a lookup column to the Demographics dataset that used values from the Languages list.
Set Up a Lookup Field
To join these tables, an administrator must add a lookup column to the Demographics dataset definition:
- Go to the dataset or list where you want to show the data from the other source. Here, Demographics.
- Click Manage, then Edit Definition. (In a list, click Design > Edit Design to reach the same editor interface.)
- Click Add Field.
- Enter a Name, used internally, and an optional Label to show in the column header.
- Click the dropdown under Type and select Lookup.
- In the popup dialog select the target table of the lookup. For example, the lists schema and the Languages table, as shown below.
- Click Apply.
- You may want to relocate your new field using the and buttons.
- Click Save.
- The lookup column is now available for grid views and SQL queries on the Demographics table.
- You can also create grids merging information from any of the columns of both tables. For example, a grid showing which translators are needed for each cohort would make it possible to schedule their time efficiently. Note that the original "Language" column need not be shown.
For more about creating joined grids see Join Columns from Multiple Tables
Default Display Fields
For lookup fields where the target table has an integer primary key, the server will use the first text field it encounters as the default display column. For example, suppose the Language field is an integer lookup to the Languages table, as below.
In this case, the server uses Language Name
as the default display field because it is the first text field it finds in the looked up table. You can see this in the details of the lookup column shown in the example above, as "English", etc, are displayed though the lookup is to an integer key.
Displaying Alternate Fields
To display other fields from the looked up table, go to (Grid Views) > Customize View
and select the fields you want to display.
You can also use query metadata to achieve the same result: see Query Metadata: Examples
Validating Lookups: Enforcing Lookup Values on Import
When you are importing data into a table that includes a lookup column, you can have the system enforce the lookup values, i.e. any imported values must appear in the target table. An error will be displayed whenever you attempt to import a value that is not in the lookup's target table.
To set up enforcement:
- Go to the definition editor of the dataset or list with the lookup column.
- Select the lookup column in the table.
- Click the Validators tab.
- Click the button Add Lookup Validator.
Note that pre-existing data is not
retroactively validated by turning on the lookup validator. To ensure pre-existing data conforms to the values in the lookup target table, either review entries by hand or re-import to confirm values.