Relations

How values from one table are carried over to another through a relation – linking records, pulled-in columns and two-way relations.

A relation links a record in one table to a record in another table and then brings the values from that other table over to you – without retyping. In the Orders table you just pick a client for each order, and their email or phone number shows right next to the order, even though they are written only once, in the Clients table.

How data gets from one table to another

A relation does not copy data – it links records and reads the values from the other one. In the blue-shaded columns below you can see the same details that live in the Clients table, as they appear on the orders:

Clients – this is where the details live
ClientEmailPhoneAlfa s.r.o.fakturace@alfa.cz+420 601 111 222Beta a.s.nakup@beta.cz+420 602 333 444

You pick a client for the order – and the columns you chose from Clients show up on it straight away.

Orders – this is where the data is brought in
OrderClientfrom ClientsEmailfrom ClientsPhonefrom ClientsOrder 1042Alfa s.r.o.fakturace@alfa.cz+420 601 111 222Order 1043Beta a.s.nakup@beta.cz+420 602 333 444Order 1044Alfa s.r.o.fakturace@alfa.cz+420 601 111 222

The Order column belongs to the Orders table; Client, Email and Phone are read from the Clients table. When a client changes their phone number on their side, it is updated on all their orders – the detail lives in one place and is not duplicated.

In short, it works in three steps: you link a record (you pick a client for the order), the selected columns from the other table show up on it (email, phone) and their values are read from the other table, so they stay up to date. A relation is therefore useful wherever one object is related to another and you do not want to maintain the same details in two places: an order and a client, a project and a task, a company and a contact, an invoice and a supplier.

Creating a relation

You create a relation in the table you want to bring data into – in our case Orders. Adding a relation and editing it later both require permission to edit the table structure.

  1. In the table header, add a new column ( Add property).
  2. In the type menu, hover over Link to another record and choose the table you want to bring data from (Clients).
  3. In the Link to another record dialog, tick the columns to bring over. The record name (primary column) is always selected – it tells you which record from the other table is linked.
  4. Enable selection of multiple values allows a link to several records; if you leave it off, exactly one can be selected.
  5. Show the column in source table as well adds the relation to the other table too.
  6. Confirm the dialog with Add.

The new column is named automatically after the other table (for example Name (Clients)). You can rename it and change which columns are brought over at any time via Edit relation.

One record or several

The Enable selection of multiple values option decides how many records the relation can point to. If you leave it off, the relation points to a single record and suits unambiguous relationships, where an order belongs to exactly one client; when you try to swap an already selected record, DATIFY prompts “Single relation is already filled. Remove the existing value first.” With the option on, you can link several records at once, which suits cases where one record relates to several others – a client has several orders, a project several tasks.

Bringing data over in both directions

By default, data is brought only into the table where you create the relation. If you turn on Show the column in source table as well when creating it, the relation exists on both sides: you see the client on the order, and in the Clients table you also see their orders. Editing the relation on one side is reflected on the other. Turn on a two-way relation where both tables work with the link; otherwise a one-way relation is enough and the other table stays without an extra column.

Linked records in the detail

In the record detail you will find linked records on the Relations tab and can open them from there. You remove a link with Delete relation – this only unlinks the two records; the target record in the other table stays. If the table has no relation column yet, the Relations tab says “You don't have any column of relation type.”

Access to the linked dataset

A relation does not copy values, it only reads them from the other table, so it follows the permissions on that table. The pulled-in values are shown to anyone who has access to the linked dataset; the Reader role is enough. Without access to it, the relation columns stay empty, both in the table and on the Relations tab in the record detail. The user sees the record's other columns unchanged.

So when a relation column is filled in for one colleague and empty for another, the deciding factor is usually access to the linked dataset, not the content of the data. It can be granted by a Manager of the dataset or of its collection, as described in How to set permissions.

Before you delete a relation

Removing a value from a relation is safe – it only unlinks two records.

Deleting a whole relation column is a bigger step, but no data disappears. A relation does not copy data, it only reads it from the other table, so deleting the column loses the link, not the content: the pulled-in values are no longer visible in your table, and the same applies on the other side of a two-way relation. The clients' emails and phone numbers stay where you have them written – in the Clients table – they just no longer show on the orders.

What you lose is which record was linked to which. Setting up the relation again means creating the column and selecting the other record again for each record. While the edit stack described in Undoing changes is still valid, one step back restores the whole column, including the links. If you simply do not want to see the relation in one view, do not delete the column – hide it.

On this page