Creating Reference Columns

Two tables can be linked via reference columns, in this way each record stored in the first table can be connected with a record stored in the second table. Reference columns are a simple way to create relations between tables. They optimize database structure, simplify data entering process and help to avoid mistakes, because you can select already entered data.

Say you manage an advertising agency and keep track projects and clients in TeamDesk database. According to your business process many projects can be ordered by each client, in this case the Single Reference (Many-To-One) column can be created.

There may be cases when a Multi-reference (Many-To-Many) column meets your needs better. For example, you hire conference interpreters and each person speaks many Languages while Language has many speakers (Persons). To keep track language skills in your TeamDesk database, create a Multi-Reference column.

For creation a reference column you can use the New Reference button on the columns form

or you can use the following steps:

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

Select a table you need (for example, the "Projects" table).

From the setup menu select Columns > Create a new reference column.

Select the reference table that is the master table where the dropdown records will be chosen from.

When you want to create a reference column you have to choose the type of the relation Many-to-One or Many-to-Many

Many-to-One (Single-Reference)

If the Many-to-One cardinality is chosen, a reference column allows choosing and displaying records from a master table in a detail table.

Say you created the Projects table comprising information about projects and theClients table where clients’ profiles are stored. Each project is ordered by a client; some clients order many projects or make repeat orders. To link the Projectsand Clients tables, create a reference column in the Projects table referring to the Clients table.

When a Single (Many-To-One) reference column is created, TeamDesk relates records by key column values, because such values are always unique. Most frequently the Id column is set as the key column therefore the reference column keeps Id autonumbers. To represent related data in a handy and intuitive way on a form and in a view, TeamDesk creates a Lookup column and sets a Proxy column.

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

In the "Projects" table the chosen client names are displayed in the "Client Company" lookup column as a link to a record (to a client profile). Moreover, by default the "Client" reference column keeping key column values is hidden in the default view.

In the "Clients" table you can review all projects ordered by a certain client. Click on the View button near a client and you’ll see the Projects section, where all related projects are displayed.

Many-to-Many (Multi-Reference)

If the Many-to-Many cardinality is chosen, a reference column allows multiple connections of records in a master table with records in a detail table.

The simplest example is: a Person speaks many Languages while Language has many speakers (Persons). Having Persons and Languages tables ready, to implement such a relationship you would need an additional "link" table, say Languages Persons that will refer to a Person as (one)Person - (many)LanguagesPersons and to a Language as (one)Language - (many)LanguagesPersons.

In edit mode such column will be displayed inside the form, featuring standard record picker, and Add button next to it to add the selection to the list of choices and the selection made previously beneath the picker.

In view mode the column will display comma-separated list of choices, each choice is decorated as a link to a record. The column will respect access rights defined for a link table - e.g. if record deletion is disable, the Remove button won’t appear for already stored choices.

Let’s consider in details how TeamDesk creates a multi-reference column, for example, in the Interpreting and Translation Agency database.

Say you want to add the "Languages" multi-reference column to the "Persons" table. When the "Languages" table and Many-To-Many cardinality are selected and saved, TeamDesk will create a link table: "Languages Persons"; two relationships: "(one)Person - (many)LanguagesPersons" and "Language as (one)Language - (many)LanguagesPersons" and, of course, a Multi-Reference column referring to Languages in the "Persons" table will be created. A link table and relationships are visible - you can extend the table with new columns and views or modify relationships’ settings in any way you need.

There may be times, when a link table and relations already exist. In this case the system will suggest you to use an existing link table or create a new one.

When a Multi-reference (Many-To-Many) column is created, TeamDesk relates records by key column values, because such values are always unique. Most frequently the Id column is set as the key column therefore reference column keeps Id numbers. To represent related data in a handy and intuitive way on a form and in a view, TeamDesk sets a Proxy column.

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