Relations Between Tables

A relation field links a row to rows in another table — a transaction to a vendor, an invoice to a client, a line item to the invoice above it. Once rows are linked, a lookup, rollup or count can pull values across the link, and a formula can read the related rows directly.

The short version#

• A relation stores links, not copied values. The original row remains the source of truth.
• Choose One row for relationships such as “invoice belongs to one client”; choose No limit for relationships such as “vendor has many transactions”.
• Turn on Show on the other table when people need to navigate the relationship from both sides.
• After the relation exists, use a lookup to show values, a rollup to summarise them, a count to count them, or a formula for custom logic.

Creating a relation#

Relations never cross workspaces.

One-way or two-way#

A relation is one-way by default. Transactions can point at Vendors without Vendors growing a column, which keeps a reference table readable when a hundred thousand rows point at it.

Turning on Show on <target table> adds a field on the other table that shows the rows pointing back. You name it, and you choose its own cardinality. There is still only one set of links underneath: the reciprocal is a view of the same links, not a second copy that can drift.

Creating a one-way relation needs permission to edit the schema of the table you are in, and read access to the target. Creating a reciprocal needs schema-edit on both tables, and neither may be structure-locked.

One row or no limit#

Each side is set independently.

• One row — the cell holds at most one link. Picking a row commits and closes the picker.
• No limit — the cell holds many links, in the order you arrange them, up to 100. The picker stays open so you can add several.

The 100-link ceiling is a refusal: linking a 101st row fails and says so. It is not a display cap, and a rollup over a cell that is at the ceiling still reads all 100.

Self-relations#

The table you are in is offered alongside every other table, so a relation may point at its own rows: an invoice to the invoice it credits, a task to the task above it. Turn on the reciprocal and you get the matching field on the same table, which is how a parent/children pair is built.

The grid stays a flat grid. Linked rows are links you can follow, not an indented tree.

Filling a relation cell#

An empty relation cell reads Add relation. Clicking it opens a search box scoped to the target table. Rows are matched on their primary field, and each result shows that primary value plus up to two preview fields you chose when configuring the relation, so two clients with the same name are still tellable apart.

• Results are limited to 25 at a time; keep typing to narrow them.
• Picked rows become chips. Each chip has ‹ and › controls to move it earlier or later in the cell, and an ✕ to remove it.

Two things the picker deliberately does not do:

• It will not create a row for you. You pick from rows that already exist. Add the row in the other table first.
• A picker filter is not a data rule. You can narrow what the picker offers — only clients whose status is Active, say. That constrains the list you are shown. Links made before you set the filter, links made through the API, and links made from the other side are unaffected, and none of them are removed.

When the other row goes away#

Deleting a linked row moves it to trash; it does not silently vanish from your cell. The chip stays and reads Unavailable record. You can remove it, and restoring the row from trash within 30 days brings the chip back to life.

The same holds for a whole table: trash a table and relations pointing at it render as unavailable, and any lookup, rollup or count reading through them reports #REF! until the table is restored. Restoring clears the error.

Deleting a relation field#

Deleting a relation is refused while a lookup, rollup, count or formula reads it, in any table. BankSync tells you how many computed fields do and what they are, and the delete only proceeds once you have seen that and confirmed. Deleting the field removes its links; the rows on both sides are untouched.

Where to go next#

• Lookups, rollups and counts — do something with the rows you just linked.
• Formulas — read related rows directly.