Links between tables

A Linked record field points a record at a record of another table: an order at its customer, an invoice at its tenancy, a payment at its invoice. The customer's details live once, in Customers, and every order that links to it shows them. Change the customer's address and every order sees the new one.

Add a link

1. In the table that points, such as Orders, choose + at the right of the headers.
2. Name the field, such as Customer, and pick the type Linked record.
3. Under Related table, pick the table it points at, such as Customers. Only tables of the same database are offered.
4. Under Display columns, tick the fields of that table the link shows, such as Name and City. The first field is ticked to start with, and at least one stays ticked.
5. If you want, use Limit records to offer only some records, such as customers whose Status is Active.
6. Choose Save.

Pick a linked record

Click the link's cell and type: the list searches by the first display column. Pick a record from it. For more room, choose Advanced search, which opens the other table to search and pick from.

A link holds one record. An order has one customer; a customer can have any number of orders linking to it.

With Limit records set, only the records it keeps are offered when you pick. The limit also applies when the link is read: a record linked earlier that the limit leaves out no longer shows its display columns.

Read across a link

• See more of the linked record. A link column's header has an expand button that shows each display column as a column of its own, such as Customer › City. The matching button folds them back.
• Group by it. Group offers the linked record itself and each of its display columns: group orders by the customer's country (see Group records and read the totals).
• Filter by it. Filter on the link with is, is not, is empty or is not empty.
• Use it in a formula. A calculation can read a display column through the link. See Calculations.
• Count or total from the other side. Customers has no field listing its orders by itself. Add a rollup to Customers to count its orders or add up their amounts.

Only the display columns of a link can be grouped by or read through it. Tick a field under Display columns before you group by it or use it in a formula. Taking away a display column that a formula reads is refused, and Edit field says what uses it.

Turn a field into a link

A column of customer names or product codes, often from an import, can become a link. Open Edit field, choose Linked record as its type and pick the table and the field to Match on. See Change a field's type.

The worked example of several linked tables is How to build a rental management system.