Relations between tables

An event points at a venue — and the event card prints its address.

A Relation column stores a pointer to a row in another table. Set it up like this: in the Events table add a venue field related to the Venues table. In each row you pick a venue from the list.

From there it works by itself: inside a repeater over events, the related row’s fields are available.

{{item.venue.title}}, {{item.venue.address}}

Why bother: the venue’s address is stored once. Change it in Venues and it changes across every event at once, instead of by hand in thirty rows.

Next to the table picker there is Show which column: the column the linked row is labelled by. By default it is the first text column, so if your table starts with something else, name the right column yourself.

A relation can be narrowed by another one. Halls belong to venues: on the Hall column pick Narrow by field → Venue, and the hall list shrinks to the halls of the chosen venue — both in the table and in the app’s form. The column being matched is found automatically (the one pointing at the same table); name it by hand only when a table is referenced more than once.

Change an event’s venue and the hall of the old venue resets to “not set”, leaving the new venue’s halls in the list. A hall that also belongs to the new venue stays selected: only what became untrue is cleared. If the new venue has a single hall, it is filled in for you.

A relation cell stores the row ID; the name is only its label. No separate ID column is needed: it is inserted like any field of the linked row — {{item.venue.id}}. To see IDs in the table pick Show which column → Row ID; you can also match on it in Compare with column.

How many rows. A relation column has a How many rows setting (the Relation type item on the schema line changes the same thing):

• One row (many-to-one) — as it always was: an event has one venue, and a venue has any number of events.
• One, and nobody else takes it (one-to-one) — a hall has one layout, an employee one pass. A row already chosen in another record is no longer offered, neither in the table nor in the app’s form. A duplicate entered before the switch is marked in amber; nothing is erased for you.
• Several rows (many-to-many) — an event has several artists, an artist several events. The cell picks them as pills. {{item.artists}} prints the names separated by commas, {{item.artists.count}} says how many, and a repeater inside the card with the source {{item.artists}} draws each one. In an app form this column is filled with the Multi-select block.

One-to-many needs no column of its own: it is the same relation seen from the other side. A city that venues point at has a list of its venues — {{item.venues}} (names separated by commas), {{item.venues.count}}, and a repeater source. In the data picker it reads “Venues whose “City” is this row”. Rows written by the app itself count too: a show sees the tickets bought for it.