Skip to main content

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.


Many-to-many relationship
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.


A join table is created
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.


Form Wizard menu item
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)!

Add form one
Figure 4 - Add form one

3. Click the Next button.


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.


Save button
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.


Add form two
Figure 7 - Add form two

2. Click the Next button.


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.


Save button
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.

Add form three
Figure 10 - Add form three

3. Click the Next button.


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.


Save button
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.


Forms menu item
Figure 13 - Forms menu item

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


Pages tab
Figure 14 - Pages tab

3. Click the Add Pages button.


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).

Add the join page
Figure 16 - Add the join page

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


Save button
Figure 17 - Save button

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


Save button
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.


Add the department names
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.


Add an employee
Figure 20 - Add an employee

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


Join page
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.


Minimum Items field
Figure 22 - Minimum Items field

Set Maximum Items

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


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.


Minimum and Maximum Item fields
Figure 24 - Minimum and Maximum Item fields

How This Works in an Application

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


Error message
Figure 25 - Error message

If the maximum exceeds, an error will return.


Error message
Figure 26 - Error message