By combining data from multiple tables in one grid view, you can create integrated grids and visualizations with no duplication of data.
Lookup Fields 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 field 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 imported values 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 field to the Demographics dataset that used values from the Languages list.
To join these tables, an administrator adds a lookup field 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.
- 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.
- In either the expanded or collapsed view, you can click the name of the target of the lookup to open it directly.
- The lookup field 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 target 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 field. 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 field shown in the example above. The names "English", etc., are displayed even 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 field, and select the fields you want to display.
You can also use query metadata to achieve the same result: see
Query Metadata: Examples
Suppress Linking
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.
<tables xmlns="http://labkey.org/data/xml">
<table tableName="MyList" tableDbType="NOT_IN_DB">
<tableUrl></tableUrl>
<columns>
</columns>
</table>
</tables>
A related option is to use a SQL annotation directly in the query to not "follow" the lookup and display the field 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.
Import to Lookup Fields
When users enter a single row of data, they will select the desired value from a dropdown menu. For data bulk imported from file or using copy/paste, additional options can ensure that data is as expected.
Ensure Value Exists in Lookup Target
When you are importing data into a table that includes a lookup field, 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 if this enforcement is enabled for that field. Otherwise, new values are allowed and may result in broken lookups.
To set up enforcement:
- Go to the field editor of the dataset or list with the lookup field.
- Select the lookup in the Fields section.
- Expand it.
- 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.
Import Lookups By Alternate Key
When importing data that includes lookup values, you can set them to be resolved by values other than the target's primary key (when the target table includes other columns that contain unique values, i.e. alternate keys). For example, tables in the "samples" schema (representing
Samples) use the RowId field as their primary key, but their Name field is guaranteed to be unique as well. Imported data can use either the primary key value (RowId) or the unique column value (Name).
When alternate key lookup is enabled and a value is provided that could be either a valid alternate key or a valid primary key, i.e. the key fields are of the same type, the system will first attempt to resolve by the alternate key. Only if the alternate key does not match will a match by primary key be attempted.
For bulk import by copy paste or from a file, you can use a checkbox to
Import Lookups by Alternate Key. API calls can also set this flag. In python, for example, you can include in a call to
import_rows a line like:
import_lookup_by_alternate_key: bool = False,
Lookup Resolution During Import
The specifics of how lookup values are resolved during import, update, or merge depends on what data is provided, whether validation is enabled, and whether the alternate-key lookup is enabled. The specific behavior is as follows:
If the imported data includes values from the
primary key column, then:
- If lookup validation is not enabled:
- A valid primary key value will succeed.
- An invalid primary key value that is of the same type as the primary key will succeed and the result will be a broken lookup.
- An invalid primary key that is of a different type will fail with an error about being unable to make the necessary type conversion.
- If lookup validation is enabled:
- A valid primary key value will succeed.
- An invalid primary key of the proper type will fail with an error about the value not being present in the target.
- An invalid primary key of the incorrect type will fail with an error about being unable to make the necessary type conversion.
If
alternate key values are provided, i.e. the
Import Lookups by Alternate Key box is checked, and the values in the import column are of the type of an alternate key column, then:
- If lookup validation is not enabled:
- A valid alternate key value will succeed, matching with the alternate key (if it could match with either the alternate or primary key).
- A valid primary key value will succeed (only if the value is not already matched as a valid alternate key).
- An invalid alternate key value of a different type than the primary key will fail with an error about the value not being found in the current context.
- An invalid alternate key value that matches a primary key value will succeed, matching to the primary key.
- An invalid alternate key that looks to be the same type as the primary key but does not match any value will succeed, resulting in a broken lookup.
- If lookup validation is enabled:
- A valid alternate key value will succeed, matching with an alternate key if it could match either an alternate or the primary key.
- A valid primary key value will succeed.
- An invalid alternate key value of a different type than the primary key value will fail with an error. Note that this error will occur before calling any trigger script.
- An invalid alternate key value that matches a primary key value will succeed
- An invalid alternate key value that looks to be the same type as the primary key but does not match any actual primary key will fail with an error about the value not being present for the given field.
A
deprecated feature flag can be enabled to "Resolve Missing Lookup Values to Null". When this flag is enabled, and alternate key lookup is in use, values that don't resolve will return null instead of an error.
- When lookup validation is enabled:
- A valid alternate key value will succeed, matching with an alternate key if it could match either an alternate or the primary key.
- A valid primary key value will succeed.
- An invalid alternate key value of a different type than the primary key value will pass the original value to the pre-import trigger task (if any).
- An invalid alternate key value that matches a primary key value will succeed and match the primary key.
- An invalid alternate key value that looks to be the same type as the primary key but does not match any actual primary key will pass a null value to the pre-import trigger task (if any).
Related Topics
