Creating 1 to Many Relationships
You are recommended to read "Understanding Relationships" before this section
if you haven't already.
Recommended video: Creating Relationships
How to Create 1 to Many Relationship
Review the displayed information carefully to make sure that the suggested relationship properties reflect what you want to do.
If you want to do the opposite, just click the button "Invert Relationship". This will interchange the catalogs to create the opposite.
- Click "Design" from main menu, select "Relationships".
- The relationship management window will open. Click "New Relationship" button.
- Select the parent catalog (a.k.a master table) you wish to create a new relationship for.
- Select the relationship type as "1 to Many Relationship".
- Select the child catalog (a.k.a detail table).
- Select a lookup field for the parent catalog.
You may change the suggested menu and lookup field titles however, you are recommended to leave the recommended
naming convention as it is if you are a new user. You will be able to change the display name any time you want.
Example: If you are creating a relationship for "Customer" and "Order" catalogs respectively,
this will display the history of all orders on each customer record.
What is a lookup field? The lookup field helps you to select a parent record for the child record.
Example: If you are creating a relationship to save order history for each customer, the lookup field is displayed on Order records.
This field will display data from one of the fields from Customer catalog. You are recommended to choose a lookup field,
which can help you most to identify the record you wish to relate. So in this example "Customer Name" would be a good choice.
Displaying Menu Button and Lookup Field on Record Window
When you create a 1 to Many relationship, a menu button is automatically added to the record window of
the parent catalog. Clicking it will display the related records from the child catalog.
You will also be able to either create new or add an existing record to the related record list.
When you create a 1 to Many relationship, a relational lookup field is also created for the child catalog.
Beware that however the lookup field will NOT automatically appear on record details window!
You must add it into the record form from Form Designer window.
In the example above the menu button Related Orders will appear on each Customer record whereas
the customer lookup field Parent Customer can be placed on the record window of Order catalog.
Modifying a Relationship
Once created, changing the relationship type (1 to N or N to N) or changing any of the catalogs is not allowed.
If you need to change these, you should consider deleting the existing relationship first which will also
remove all the related data information from records.
You may at any time rename menu titles as well as lookup field titles.
Deleting a relationship deletes both the relationship menu button on parent catalog and relational lookup field on child catalog.
No records are deleted from your database however, you will lose the "related data" information from all records on both catalogs.