By combining data from multiple tables in one grid view, you can create integrated grids and visualizations while avoiding duplication of data. The basis of joined grids is a "lookup column" -- a column that connects the two tables such that one table looks up values in another table, making it possible to join data from both tables in combined grids.

• Create a Lookup Field
• Troubleshooting

Create a Lookup Field

An administrator first needs to connect the two tables by creating a lookup column between the two tables. Lookup columns can be created in two ways: either through the graphical user interface, as described in Lookup Columns; or through a SQL query, as described in Lookups: SQL Syntax.

Create a Joined Grid View

Once tables are connected by a lookup column, you can create a joined grid view on either table, subject to key complexity rules described below.

For example, suppose you wish to create a grid that brings together columns from both the Demographics and the Languages tables. A lookup column has already been defined in the Demographics table that looks up data in the Language table. To create a grid including data from both tables:

Troubleshooting

FAQ: In a study, why can't I customize my grid to show a particular field from another dataset?

Background: To customize your grid view of a dataset by adding columns from another dataset, it must be possible to join the two datasets. The columns used for a dataset's key influence how this dataset can be joined to other tables. Certain datasets have more than one key column (in other words, a "compound key"). In a study, you will typically encounter three types of datasets:

• Demographic datasets use only one column as a key. The key is the participantID. This means that only one line of data (for any date) can be associated with a participant in such a dataset.
• Clinical or standard datasets use participant/visit pairs as a compound key. This means that each row is uniquely identified by participant/visit pairs, not by participant identifiers alone.
• Assay datasets copied into a study also use compound keys. Assay datasets use participant/visit/rowID columns as compound keys, so only the trio of these columns uniquely identifies a row. In other words, a participant can have multiple assay rows associated with any individual visit - these are uniquely differentiated only by their rowIDs.

Consequences: When customizing the grid for a table, you cannot join in columns from a table with more key columns. For example, if you are looking at a clinical dataset in a study, you cannot join to an assay dataset because the assay dataset has more columns in its key, and thus can have multiple rows per participant/visit. So there isn't a unique mapping from a participant/visit pair in the 'originating' clinical dataset to a specific row of data in the assay dataset.

Guidance: To create a grid view combining columns from disparate datasets, start with the dataset with more columns in the key. Then select a column from the table with fewer columns in the key. There can be a unique mapping from the compound key to the simpler one - some columns will have repeated values for several rows, but rows will be unique.

Related Topics

• Customize Grid Views
• In SQL terms, this combined grid view is the equivalent of a SQL SELECT query with one or more inner joins. For details on using lookup syntax in SQL queries, see Lookups: SQL Syntax.
• The Tutorial: Longitudinal Studies uses combined grids using a more complex set of tables joined by multiple foreign key lookups.
• Step 3: Integrate Data from Different Sources