Creating Relations

Say in your database you created a table called Projects comprising information about projects and the Clients table where clients’ profiles are stored. Every project is ordered by a client; some clients order many projects or make repeat orders. To link the Projects and Clients, create a relation between these tables.

To create a new relation:

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

Select a table you need.

From the setup menu select Tables > Manage relations between tables.

Click the New button on the Relations form.

Select the tables to set relations between; click the Next button:


Select the type of a new relation; click the Next button:


Specify properties of master-detail relationship between selected tables:

The options are described below:

Option

Description

Name

Enter a name that will identify relationship between two tables.


Master Table options :

Table

This field displays the master table name.

Key Column

Here the master table key column is displayed

Proxy Column

Use the Proxy Column to specify that another field containing more accurate description should look and act like the reference field when you view a record data on the View Form .

By default the Name column of the master table is specified as the Proxy Column. Therefore, when a reference column is created, the Lookup column referring to this Proxy Column is created automatically. Let’s call this lookup ‘Proxy Lookup’. So, in the most of cases, after a reference column creation you get two new columns: one ‘reference’ and one ‘lookup’ (proxy lookup).

Record Picker

The Record Picker section defines how the master table records will be organized for the reference column of the details table:

  • Select Simple Choices if values should be accessible via a drop-down list.
  • Select Dropdown with ‘Browse Choices’ option if values should be selected from the browse window, not from the drop-down list (for more information, see the Record Picker section).
  • Select Type-in with ‘Search’ button to enable search for the reference field of the details column.

The Default Record Picker Options are applied to the reference column by default. You can set the default record picker according to your needs, but there may be cases when such default option should be override. To set the specific record picker option, check the Override default record picker checkbox (for more information, see the Record Picker section).


Detail Table options :

Table

This field displays the detail table name.

Reference Column

Select the name of the Reference column. A reference column is a column in the detail table containing a link (a reference) to a master table record.

To hold reference values, TeamDesk can create a new reference column or a user can select an already existing column in the dropdown. If you want to set an existing column as a reference column, please notice that the type of the Reference column should match the type of the Key column specified in the relation. So, you can match the same column types. Moreover, the following types can be matched with each other: Text type, AutoNumber, Phone, E-mail Address, Barcode.

Details View

Select a view in the Details View list to see records from the detail table below the master table view form (for more information, see the Displaying Details section).

Alternative Name

In the Alternate Name field enter an alternative name for detail table records block (for more information, read the Displaying Details section).

If a relation between tables is deleted, the reference column will still be present in the detail table. You can delete this column from the column listing, just select Columns > Customize existing columns link.

Next: Matching Columns