Creating Reference Columns

Tables in TeamDesk can be linked using reference columns, allowing each record in the first table to be associated with a record in the second table. Reference columns provide a straightforward method to establish relations between tables. They optimize the database structure, simplify data entry processes, and help prevent errors by enabling you to select previously entered data.

For example, let’s say you manage an advertising agency and use TeamDesk to keep track of projects and clients. In your business process, many projects can be ordered by each client. In this scenario, you can create a Single Reference (Many-To-One) column.

There are also cases where a Multi-reference (Many-To-Many) column might be more suitable. For instance, if you hire conference interpreters, each person may speak multiple languages, and each language can have many speakers (persons). To manage language skills in your TeamDesk database, you would create a Multi-Reference column.

To create a reference column, you can use the New Reference button on the Columns form:

New Reference

Alternatively, you can follow these steps:

  1. Click the Setup link in the top right corner of the window.

  2. Select the desired table (e.g., the "Projects" table).

  3. From the setup menu, choose Columns > Create a new reference column.

When creating a reference column, the following properties have to be defined:

Reference to
Select the reference table that serves as the master table, where records will be selected from.
Cardinality
Select the type of the relationship that needs to be established:

Reference Column Form

Many-to-One (Single-Reference)

If you choose Many-to-One cardinality, a reference column allows you to choose and display records from the master table within a detail table.

For instance, if you have a "Projects" table that contains project information and a "Clients" table with client profiles, you can create a reference column in the "Projects" table that references the "Clients" table.

Clients and projects reference

When a Single (Many-To-One) reference column is created, TeamDesk relates records based on key column values, which are always unique. Typically, the Id column is set as the key column, resulting in the reference column storing Id autonumbers. To present related data conveniently in forms and views, TeamDesk creates a Lookup column and sets a Proxy column.

So, with the "Client" reference column added, you can edit records in the "Projects" table and choose a client for each project. In edit mode, the "Client" reference column is displayed within the form, featuring a standard record picker for selecting from a list of choices.

Reference choices example

In the "Projects" table, the chosen client names are displayed in the "Client Company" lookup column as links to client profiles. Additionally, the "Client" reference column, which holds key column values, is hidden in the default view by default.

List of choices

In the "Clients" table, you can review all projects ordered by a specific client by clicking the View button next to a client, where you’ll find the Projects section displaying all related projects.

Projects table with a reference to the Client

Many-to-Many (Multi-Reference)

Choosing Many-to-Many cardinality allows a reference column to establish multiple connections between records in a master table and records in a detail table.

For example, consider a situation where a person speaks multiple languages, and each language has many speakers (persons). To implement such a relationship, you would need an additional "link" table, such as "Languages Persons," which references a person as (one)Person - (many)LanguagesPersons and references a language as (one)Language - (many)LanguagesPersons.

Many-To-Many Relation Form

In edit mode, such a column is displayed within the form, featuring a standard record picker and an Add button for adding selections to the list of choices, along with the previously made selections beneath the picker.

Record Example

In view mode, the column displays a comma-separated list of choices, with each choice presented as a link to a record. The column adheres to access rights defined for the link table, meaning that if record deletion is disabled, the Remove button won’t appear for already stored choices.

Let’s delve into how TeamDesk creates a multi-reference column, using the Interpreting and Translation Agency database as an example.

Many-To-Many Cardinality

Suppose you want to add a "Languages" multi-reference column to the "Persons" table. When you select "Languages" as the table and Many-To-Many cardinality and save it, TeamDesk creates a link table: "Languages Persons," two relationships: "(one)Person - (many)LanguagesPersons" and "(one)Language - (many)LanguagesPersons," and a Multi-Reference column in the "Persons" table. You can see the link table and relationships, allowing you to extend the table with new columns, views, or modify relationship settings as needed.

New Many-To-Many

In some cases, link tables and relationships may already exist. In such situations, the system will suggest using an existing link table or creating a new one.

Existing Linked Table

When creating a Multi-reference (Many-To-Many) column, TeamDesk relates records based on key column values, which are always unique. Frequently, the Id column is set as the key column, resulting in the reference column storing Id numbers. To present related data conveniently in forms and views, TeamDesk sets a Proxy column.

Another way to create a multi-reference column is to convert an existing one-to-many relationship. If your initial database design specifies a single language that a person speaks (that’s a (one)Language-(many)Persons relationship), and you decide to take advantage of a multi-reference column, you can select the reference column of the one-to-many relationship and perform a single-step conversion using the Convert to Multi-Reference button.