Index

• Introduction
• Step-By-Step Relational Database Creation
• Working with the Many-to-Many Field
• Many-to-many Field Properties
• Relational Set Option

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 Many-to-Many 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. I.e. If a relational field is deleted, the connection between records does not disappear.

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.

Many-to-Many Relational Field Properties

• Source Folder - folder from which the user can select records for the relational field.
• Relational Set - see below;
• One-way Relation - if this check box is selected, a one-way relation will be created. That is, in the example with Author-Book, if we add a book to the Author, the Author will not appear for the Book. One-way many-to-many relational fields work faster than usual, as in this case, it is not necessary to make changes in the created record.
• Configure Appearence - allows you to customize the appearance of the table using the Table Style Editor in which all related records are displayed.
• Records Input Config - allows you to customize the appearance of the window for selecting records for a relational field. Learn more...

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.