{Header}
This example shows how to implement a sample record form that allows users to assign multiple projects to an employee using two Listboxes. This is an example of a many-to-many relationship whereby one employee can have many projects and one project can have many employees.
To assign projects to an employee, select an employee from the list to the left, then select multiple projects by holding the CTRL key and clicking on the project in the list. Click on the ">>" button to move projects to the 'Assigned projects' list. Note that can also remove projects from 'Assigned projects' by selecting them and clicking on the "<<" button. When done, click “Submit” to save the selection.
To add a new employee and assign projects to him\her, click the "Add New" link and enter the name of the employee in the 'Employee' field, then assign the projects as described above . When done, click "Submit" to save the new record.
Database: Intranet
Tables: projects, employees, projects_employees
The Before Build Select event of the AvailableListBox ListBox is used to show only the projects bound to the current employee.
The ProjectEmployeesModify function is used to simplify the updates performed by the events below.
The record form’s After Update event is used to retrieve the ListBox values and update the many-to-many relationship table, in this case projects_employees.
The record form’s After Insert event is used to obtain the last inserted key and update the many-to-many relationship table when a new record is added by a user.
Note: you can use several methods to retrieve the value of the last key inserted into the database, which is the current project_id. Although many databases have appropriate functions for retrieving the last inserted key within the current session, some databases do not support this. As such, you can simply retrieve the highest key value in the table and assume that it is the key last inserted by the current user. This method can be improved to be more reliable, though this is not the purpose of this example.
The record form’s Before Delete event is used to delete records from the many-to-many relationship table whenever a project is deleted.