Skip to main content

Add a Multi-Table Lookup

Goal​

Add a field that points at a record in one of several tables — a Customer that may be an Account or a Contact, a Payer that may be any of four things.

Prerequisites​

  • You are signed in with a maker (or platform administrator) role.
  • The table you are adding the field to exists, and so do at least two tables you want it to be able to point at.

Steps​

  1. Open Tables, choose the environment, and open your table on the Fields tab.
  2. Select New, and give the field a display name and a logical name as usual.
  3. For the data type, choose Lookup (several tables).
  4. Tick the tables the field may point at. At least two. A small number beside each ticked table shows the order you ticked them in — that order is the order a user sees, so the first one is the one the picker opens on. Tick the commonest one first.
  5. Select Add.
  6. The field is live as soon as it is added — as with any field, its column exists at once.

What a user sees​

An ordinary lookup box. Selecting it opens a panel, and the tables you ticked lead that panel, above the names they are filtering:

Customer
[ 👤 Ada Lovelace            ] ← closed: the record, with its table's icon
─────────────────────────────
[ Account ] [ Contact ]        ← open: the tables you ticked, in your order
  Ada Lovelace                 ← …and the matches in whichever is selected
  Ada Byron

Choosing the table first is not an extra step for its own sake. "Find me a contact called Ada" and "find me an account called Ada" are two different searches over two different sets of columns, so one merged result list would have to reconcile columns that do not correspond. Naming the table first means everything below it is the lookup that already works — its search, its second lines, its pictures, its Advanced Lookup grid.

The tables sit inside the panel rather than above the box, so they take no room on the form when nobody is picking. That is why a chosen record shows its table's icon beside it: with the chooser out of sight, the icon is what still says whether Ada is an account or a contact. It is the icon you gave the table in the Table Designer, so give your tables distinct ones if they are going to share a multi-table lookup.

Switching tables clears the pick, deliberately: a contact's id is not an account's.

What is stored​

Two things, in two columns: the record's id, and which table it is in. You will see the second one if you query the table directly — it is your column's name with _type on the end, holding the table's logical name.

That is the same shape the platform already uses for a record's owner, and it is why the field is a separate data type rather than a Lookup with a longer list of targets.

The one real trade-off: no foreign key​

An ordinary Lookup gets a real database foreign key, so the database itself refuses to let the record it points at be deleted out from under it. A multi-table lookup cannot have one — a single column cannot reference several tables — so:

  • The platform still checks, on every save, that the record you picked exists.
  • But if somebody deletes that record afterwards, nothing stops them. The field then shows the raw reference instead of a name, which is the same thing an ordinary lookup shows when its target is gone: visible, not silent.
  • Deleting a whole table is still blocked while a multi-table lookup lists it as a target, the same as for an ordinary lookup.

If you only need one table, use a plain Lookup. You get the foreign key, and the designer will tell you so rather than letting you build a weaker column by accident.

Each ticked table gets its own relationship, so a record on any of them offers a Related tab listing the records that point at it. An Account form shows its Customers; so does a Contact form. Nothing extra to set up.

Owner is one of these​

A record's Owner is a multi-table reference — a person or a team — and it is shown with this same control on any form that places the Owner field. Picking a new owner there does the same thing as the Assign command, and goes through the same checks; see Change who owns a record.

Troubleshooting​

  • "Tick at least two tables." — with one target this is an ordinary Lookup that has given up its foreign key for nothing. Choose Lookup instead.
  • The tables are greyed out when I open an existing field. — the target list is fixed once the field exists, for the same reason a Lookup's single target is: records already hold references that were checked against this list, and removing a table would leave some of them pointing at something the field says it cannot reach. Delete the field and add it again.
  • The picker shows logical names instead of table names. — the environment's table list has not loaded yet. It resolves a moment later; if it does not, reopen the form.
  • The field shows something like contact:9f2c… instead of a name. — the record it points at has been deleted. Pick another.
  • A table I want is not in the list. — the field's own table is excluded, the same as for an ordinary Lookup. Everything else in the environment is offered.