Lookups
A lookup finds the records of another table that match a condition you write, such as the price list row for a product code, and shows them without anyone picking them.
A link points at a record someone picked. A Lookup finds the records by itself: you write the condition once, and every record shows the rows of the other table that meet it. Use one when the two tables share a value but nobody links them by hand: an order line and a price list share a product code, a sale and an exchange rate share a currency.
A lookup is worked out each time the table is read, so it follows changes on both sides. Nobody types in it.
Add a lookup
- Choose + at the right of the headers, name the field, such as
Price, and pick the type Lookup. - Pick the table to look in, such as Price list.
- Under Display columns, tick the fields of that table to show, such as Unit price.
- Under Formula, write the condition a row of that table must meet. Write a field of this table by its name, and a field of the other table as the lookup's name, a dot, and the field's name. For a lookup called
Price, on order lines that have aCodefield:Price.Code = Code, the price list rows whose Code is this line's Code. - Use Limit records to look only among some rows, such as those whose Status is Current.
- Decide on Allow multiple matches, then choose Save.
The formula is written in the same way as a calculation's: see Calculations for the syntax.
One match or several
| Allow multiple matches | What the lookup shows |
|---|---|
| On, as it starts | Every row that meets the condition, as a list. It cannot be sorted, filtered or grouped by. |
| Off | One row: the earliest-added of those that meet the condition. It behaves like a link: you can sort, filter and group by it, and read its display columns in a formula. |
Switch it off when the condition can only be met once, such as one price per product code.
Turn a lookup into a link
A lookup changes whenever the other table changes. To keep what it found as it is now, turn it into a link: open Edit field and change its type to Linked record. Each record is linked to the row its lookup found; where several rows matched, the earliest-added is kept. From then on the link stays put until someone changes it. The table it points at cannot change in the same step.
The formula box does not check field names as you type. A misspelt name is only reported when you save the field or when the table is read.
Last updated 29 October 2026