Changing relationships

Changing relationships

After you have created a relationship, you can add or change match fields, change relational operators, and set options for creating, deleting, and sorting related records.

Note You can’t change tables within a relationship. You must create a new relationship.

To change a relationship:

1.

Open the relationships graph for the database.

See Working with the relationships graph.

2.

In the relationships graph, locate the relationship to edit, and double-click the indicator line between the related tables to open the Edit Relationship dialog box.

You can also select the relationship in the relationships graph and click .

3.

Edit match fields for the relationship.

To

Do this

Change a match field

Select a new match field and click Change.

Add a match field

Select a new match field and click Add.

Change the relational operator

Select a new relational operator from the list and click Change.

Duplicate a pair of join criteria

Select the paired criteria from the list in the middle part of the dialog box and click Duplicate.

Delete a pair of join criteria

Select the paired criteria from the list in the middle part of the dialog box and click Delete.

4.

Select options for the relationship.

To

Select

Add a related record (to the related table) whenever you enter data into a related field in a record of the current table

Allow creation of records in this table via this relationship. When this option is selected, entering data in a related field that has no corresponding related record creates a related record based on the match field in the current table.

Delete related records (in the related table) whenever you delete a matching record in the current table

Delete related records in this table when a record is deleted in the other table. This option deletes related records even when you're browsing a layout that doesn't display the related records.

Note When this option is selected in multiple relationships in the same database, deleting a record from one table can also delete related records in other related tables for which the option has been enabled. This is called a cascading delete. You should consider this option carefully before enabling it.

Sort related records

Sort records. Then, in the left list in the Sort Records dialog box, double-click the fields to sort. To change the order in which fields are sorted, drag fields in the right list into the order you want. Select other options, then click OK.

To change the sort order, click Specify in the Edit Relationship dialog box.

Selecting this option does not affect the sort order in the related file.

5.

Click OK.

Notes

•

Select Sort records to display the first found record in a particular sort order (such as the lowest or highest value) when you have one related field directly on a layout of the current table. (For example, display the most recent check-out date for a particular piece of equipment.) Also sort related records to access multiple records that are in a particular sort order and display them in a portal.

•

When Sort records is selected and data in a related field in a portal changes, the portal records are re-sorted whenever the relationship is re-established. (For example, records re-sort when you browse out of and then back into the record, or change the value in the match field.)

•

Because relationships are bi-directional, the tables that appear in the left and right side of the Edit Relationship dialog box have equal capabilities.

•

When displaying related records in a portal, the Sort Portal Records sort order takes precedence over the sort order specified in the Edit Relationship dialog box.

Related topics

About relationships

About planning a database

About the types of relationships

About match fields for relationships

Creating relationships

Adding tables to the relationships graph

To	Do this
Change a match field	Select a new match field and click Change.
Add a match field	Select a new match field and click Add.
Change the relational operator	Select a new relational operator from the list and click Change.
Duplicate a pair of join criteria	Select the paired criteria from the list in the middle part of the dialog box and click Duplicate.
Delete a pair of join criteria	Select the paired criteria from the list in the middle part of the dialog box and click Delete.

To	Select
Add a related record (to the related table) whenever you enter data into a related field in a record of the current table	Allow creation of records in this table via this relationship. When this option is selected, entering data in a related field that has no corresponding related record creates a related record based on the match field in the current table.
Delete related records (in the related table) whenever you delete a matching record in the current table	Delete related records in this table when a record is deleted in the other table. This option deletes related records even when you're browsing a layout that doesn't display the related records. Note When this option is selected in multiple relationships in the same database, deleting a record from one table can also delete related records in other related tables for which the option has been enabled. This is called a cascading delete. You should consider this option carefully before enabling it.
Sort related records	Sort records. Then, in the left list in the Sort Records dialog box, double-click the fields to sort. To change the order in which fields are sorted, drag fields in the right list into the order you want. Select other options, then click OK. To change the sort order, click Specify in the Edit Relationship dialog box. Selecting this option does not affect the sort order in the related file.

Was this topic helpful? Send feedback.