Loading

Lookup Column (Linked Record)

A Lookup Column (Linked Record) shows a value from the record that one of this datastore's dropdowns points at. Use it to put the department's supervisor e-mail address on an applications list without copying that address onto every application.

Where to find it

Architect Panel → Data:

  • Datastores — the datastore, then its Fields list, to add the lookup column

Why not just copy the value

The old way to show a related record's value on a list was to copy it onto the row when the record was saved. That copy goes stale the first time somebody corrects the parent: the list keeps showing the old address, e-mails go to the old address, and the person who fixed the department believes they fixed it.

A lookup column stores nothing. It is resolved every time the cell is drawn, so it always shows what the linked record says now.

Before you start

The datastore needs a database dropdown that holds the link: Dropdown Box (Single) (DB) or Dropdown Box (Searchable) (DB) (see Datastore Field Types). No other type has a linked record to look in. You also need the ID of the field you want to show, from the Fields list of the datastore that dropdown points at.

Adding one

  1. Open the datastore the dropdown points at (for example Departments), go to its Fields list, and note the ID of the field to display (for example the supervisor's e-mail). Note the ID, not the field's row name.
  2. Open the datastore that holds the dropdown (for example Applications) and add a field.
  3. Choose the field type Lookup Column (Linked Record).
  4. In Dropdown Field on This Datastore, enter the row name of the dropdown that holds the link, for example dept.
  5. In Field to Display (Field ID), enter the ID you noted in step 1.
  6. Give the column a friendly name such as "Supervisor E-mail", save, and open the list to check the values.

How it behaves

  • Display only. It never appears on the add or edit form, and anything submitted under its name (by an import, the API or a crafted form) is discarded.
  • Follows the link live. Edit the parent and the column changes; re-point the dropdown and it follows the new parent; clear the dropdown and the cell is empty.
  • Merges and exports. The value is available as a merge field in e-mail templates and appears in CSV, Excel and PDF exports of the list.
  • One hop, one column. It shows a single field, verbatim, from the record one dropdown points at. It cannot follow a lookup column on the parent, and it cannot combine several fields into one label.

What it cannot do

  • No sorting. Clicking the column header does nothing, on purpose.
  • No column filter, and the search box ignores it. Searching the list for a supervisor's address finds nothing. Filter on the dropdown instead.
  • No joins in reports. Report on the dropdown or on the parent datastore.
  • Not offered by the onboarding wizard, because both questions need answers only you can give.

What goes wrong and how to tell

  • The column is blank for every row. Either the Field to Display ID belongs to a different datastore from the one the dropdown points at, or the dropdown field named is not a database dropdown. The platform refuses to show a value it cannot vouch for, leaves the cell blank and writes the mismatch to the error log. Check both answers.
  • Blank for some rows only. Those rows have no value in the dropdown, or point at a record that no longer exists.
  • A deleted parent still shows its value. A record that has been moved to the trash still resolves, unlike the dropdown beside it.

Worked example

A placements team keeps a Departments datastore with a supervisor e-mail for each department, and an Applications datastore with a searchable dropdown dept. They add "Supervisor E-mail" to Applications as a Lookup Column, with dept as the dropdown and the ID of the Departments e-mail field as the field to display. When a supervisor leaves and the department record is updated, every open application shows the new address immediately, and the confirmation e-mail that merges the column goes to the right person.

Recommendations

  • Use it instead of copying parent values onto child records.
  • Take the ID from the right datastore: the one the dropdown points at.
  • Filter and sort on the dropdown, not the lookup column.
  • Check a few rows after saving; a blank column means a mismatched answer.