 |
Online Help System
|
 |
Back to Brilliant Database Web-Site |
Write to Support |
Help Index
Relational Fields
It is recommended that you read the Form Editor
section first.
The program provides three types of relational elements to define database software records relations, combine database software records into groups and work with these database software recordsets:
Many-to-Many Relational Field
Introduction
With the help of Many-to-Many Relational Fields you can create relations between
database software recordsets. What does it mean? For example, you want to create a database of books. To do this, you would need to create two types of database software records, i.e., Book and Author. Since each book may be written by several authors, and each writer may be the author of several books, you would need to set a conformity between the database software recordsets of the Author type and of the Book type.
Below is a graphical illustration of this process:
According to these relations, the user can affirm that:
- Author 1 wrote Book A;
- Author 2 wrote Books A and B;
- Author 3 wrote Book B and C.
Or, in a different way:
- Book A was written by Authors 1 and 2;
- Book B was written by Authors 2 and 3;
- Book C was written by Author 3;
Establishing such relations allows you to get the answers to the following questions:
- Which books did this particular author write?
- How many books are written by this author?
- Who are the authors of this particular book?
- You will also be able to answer such "exotic" questions as: "What is the average number of pages in the books of this author?"
In exactly the same way, you can connect more than two types of database software records, such as Products, Customers and Orders.
Step-By-Step Relational Database Creation
This section contains a detailed instruction of how to create the Books and Authors database described above.
1. Switch to the Form Editor mode.
2. Create the Book form and then place the following elements on it (see the Form
Editor section):
- Book Name - text field;
- Pages - numerical;
- Cost - numerical.
It is also recommended to give some space for the many-to-many relational field Authors.
3. Create the Author form and place the following elements on it:
- Author Name - text field;
- Books - many-to-many relational field;
- Books Number - mathematical field;
- Total Page - mathematical field;
- Average Pages/Book - mathematical field.
When creating the Books field, the program will ask you:
- To which type of database software records the specified type of database software records will be related? Select Author.
- Which fields of the database software records should be displayed? Specify all existing fields (Book Name, Pages, Cost), and then set their width and position, in the way it is shown on the picture above. To learn more, see the Table Style Editor help
section.
Use the following formulas to extract data from a relational field when creating mathematical
fields:
- mmrMathOp(|Books|,|Pages|,|Sum|) - to calculate the number of pages written by a particular author;
- mmrMathOp(|Books|,|Pages|,|SimpleAverage|) - to calculate the simple average number of all pages from all books;
- mmrRecN(|Books|) - to calculate the number of books written by a particular author.
- more...
4. After that, return to the Book form, and add the Authors field, specifying the Book form as a source:
Nothing depends on order in which the relational fields are created. Moreover, information about connections between database software records is stored in the database software record itself, not in the relational field. This means the following:
- If a relational field is deleted, the connection between database software records does not
disappear;
- A relational field can be used in one form, too. If the field is added to a different form, it will display connections between this particular form and the database software records. For example, if the Books-Authors is created, using only one relational field (e.g., Books), and then the Authors field was added to the Book form, it will be automatically filled with the authors, who wrote this particular book.
Working with the Many-to-Many Field
After the forms are created, close Form Editor and switch back to the database mode.
There are two ways to connect two database software records (add a database software record to a relational field):
- Double-click the table heading in the relational field;
- Right-click many-to-many relational field, and then select Add
Item... in the menu:
Accordingly, to delete a connection between 2 database software records, you should right-click the database software record in the relational field, and then select the Remove selected item menu item:
As stated above, relations between the database software records are equivalent, which means that it does not matter either you add authors to a book description, or you add books to an author description, the result will be exactly the same. The same way is also appropriate for deleting
relations.
The contents of the many-to-many fields can be changed by using
scripts.
View example.
One-way connections can be created by using a simple Relational Field.
What is Relational Set?
For example, you want to distinguish between two types of authors for a book:
the major authors and minor authors. For this purpose you will need two many-to-many
fields in the book form. One of the fields will store the major authors, while
the other field will store minor authors.
To make each of these fields independent of each other, you have to define different Relational Sets for them. This can be done on the Individual Properties tab of the field:
By default Relation Sets have standard names Primary, Secondary, etc, but for convenience we will rename them into Major Authors and Minor Authors. Now the relations structure will look as follows:
Now, when you work with the many-to-many field, you will have an additional option at your disposal:
Move selected to - this action moves database software records from one mmr field into another. In our case using this action you can move an author from the Major Authors field into Minor Authors, and vice versa. Simple Relational Field
Simple Relational Field allows to use in the database software record the data taken from other database software records. For example, you have such database software records as Client type, and such ones as Order type. In the Order database software record it will be convenient to create a Relational Field which will contain the link to the database software record about the client. Thus, the list of clients will be the source for the Order database software record.
The field has the following parameters:
- Source folder - a folder from which the data for the current field will be chosen by default (in the case it can be the Clients List folder);
- Source database software record type - the database software record type which will be used as a source (in the case this type is Client type);
- Field to show - the source database software record field which will be displayed in the Relational Field (in the case it can be Client Name or Client).
Relational Lookup Field
This field is used only together with the Relational Field or Many-to-Many Field, and is
used to display the source database software record(s) fields. In the case mentioned above, we can use the Relational Lookup Fields to display the client's age and address:
The field has the following parameters:
- Source Relational Field - Relational Field (one or many) used as a source for this field, Field (in the case mentioned above it is the field created above, containing the link to the client);
- Field To Show - the source database software record field which will be displayed in the Relational Lookup Field. This parameter is completely similar to the Field To Show parameter for the Relational Field, presented above. If a many-to-many field is selected as the source, the values will be separated by comma (",").
| 
