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 inside the
Demographics table. You would add a lookup column to the Demographics table that used values from the Languages table.
Set Up a Lookup Field
To join these tables, add a lookup column to the Demographics dataset definition:
- Go to the dataset or list of interest -- the table where you want the lookup to originate. For example, Demographics.
- Click Manage > Edit Definition. (Or Design > Edit Design for lists.) You must have the admin role to see these buttons.
- Click Add Field.
- Enter a Name. This is the system name.
- Enter an optional Label. This will be shown for the column header and other human readable interfaces.
- 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 show below.
- Click Apply.
- Click Save.
- The lookup column is now available to the grid view customizer (the GUI) and to SQL queries.
- You can now create grids from any of the columns of the two tables, Demographics and Languages. For details on creating such grids see Join Columns from Multiple Tables.
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, such that any imported values must appear in the lookup's 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 table being imported into.
- 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 make pre-existing data conform to the values in the lookup target table, either edit them by hand or re-import conforming values.
Related Topics
