Add a Join Page

Last updated 10/04/2024

To add a Join page type you will need to have a many-to-many relationship in your database. A many-to-many relationship occurs when multiple records in a table are associated with multiple records in another table. For example, a many-to-many relationship exists between Employee and Department: employees can work in various departments, and departments can have many employees.

Figure 1 - Many-to-many relationship

A third table is required to resolve this many-to-many relationship. This acts as a middle table. The original many-to-many relationship effectively becomes two one-to-many relationships. The unique identifier (Primary Key) of the Employee and the Department tables are taken on by the middle table as foreign keys. This is represented in the diagram below.

Figure 2 - A join table is created

For this example, we will create an Employees form that has a Departments join page to enable us to assign departments to an employee.

Add a Join Page Type

info

Three forms will be added to achieve a join page.

Add Form One (Departments)

info

The first form, is just a straight forward form and can be added via the Form Wizard or the form.

1. Select Visual in the menu and Form Wizard in the sub-menu.

Figure 3 - Form Wizard menu item

2. Select your data source (Department) in the Main Data Source field.

tip

You will need a menu item for this form to have the ability to add records (departments)!

Figure 4 - Add form one

3. Click the Next button.

Figure 5 - Next button

4. Perform any changes required for your form fields and click the Save button in the Form Wizard app bar.

Figure 6 - Save button

Add Form Two (Employees)

info

The second form, is the form that will have the join page added. To begin with, we will just add the Employees form through the Form Wizard and then edit the record to add the join page.

1. Select your data source (Employee) in the Main Data Source field.

Figure 7 - Add form two

2. Click the Next button.

Figure 8 - Next button

3. Perform any changes required for your form fields and click the Save button in the Form Wizard app bar.

Figure 9 - Save button

Add Form Three (EmployeeDepartments)

info

This form will use your join table as the data source.

1. Select your join table as the data source in the Main Data Source field.

2. Click the menu item switch off.

info

The join form (EmployeeDepartments) will be referenced on the Employees form so it does not need a menu item.

Figure 10 - Add form three

3. Click the Next button.

Figure 11 - Next button

info

Five has automatically referenced both of the other forms because of the foreign relationships defined at the table-level.

tip

Both of the foreign key fields that form the many-to-many relationship must be required and included in the list!

4. Ensure that the Req. and List checkboxes are clicked for both of the foreign key fields.

5. Click the Save button in the Form Wizard app bar.

Figure 12 - Save button

Edit the Employee Form to Add the Join Page

1. Select Visual in the menu and Forms in the sub-menu.

Figure 13 - Forms menu item

2. Select the form (Employees) in the list to add the join page and click the Pages tab.

Figure 14 - Pages tab

3. Click the Add Pages button.

Figure 15 - Add Pages button

4. Give your page a caption in the Caption field.

5. Select Join in the Page Type field.

info

The Action field will now be available for you to select your form (EmployeeDepartments)that is using your join table (EmployeeDepartment).

Figure 16 - Add the join page

6. Click the Save button in the form app bar.

Figure 17 - Save button

7. Click the Save button in the form app bar above the list.

Figure 18 - Save button

How This Works in an Application

Firstly, select the first form (Departments) you added. This is where you add the names that will be positioned next to a switch.

Figure 19 - Add the department names

Next select the menu item holding your second form (Employees), add a new record and click the tab holding your join page.

Figure 20 - Add an employee

Once you click the tab holding the join page, your switches will be available to select.

Figure 21 - Join page

tip

A List page type can also be used for a many-to-many relationship!

Set Minimum and/or Maximum Items to Select

Whe creating a join page, you can define a minimum and/or maximum amount of items that can be selected. The following options can be selected.

• A minimum amount of items with no maximum amount set.
• A maximum amount of items with no minimum amount set.
• A minimum and maximum amount of items. The selected amount of items must fall between the minimum and maximum criteria.
• No minimum and maximum amount of items. Any amount of items, including zero, can be selected. This is the default.

Set Minimum Items

1. Type an integer value in the Minimum Items field.

Figure 22 - Minimum Items field

Set Maximum Items

1. Type an integer value in the Maximum Items field.

Figure 23 - Maximum Items field

Set Minimum and Maximum Items

1. Type an integer value in the Minimum Items field.

2. Type a larger integer value in the Maximum Items field.

Figure 24 - Minimum and Maximum Item fields

How This Works in an Application

If the minimum is not reached, an error will return.

Figure 25 - Error message

If the maximum exceeds, an error will return.

Figure 26 - Error message