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
- 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.
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.
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 Relationships
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.
Displaying Sub-Records of Sub-Records
Assume that you have a Customer catalog having a 1 to Many relationship to Invoice catalog.
Assume that also the Invoice catalog having a 1 to Many relationship to Order Items catalog.
In this configuration, each customer record would display related invoice records via a menu button. Each invoice record would also
display related order items of that invoice via it's menu button. This bring the following question:
While displaying a customer record, how can you access the list of all "order item" records which belong to any of the invoices of that parent customer?
SpeedBase has a solution to view "sub record of sub records".
The steps given below use the catalog names given in the example above. You may apply the same steps for any set of three catalogs which
have the same relationship type.
- Open any record from the top parent catalog, ie. Customer catalog, click "config" button, select "menu items",
- On "menu configuration" window, select the "grandchild" catalog, ie. Order Items catalog, click "Add" button.
When asked, click "do not create relationship" button.
- Select the new menu item (order items) you've just added from the box on right, click "Views" button.
- View Management window will open, click "New View" button, add the desired fields and then save the view.
- Select the the view you've just added, click "Filter Settings" button.
- To create the filter, select "Parent Invoice" from the first selection box, then "Parent Customer" from the next selection box and then
"Current Item" from the next selection box. Note that the actual field names you must select depend on the names you used for the
relational lookup fields on each catalog.
- Save the filter and exit all configuration windows.
After completing the steps shown above, a new menu button to display order items specific to the current customer record will appear.