Updating Many-to-Many via Multi-Select ListBox
This example shows how to implement a sample record form that allows users
to assign multiple employees to a project using a multi-select ListBox. This
is an example of a many-to-many relationship whereby one project can have many
employees and one employee can have many projects.
To assign employees to an existing project, select a project from the list
to the left then select multiple employees from the multi-select ListBox. You
can select multiple employees by holding the CTRL key then clicking on each
employee you want to add to the selection. When done, click “Submit” to save
To add a new project and assign employees to it, click the “Add New” link then
in the 'Add/Edit Project' form, type in a name for the new project then select
the employees you want to assign to the project. When done, click "Submit"
to save the new record.
Steps to recreate
- Create a HTML table with two columns.
- In the left cell of the HTML table, use the Grid Builder to create the
grid based on the "Projects" table and its two columns: project_id and project_name.
- Change the project_name field type from Label to Link, then set the
current page as the page name in its Href Source property.
Also add a Link Parameter with the following property values: Data
Source Type = DataSource Column, Parameter
Source = project_id and Parameter Name = project_id.
- In the bottom row of the Grid, add a Add New Link
control and set it's Href Source property to the current
page and add the text project_id into the Remove Parameters property.
- In the right cell of the HTML table, use the Record Builder to create the
'projects_rec' record form with the project_name field.
- Enter project_id in the Remove Parameters property of
the record form.
- Insert a new row into the record form (select "Insert row" from the
context menu which appears for the mouse right-click) and move it below the
project_name control (ALT + Arrow Down ).
- In the left cell of the new row, enter the text 'Employees' then in the right cell,
add a ListBox control called employee_list.
- Under the Format tab of the Properties window, activate the multiple property
of the employee_list ListBox and set its Size property to 8.
- Switch to HTML mode and locate the code for the employee_list ListBox
then delete the <option> tag for the default 'Select Value' option (i.e.
<option value="" selected>Select Value</option>).
- Switch back to Design mode and for the employee_list ListBox, set
the Connection property to IntranetDB and select employees
in the Data Source property. Select emp_id in the Bound
Column property, emp_name in the Text Column Property
and Integer in the Data Type property. Also set the Source
Type property to Code Expression.
- Add the appropriate programming code into Before Show, After Insert,
After Update and Before Delete events of the record form as
shown in the example. Note that you also need to copy the function ProjectEmployeesModify,
which should to be added anywhere in the event file, preferably between Events.
- Make cosmetic changes as needed, by modifying the text and captions within
Database Tables used
Tables: projects, employees, projects_employees
The record form's Before Show event is used to retrieve the existing
database values and select the appropriate values for the multi-select ListBox
before the page is displayed.
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
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.