# Relational Fields

Three field types connect tables and derive data from those connections. They form a chain: a `relationship` defines the link, then `lookup` and `rollup` read or aggregate data through it. (The related [`count`](/en/docs/advanced-fields#count) field — categorized as Advanced — also traverses a relationship.)

| Type           | Purpose                                                               |
| -------------- | --------------------------------------------------------------------- |
| `relationship` | Creates a foreign-key link to another table. Foundation for the rest. |
| `lookup`       | Reads a single field value from a related record. Read-only.          |
| `rollup`       | Aggregates a field across many related records (sum, avg, …).         |

## `relationship`

Creates a foreign-key link to another table.

| Property          | Description                                                                                                        |
| ----------------- | ------------------------------------------------------------------------------------------------------------------ |
| `relatedTable`    | **Required.** Name of the target table to link to. Must match an existing table.                                   |
| `relationType`    | Cardinality. Defaults to `many-to-one`. Common values: `one-to-one`, `many-to-one`, `one-to-many`, `many-to-many`. |
| `foreignKey`      | Custom foreign-key column name (used for `one-to-many`). Auto-generated when omitted.                              |
| `displayField`    | Field from the related table shown in the UI (e.g. `name` instead of the raw id).                                  |
| `onDelete`        | Referential action when the related row is deleted (e.g. `cascade`, `set-null`, `restrict`, `no-action`).          |
| `onUpdate`        | Referential action when the related primary key is updated.                                                        |
| `reciprocalField` | Field name for the inverse (bidirectional) link on the related table.                                              |
| `allowMultiple`   | Boolean. Allows linking to multiple records (defaults to `true` for `many-to-many`).                               |
| `limitToView`     | Restricts selectable related records to those visible in a named view of the related table.                        |

```yaml
- id: 1
  name: customer
  type: relationship
  relatedTable: Customers
  relationType: many-to-one
  displayField: full_name
  onDelete: set-null
  reciprocalField: orders
```

## `lookup`

Displays a field value pulled from a related record via an existing relationship. Read-only and auto-updated.

| Property            | Description                                                                              |
| ------------------- | ---------------------------------------------------------------------------------------- |
| `relationshipField` | **Required.** Name of the relationship field to traverse (must exist in the same table). |
| `relatedField`      | **Required.** Field on the related table whose value to display.                         |
| `filters`           | Optional filter expression narrowing which related records are considered.               |

```yaml
- id: 2
  name: customer_email
  type: lookup
  relationshipField: customer
  relatedField: email
```

## `rollup`

Aggregates values from many related records.

| Property            | Description                                                                         |
| ------------------- | ----------------------------------------------------------------------------------- |
| `relationshipField` | **Required.** Name of the relationship field connecting to the related table.       |
| `relatedField`      | **Required.** Field on the related table to aggregate.                              |
| `aggregation`       | **Required.** Aggregation function (e.g. `SUM`, `AVG`, `MIN`, `MAX`, `COUNT`).      |
| `format`            | Display format for the aggregated result (e.g. `currency`, `number`, `percentage`). |
| `filters`           | Optional filter applied before aggregation.                                         |

```yaml
- id: 3
  name: total_sales
  type: rollup
  relationshipField: orders
  relatedField: amount
  aggregation: SUM
  format: currency
```

:::callout
**The relational chain.** Start with a `relationship` to create the link, then derive data without duplication: `orders → customer (relationship) → customer_email (lookup)`, or aggregate with `rollup`/`count`. `count`, `lookup`, and `rollup` are validated at config time to ensure their `relationshipField` references an actual `relationship` field in the same table.
:::

See [Relationships](/en/docs/table-relationships) for a deeper look at cardinalities, referential actions, reciprocal links, and `displayField`.