Linking Tables With Reference And Lookup Columns
Link rows across tables and databases, and pull values through those links automatically.
Overview
Real information lives in more than one list. Invoices name products, projects name clients, and
orders name customers. Reference and Lookup columns let one table point at the rows of
another, even a table in a different database, so you record a fact once and use it everywhere.
- A Reference column holds links to rows in another table. Each link shows as a chip with the
linked row's name, and you can click it to jump there.
- A Lookup column reads a value through a reference, for example the Price of the product an
invoice links to. It is read-only and always current.
An Example
Say you have two databases:
Database › Table | Columns |
**Catalogue › Products** | Name, Price |
**Sales › Invoices** | Name, Stage, Qty, Amount, **Product** (Reference → Products), **Unit price** (Lookup: Product › Price) |
Each invoice links to one product. Its Unit price column shows that product's price without you
typing it again. When you change the product's price in the Catalogue, every invoice that links to it
shows the new price immediately.
Adding A Reference Column
- Open the table that should hold the links (in the example, Invoices) and click + Column.
- Give it a name (for example
Product) and set Type to Reference. It is in the
Relational group.
- In Search databases and tables, type to find the table to link to. The list shows every
Database › Table you can read, including tables in other databases.
- Tick Allow multiple if one row may link to several rows. Leave it off for a single link.
- Press Enter or click Save.
Setting And Clearing A Link
- Double-click a cell in the reference column. A Choose reference rows picker opens.
- Type in Search rows by name to find the row, then pick it (or several, if the column allows
multiple).
- Confirm to save. To remove the link, open the picker and choose Clear.
The cell then shows a chip for each linked row:
Widget ← click to open this row in its own database and table
Restricted ← the linked row exists, but you don't have access to it
Deleted ← the linked row (or its whole table) has been deletedClicking a chip takes you to the linked row, scrolls it into view and briefly highlights it, even
when it lives in another database.
Adding A Lookup Column
- On the same table, click + Column and choose Type → Lookup (in the Relational group).
- Pick the Reference column it should read through (for example
Product).
- Pick the Target field: any column of the linked table, or its Name.
- Save.
Lookup cells are read-only. They change only when the linked row changes, and reading them never
alters your own row.
Seeing What Links To A Row
Click the › arrow at the start of a row to expand it. The panel underneath lists every row, in
any table of any database you can read, that links to this one, and through which column. For
example:
Sales › Invoices › INV-001 via product
Sales › Invoices › INV-003 via productIf nothing links to it you'll see No rows reference this one.
Links Keep Your Data Consistent
- Deleting a linked row is refused. If you try to delete a row that other rows still point at, a
Row is still referenced dialog tells you how many rows reference it. Clear those links first,
then delete.
- Deleting a whole table is allowed, with a warning. The confirmation says how many rows elsewhere
refer to it. After the delete, those links show as Deleted chips, so nothing vanishes without a
trace.
Column type | Can filter by it in a view | Can sort or group by it |
Reference | Yes: "is this row", "is one of these rows", or "has no link" | Not in a saved view |
Lookup | No | No |
Summary
Reference columns link rows across tables and databases as clickable chips. Lookup columns pull a
value through those links and stay current on their own. Expanding a row shows everything that points
at it, and Sapience won't let you delete a row that is still linked without telling you first.