SSpecLatch
ContentsLink two tables, add lookups and rollups

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.

TopicTables & fields
TypeHow-to
WhoAdminEngineering
PlanEvery plan
Time8 min
Verified11 Nov 2026

View .md

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.

  • 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.
  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
Pick the table the field links to; the other table gets a link back.

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
Tick the linked records; the change shows on both sides.

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
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.

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 %.

Related

ReferenceField types
ConceptTables & fields
How-toGive fields units and write your first formula