 |
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 records relations, combine records into groups and work with these recordsets:
Many-to-Many Relational Field
Introduction
With the help of Many-to-Many Relational Fields you can create relations between
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 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 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 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 records the specified type of records will be related? Select Author.
- Which fields of the 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 records is stored in the record itself, not in the relational field. This means the following:
- If a relational field is deleted, the connection between 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 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 records (add a 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 records, you should right-click the record in the relational field, and then select the Remove selected item menu item:
As stated above, relations between the 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 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 record the data taken from other records. For example, you have such records as Client type, and such ones as Order type. In the Order record it will be convenient to create a Relational Field which will contain the link to the record about the client. Thus, the list of clients will be the source for the Order 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 record type - the record type which will be used as a source (in the case this type is Client type);
- Field to show - the source 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 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 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 (",").
| 
