Reference Columns Guide#
Mastering Reference Columns in 3 Steps#
In Grist, reference columns are the key tool to organize relational data. Reference columns tell
Grist that two separate tables are related, and specifies which records within those tables are
related. For example, if you have a table of
Dogs and a table of
Dog Owners, you may want each
dog’s record to explicitly reference their owner’s record in
Reference columns are a powerful tool for looking up data from a related table, and for creating highly productive layouts. You can learn to do this in three easy steps. We’ve created a simple template you can copy and edit as you follow along with this guide.
Using Reference Columns to Organize Related Data#
In our example, you are a graphic designer applying for jobs, and you are using Grist to keep
track of your job application process. In the template you have four tables:
Contacts. Your goal is to relate relevant data between tables and
create a custom dashboard where you can easily view each job application’s status, and add new job
applications and new milestones easily.
In this dashboard, clicking on each job application shows only the milestones related to that job application. We can create this relationship using reference columns.
Let’s create this dashboard together.
Step 1: Creating References#
Reference columns are a type of column available under COLUMN TYPE. It is helpful to think of the table with the reference column as the referencing table, and the table which is being referenced as the underlying table.
Converting Columns with Text into Reference Columns#
If you already have text in the selected column, set the COLUMN TYPE in the right-side panel to “Reference”. Grist will guess the underlying table and column you want to show in the reference column. You can review and edit this guess and set the appropriate underlying table and display column. DATA FROM TABLE points to the underlying table. SHOW COLUMN sets the display value in the reference column.
For example, in the
Milestones table, convert the
Role column into a reference column. DATA
FROM TABLE should point to
Job Applications. SHOW COLUMN should be set to
Role. Note that the
reference column is referencing the entire record and you are choosing which column to
display in the reference column.
Tip: You can easily identify reference columns by the chain link icons in the column’s cells.
Creating Reference Columns#
Contacts table we have a list of contacts that are not associated with any job
application or company. Let’s create a new column called
Company, set its type to Reference, and
Job Applications as the underlying table with
Company as the display in the reference
Click on the empty cell to open a drop-down menu and manually select the company at which each contact works. Look at the email address for a hint.
Note: You can always click on a Reference Column cell to open the drop-down menu and select a new value.
Step 2: Look up additional data in the referenced record#
Recall that the reference column is referencing another table, and correlating two specific records. Although you see a specific column in the display of the reference column, the reference is being made to the entire record. This allows us to look up additional data fields in the related record using a simple formula. Let’s try it.
In step 1, in the
Milestones table we created a reference column called
Role. It would be useful to
also see for each
Milestone record, the relevant company. For example, in row 1 we see the
Milestone event “Rejected!” for the role “Head of Digital Design”. It is not immediately apparent
which company this is. Let’s use the
Role reference column to easily look up the company listed in
the “Head of Digital Design” record in the
Job Applications table. To do so we create a column
Company and we use the formula
The formula structure is
$[Reference Column ID in Referencing Table].[Column ID in Underlying Table].
Grist will also auto-complete parts of the formula as you type it.
There’s an alternative way to add multiple columns from an underlying table. If you’re interested in learning more, visit our website’s help section on reference columns.
Step 3: Create a Highly Productive Layout with Linked Tables#
One of the most powerful features of Grist is the ability to link related tables in the same page to create highly productive layouts. In the final dashboard shown at the start of this tutorial, we saw that clicking on a job application would populate a view of milestones related to that job application.
Let’s do that now by adding
Milestones as a widget to the
Job Applications page. (Brush up on
widgets here.) Adding the table as a
Card List widget makes the data easier to view. Similarly, you may want to change the
Applications table to a Card List widget.
In the CARD LIST menu on the right, select DATA to set data selection rules. Under SELECT BY you
will see the option “JOB_APPLICATIONS Card List”. This option is only available because in step 1
we created a reference from the
Milestones table to the
Job Applications table in the
reference column. This reference tells Grist which milestones are related to which job
Congrats! You now know how to use reference columns to organize related data, give your data structure, and create linked widgets in productive layouts. If you’d like, compare your document to the tutorial solution.
Dig Deeper: Combining formulas and reference columns.#
If you’re comfortable with formulas, try using formulas in reference columns to make Grist an even more powerful tool. In the tutorial solution, we’ve used a formula to do more.
The formula in the
Last Milestone field in the
Job Applications widget is looking up the most
recent date in related records in the
Milestones table. Thus, adding a new milestone with a more
recent date would automatically update this field. You can learn more about lookup formulas on
Last Milestone is both a formula column and a reference column, we’ve also done
the following which follows the formula described in step 2 of this tutorial.
Status field uses the formula
$Last_Milestone.Round to look up the related
milestone’s round status. The
Updated On field uses the formula
$Last_Milestone.Date to look up the related milestone’s date.
By doing this, status and date also auto-update when the
Last Milestone field updates.