The Northwind database features a many-to-many relationship between Territories and Employees.
The app generator will create a page for EmployeeTerritories that allows editing records directly.
EmployeeTerritories is also available as a child data view on the Employees page. Selecting an employee will reveal a list of related territories.
Let’s create a many-to-many field on the employee edit form that will allow selecting territories in a check box list. Each option in the list will represent a territory from the Territories table. The checkbox next to the territory name will be checked if there is a record in the EmployeeTerritories table linking the selected employee with the territory. The relevant EmployeeTerritories records will be inserted or deleted when the user changes the selection of checkboxes and saves the employee record.
Start the Project Designer. In the Project Explorer, switch to the Controllers tab. Right-click on Employees / Fields node, and press New Field.
Give this field the following values:
| Property | Value |
| Name | Territories |
| Allow null values | true |
| The value of this field is computed at run-time by SQL expression. | true |
| Label | Territories |
| Items Style | Check Box List |
| Items Data Controller | Territories |
| Data Value Field | TerritoryId |
| Data Text Field | TerritoryDescription |
| Target Controller | EmployeeTerritories |
Press OK to save the field.
Drag Employees / Fields / Territories field node onto Employees / Views / editForm1 view node.
Double-click on Employees / Views / editForm1 / Territories data field node.
Change the Columns property:
Press OK to save. Switch to the Pages tab. Right-click on Employees page node, and press View in Browser.
View the details for an employee. Territories field will display a comma-separated list of values associated with the employee.
Press Edit button. All territories will be rendered as a check box list.
Check or uncheck several values, and press OK to save the record. Select the record to verify that the changes were persisted.
Changing the Child Views
The Employee Territories child data view is now unnecessary.
Switch back to the Project Designer. In the Project Explorer, right-click on Employees / container2 / view4 node, and press Delete.
Drag view3 node on the left side of view2 to place it in the first position.
Right-click on Employees page node, and press View in Browser.
Select an employee from the list, and the Orders and Employees child data view tabs will appear below. The Employee Territories child data view tab is no longer present. The picture also shows the many-to-many field Territories in the master grid view.
