Editing Relationships |
Top Previous Next |
The Edit Relationship dialog is shown when adding or editing a relationship from Setup Relationships.
Here you define the From and To details, and optionally designate it as a "unique" relationship. Mechanically it's as simple as selecting the tables and fields from drop-down lists. However it's important that you understand what to select and hopefully why (as well as how relationships work in databases, which is beyond the scope of this document).
From Table -- Select the table in which the "link" field is located that will refer to the related table. In the example of a Membership Type pick list for customers, this would be the "Customers" table.
From Field -- After selecting the "From Table" (so this list is filled with the appropriate fields), select the "link" field. In the Membership Type example, this would be the User field you added to customers for membership type, with the type "Pick List item (Enum)". The field selected here must be one of 3 types:
•Pick List item (Enum) - specifically for use with Pick Lists, but technically could be used for any relationship where you would normally want to have the user select one record from the related table, and multiple records could refer to the same record in the related table. This is usually a many-to-one relationship.
•Link to Record - a general link to a single record in the related table. For instance, the Reservation record has a single link to a Customer record. This is often a one-to-one relationship but can be a many-to-one back-link in a one-to-many relationship (as in the case of Reservation-to-Customer).
•Linked List of Records -- a general link to any number of records in the related table. For instance, the Customer record has a linked list to many Reservation records. Naturally this is a one-to-many relationship.
To Table -- Select the table being referred to by the link in the "From Table". In the Membership Type example, this would be the Membership Type table (pick-list).
Note: there is no "To Field" selection, because the To Field is always the Record ID. Every table has a Record ID as its index field.
Display Field -- After selecting the "To Table" (so this list is filled with the appropriate fields), select the field in the "To Table" that will be shown as the text for the linked record, e.g. when displaying the From Field. In the Membership Type example, or for any Pick List type table, this would be Selection Name. So for instance in a Query or a Form definition or anywhere you use the expression function FieldText( ) for the "From Field", instead of just displaying the Record ID of the linked record it will show the text from the field you have selected here.
'To' record is only referenced by the 'From' record -- This should only be selected if the related record ("To" record) will only be linked from one and only one "From" record (i.e. a one-to-one or one-to-many relationship), and there's no reason to keep the "To" record if the "From" record referring to it is deleted. If this box is checked, it is considered a "unique relationship", which means several things are generally handled automatically by the system (see exceptions below):
•Whenever the "From" record is locked (e.g. for editing), the "To" record is also locked.
•If the "From" record is deleted, the "To" record is also deleted.
•If the "From" record is Exported, the linked "To" record is also Exported (and Imports along with it as well, as a new record).
Naturally this option should not be used for Pick Lists, since many records will refer to the same Pick List item. One typical application having this option selected would be a list of Children linked from a Customer. Presumably each child record only belongs to one customer (and thus only linked to that customer and no others), so if the Customer is deleted their linked children should be deleted along with them and not left in the system as orphans.
Exceptions to automatic functions -- The automatic locking or deleting of the linked record only occurs with "normal" user interface functions, such as through the standard Record List or Edit Record dialogs. It will not occur if using Raw Data Tables to manipulate the data, or if using functions like DeleteRecord( ) directly within a Script.
Additional Topics:
User-defined Relationships overview
Advanced Customizations Overview & other topics