Main page

Updating Many-to-Many Relations via Two Listboxes

Description

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.

Usage

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.

Steps to recreate

  1. Create a HTML table with two columns.
  2. In the left cell of the HTML table, use the Grid Builder to create the grid based on the "Employees" table and its two columns: emp_id and emp_name.
  3. Convert the emp_name field to a Link field that points to the current page and passes the value of emp_id as a URL parameter. (Use the Href Source property).
  4. 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 emp_id into the Remove Parameters property.
  5. In the right cell of the HTML table, use the Record Builder to create a record form with one emp_name field.
  6. Enter emp_id in the Remove Parameters property of the record form.
  7. Insert a new row into the record form and move it below the row with the emp_name control.
  8. Within the new row, add a HTML table with three columns. In the first and last column, add two ListBox controls (named AvailableListBox and LinkedListBox respectively) and in the middle column, add two buttons (named RightButton and LeftButton respectively)
  9. Under the Format tab of the Properties window, activate the multiple property of the two ListBox controls and set their Size property to 8.
  10. Switch to HTML mode and locate the code for both ListBox controls then delete the <option> tag for the default 'Select Value' option (i.e. <option value="" selected>Select Value</option>).
  11. For the AvailableListBox ListBox, set the Connection property to IntranetDB  and select projects in the Data Source property. Select project_id in the Bound Column property, project_name in the Text Column Property and Integer in the Data Type property. Also set the Source Type property to Code Expression.
    In the Format tab of the Properties window, set the Id property to available.
  12. For the LinkedListBox ListBox, open the 'Data Source' window by clicking on the [...] button next to the Data Source property, then click on the "Build Query" button and select the following tables: 'projects' and 'project_employees'. Select [*] (all fields) in the 'projects' table and unselect all fields in the 'projects_employees' table.
  13. While still in the 'Data Source' window, add a Where parameter of the form: emp_id equals(=) emp_id with the Parameter Source Type set to URL and the Default Value set to -1.
  14. Close the 'Data Source' window then with the LinkedListBox ListBox still selected, select project_id in the Bound Column property, project_name in the Text Column Property and Integer in the Data Type property. Also set the Source Type property to Code Expression.
  15. Within the record form, add a Hidden control called LinkedID.
  16. Add Custom code to the On Click client event of the RightButton and LeftButton buttons as shown in the example.
  17. Add Custom code to the On Submit client event of the record form as shown in the example.
  18. Add the appropriate programming code into the After Insert, After Update and Before Delete events of the record form and the Before Build Select event of the AvailableListBox ListBox 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.
  19. Make cosmetic changes as needed, by modifying the text and captions within the HTML.

Database Tables used

Database: Intranet

Tables: projects, employees, projects_employees

Programming Notes

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.