Home      FAQ      Forum      Idea Exchange      Ask a Question      My Stuff      Help   
  
Relations between tables
Question
ID
15
Category
TeamDesk
Date Created
4/17/2006 8:00:57 AM
Date Updated
4/25/2006 1:54:53 PM
How to create relations between tables?
Answer
To create a relationship your application has to have at least two tables. You may use either "Tables" -> "Manage relations between tables" menu item from a left side bar to see a complete list of relationships, or "Table" -> "Manage relations" from a table main menu to see the list of relationships belonging to the selected table.

To create a relationship, click a "New" button on the top of the relationships list and follow the steps of "New Relation" wizard.

Step 1: Select Tables.

You have to select two tables you want to bind.

Step 2: Select the type of relationship.

Select one of first two types to specify master-detail relationship type. It is used to describe cases where zero, one or more records from detail side belong to one and only one record from master table. For example, in "Charitable Organization" application each Donation made by a particular (one and only one) Donor, while Donor can make one or more Donations (or do not make any). For this relationship types binding is created between key column of master table (to ensure that only one record can be selected) and any column of matching type in detail table.

Many-to-many relationship type (the last one) is used to bind records when the values match in each table's selected columns.

Step 3: Select properties for the relationship.

* Referencing Column.

Select the column in 'detail' table to hold the reference to the master table's record. You may select from the existing columns of matching type, or create a new one.
When viewed, this column will be rendered as a link to view master record if exists. When edited, the column will be rendered as a drop-down list or a record picker, depending on "Use the Record Picker rather than a drop-down list" checkbox setting.

* Use the Record Picker rather than a drop-down list.

Detail record reference column is rendered as a drop-down list in the detail record's edit form by default. If you have many candidates for the master record you may want to use "Record picker" window to allow users searching for the record.
To setup what columns to display in the drop-down list or record picker, use "Record Picker" view (Setup -> Views / Reports -> "Customize record picker" view).

* "Detail View" and "Alternate Name".

When "Detail View" is selected, users will see the list of detail records below the master record's view form. The title for the list will be the name as for the view, unless an "Alternate Name" is provided.

In case if you have several details to the master record you may control the order, in which details are displayed via relationships order (Setup -> Tables -> "Manage relations between tables" -> "Reorder" button).

Click "Save" button to finish the wizard. TeamDesk will navigate to the relation information screen.

Information screen allows editing relationship properties as well as adding lookup and summary columns and match conditions.

Lookup columns used to "pull" information from the master table into detail table (for example, Donor's Name - into Donation table). Click "Add Lookup" button to add lookup column. Later you may adjust column properties by clicking "Edit" button either on this screen or from master table's column list.

Master table Summary columns allow calculations on the set of related detail records (records count, total and average number, minimum and maximum value). Click "Add Summary" button to add 'summary' column.

'Match Conditions' are used to add additional restrictions to the existing column-to-key binding by specifying columns in one table that should match columns' values from another table.

It is possible that during editing process, columns listed in match conditions are changed and selection lists should be re-evaluated. For such cases, TeamDesk has "Refresh" button in a form that, unlike to the "Save" button, calculates formulas and lists without submitting data to the table.
Related articles
Was this information helpful?
 
Back to Search Results