# Link two tables, add lookups and rollups

> Connect records in two tables with a link field, then show a linked record's value or combine values across several linked records.

Source: https://www.speclatch.com/guide/tables/link-tables-lookups-rollups
Who: Admin or Engineering. Plan: Every plan.
Last verified: 2026-11-11

Some specs belong to another table: a pump runs on a motor whose rating already lives in your Motors table. A link connects each pump to its motor, so nobody types the motor's specs twice. Then a pump can show its motor's rating, and each motor the most power any of its pumps needs, and both stay current when either side changes.

This guide links a pump table to a Motors table, picks a pump's motor, then adds a lookup and a rollup.

## Links, lookups and rollups

- A *link* field holds which records of another table go with this record. The other table gets a matching link field, so the connection shows from both sides.
- A *lookup* shows a field of the linked records, such as the motor's rated power on the pump.
- A *rollup* combines one number across all the linked records: the highest, the lowest, a sum, an average, a range or a count.

Lookups and rollups are both **From linked records** fields, worked out from the current links and values each time the record is read.

## Before you start

- To add fields, you need the Admin role, or Engineering with Confidential visibility. Any Engineering member can pick links once the field exists.
- Both tables exist.

## Link the tables

1. Open **Fields**, pick the pump table and click **Add field**.
2. Click the **Link to records** tile.
3. Type the name *Standard motor*.
4. Under *Links to records of*, pick *Motors*.
5. Click **Create field**. Motors gets a link field back to the pumps, named after the pump table and this field.

![The New field dialog for a Link to records field named Standard motor, with Motors chosen under Links to records of](https://www.speclatch.com/guide/tables/link-tables-lookups-rollups-1.png)

*Pick the table the field links to; the other table gets a link back.*

> **Tip:** Rename the field on Motors so it reads well there, such as *Close-coupled pumps using it*.

## Pick a pump's motor

1. Open a pump record that is still a Draft.
2. In the *Standard motor* row, click **Edit links**.
3. Tick a motor, such as the 5 HP 2-pole one.
4. Click **Save links**. The motor's record now lists the pump too.

![The link picker for Standard motor: a search box, a list of motors with checkboxes and statuses, and a Save links button](https://www.speclatch.com/guide/tables/link-tables-lookups-rollups-2.png)

*Tick the linked records; the change shows on both sides.*

> **Which record must be a draft.** A link belongs to the record on the side where you created the field, here the pump. It saves only while that pump is a Draft, even from the motor's page.

## Show the motor's rating on the pump

This is a lookup.

1. On **Fields**, pick the pump table, click **Add field** and click the **From linked records** tile.
2. Type the name *Standard motor rating*.
3. Set *Through* to *Standard motor*, the link to follow.
4. Set *Show* to *Each value*.
5. Set *Field* to *Rated power*, the motor field to show.
6. Click **Create field**.

A pump linked to the 5 HP motor now shows 5 HP, with 3.73 kW on the metric side.

## Find the most power each motor must deliver

This is a rollup, on the other table.

1. On **Fields**, pick Motors, click **Add field** and click **From linked records**.
2. Type the name *Highest pump power*.
3. Set *Through* to the link back from the pumps.
4. Set *Show* to *Highest*.
5. Set *Field* to *Maximum power, water (end of curve)*.
6. Under *Show in*, pick *HP*. Values stored in kW convert before the comparison.
7. Click **Create field**.

![A From linked records field on Motors: Through set to the pumps' link, Show set to Highest, a power field chosen and Show in set to HP](https://www.speclatch.com/guide/tables/link-tables-lookups-rollups-3.png)

*A rollup combines one number across every linked record.*

Two pumps use the 15 HP 2-pole motor, peaking at 12.28 HP and 15.27 HP, so the motor shows 15.3 HP.

> **Tip:** *Count* counts the linked records, so it needs no *Field*. *Unique values* lists each linked value once.

## Use them in formulas

A formula reads a lookup or a rollup by its key, like any other field. With the lookup `motor_hp` = 5 HP and `max_bhp` = 5.22 HP, `round(max_bhp / motor_hp * 100, 0)` gives a motor loading of 104 %.

> **Who sees them.** A lookup or rollup stays empty for anyone who can't see the field it reads. At the Public tier, it reads only Published linked records. When an Engineering member creates one, its tier can't be more visible than the field it reads.