By combining data from multiple tables in one grid view, you can create integrated grids and visualizations with no duplication of data. Lookup Columns
give you the ability to link data from two different tables by reference. Another name for this type of connection is a "foreign key".
Once a lookup column pulls in data from another table, you can then display values from any column in that target (or "looked up") table. You can also take advantage of a lookup to simplify user data entry by constraining entered values for a column to a fixed set of values in another list.
Set Up a Lookup Field
Suppose you want to display values from the Languages
list, such as the translator information, alongside other data from the Demographics
dataset. You would add a lookup column to the Demographics dataset that used values from the Languages list.
To join these tables, an administrator adds a lookup column to the Demographics dataset definition using this interface:
- Go to the dataset or list where you want to show the data from the other source. Here, Demographics.
- Click Manage, then Edit Definition.
- Click Fields.
- Use to expand the field you want to be a lookup (here "language").
- If the field you want doesn't already exist, click Add Field to add it.
- From the dropdown under Data Type, select Lookup.
- In the Lookup Definition Options, select the Target Folder, Schema, and Table to use. For example, the lists schema and the Languages table, as shown below.
- Scroll down and click Save.
- The lookup column is now available for grid views and SQL queries on the Demographics table.
This can also be accomplished using a SQL query, as described in Lookups: SQL Syntax
Create a Joined Grid View
Once you have connected tables with a lookup, you can create joined 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 itself need not be shown.
- Go to the "Demographics" data grid.
- Select (Grid Views) > Customize Grid.
- Click the to expand the lookup column and see the fields it contains. (It will become a as shown below.)
- Select the fields you want to display using checkboxes.
Learn more about customizing grid views in this topic:
Default Display Field
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. The names "English", etc, are displayed though the lookup is to an integer key.
Display Alternate Fields
To display other fields from the looked up table, go to (Grid Views) > Customize View
, expand the lookup column, 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 field editor of the dataset or list with the lookup column.
- Select the lookup column in the Fields section.
- Expand it by clicking the .
- Under Lookup Validator, check the box for Ensure Value Exists in Lookup Target.
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.
By default the display value in a lookup field links to the target table. To suppress the links, and instead display the value/data as bare text, edit the XML metadata
on the target table. For example, if you are looking up to MyList, add <tableUrl></tableUrl> to its metadata, as follows. This will suppress linking on any table that has a lookup into MyList.
<table tableName="MyList" tableDbType="NOT_IN_DB">
A related option is to use a SQL annotation directly in the query to not "follow" the lookup and display the column as if there were no foreign key defined on it. Depending on the display value of the lookup field, you may see a different value than if you used the above suppression of the link. Learn more here: Query Metadata