If you have an existing many-to-many relationship in your database, you can turn it into a many-to-many field. In the Northwind database, there is a many-to-many relationship between tables EmployeeTerritories, Employees, and Territories.
An employee can be assigned to several territories, but these territories are not exclusive to an employee. Each employee can be linked to multiple territories, and each territory can be linked to multiple employees.
On the Employees page of a Northwind web application, a separate child data view underneath the master record shows a list of territories associated with the selected employee.
Let’s display these territories as a comma separated list on the master employee record, and allow end users to select territories for each employee using a check box list style of presentation.
Start Code On Time web application generator and activate the Project Designer. In the Explorer, switch to the Controllers tab. Right-click on Employees / Fields node and select New Field.
Give this field the following settings:
| Property | Value |
| Name | Territories |
| Type | String |
| Allow null values | True |
| The value of this field is computed at run-time by SQL expression | True |
| SQL Formula | NULL
|
| Label |
Territories |
| Items Style |
Check Box List |
| Items Data Controller |
Territories |
| Data Value Field |
TerritoryId |
| Data Text Field |
Territory Description |
| Target Controller |
EmployeeTerritories |
Press OK to save the field. Next, we’ll need to bind the field to each view.
At the top of the page, switch to the Data Fields tab.
On the action bar, press New | New Data Field. Give this data field the following settings:
| Property |
Value |
| View |
editForm1 |
| Category |
Employees |
| Columns |
5 |
Press OK to save the data field. In the Explorer, double-click on Employees / Fields / Territories field node.
Create another data field with the following settings:
| Property |
Value |
| View |
createForm1 |
| Category |
Employees |
| Columns |
5 |
On the tool bar, press Browse to generate the application. It will open in your default web browser. Navigate to the Employees page, and select an employee. You will see a comma separated list of territories that belong to the employee.
If you edit the employee, you will see a check box list of all options.
