How to setup many-to-many relationships

One-to-Many relationships are very straightforward, can be setup with a few clicks and are described in the ‘Master-Details Relationships’ tutorial. However, many-to-many relationships are not as intuitive and will be a subject of this post.

Let’s look at an example where we have multiple offices and employees. One employee can work at multiple offices and an office can have more than one employee.

To handle this many-to-many relationship the first step would be to break it into two one-to-many relationships creating a new link table, which combines the employees and the offices.

Now, we will have three tables: an Employee table, an Office table and an Office_Employee link table.

In the generated application you can browse the offices, see which employees work at which office, as well as assign new and delete existing employees from any office. If you are assigning an employee that is already assigned to an office, the application will throw an error and you will have to select a different employee.

Now, lets set it up in the software.

You will need to add all three tables to your project. Then you will need to setup the master-detail relationship between the Office and Office_Employee table using the ID and OfficeID as link fields. Don’t forget to display inline the child records on the list page.

Even though we just started and only configured the master-details, if we build our project now we will see that some functionality is already there. Our application has three tables, each with its own core functions such as add, edit and delete.  Although not user-friendly just yet you can already see the employee IDs assigned to a particular office. Even the inline add functionality is available in its basic form. Now we can build on what we already have making our application more user-friendly.

The next step is to modify the Office_Employee SQL query to pull in a FullName and Phone Number from an Employee table. The Phone Number will be visible when you assign the employee to an office.

SELECT
Office_Employee.ID,
EmployeeID,
OfficeID,
FullName,
Phone
FROM Office_Employee
INNER JOIN Employee ON Office_Employee.EmployeeID = Employee.ID

Lets set the EmployeeID and OfficeID as the key fields on the ‘Choose pages’ steps. I also enable add, inline add and delete pages.

On the Choose fields screen we select an EmployeeID and Phone Number fields to appear on the List and InlineAdd pages. We won’t need any other fields, therefore you can remove them.

Now proceed to the Visual Editor tab and setup EmployeeID as the Lookup Wizard selecting the FullName field from Employee table to be a Display Field. I will also enable ‘Autofill’ of the Phone number to be selected alongside the Employee.

Since we cannot actually save the Phone Number field in the Office-Employee table as this fields doesn’t exist I will exclude it from the insertion in the events editor under the Before Record Added event of the Office-Employee table. The following line of code takes care of this:

unset($values["Phone"]);

To prevent the user from assigning the same employee to an office twice I will insert the validation code to the same Before Add event, which will throw an error in case of a duplicate.

unset($values["Phone"]);

$sql = "select * from Office_Employee where EmployeeID=".$values["EmployeeID"].
" and OfficeID=".$_SESSION["Office_Employee_masterkey1"];
$rs = CustomQuery($sql);
if ($data = db_fetch_array($rs))
{
$message="This employee already assigned to this office";
return false;
}

return true;

Now our project is ready to be built and you should have a better idea of how to handle the many to many relationships.