Friday, September 7, 2012

Creating Many-to-Many Fields

The Northwind database features a many-to-many relationship between Territories and Employees.

Many-to-many relationship between Employees and Territories.

The app generator will create a page for EmployeeTerritories that allows editing records directly.

Employee Territories page allows setting up relationships between employees and territories directly.

EmployeeTerritories is also available as a child data view on the Employees page. Selecting an employee will reveal a list of related territories.

Employee Territories child data view allows editing territories associated with the selected employee.

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.

New Field context menu option for Employees controller.

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.

Dragging Territories field node onto 'editForm1' view node.

Double-click on Employees / Views / editForm1 / Territories data field node.

Territories data field node in editForm1 view of Employees controller.

Change the Columns property:

Property Value
Columns 5

Press OK to save. Switch to the Pages tab. Right-click on Employees page node, and press View in Browser.

View in Browser context menu option for Employees page.

View the details for an employee. Territories field will display a comma-separated list of values associated with the employee.

Territories many-to-many field will render a comma separated list of values.

Press Edit button. All territories will be rendered as a check box list.

When the form is in edit mode, all territories are rendered in 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.

Delete context menu option for 'view4' on Employees page.

Drag view3 node on the left side of view2 to place it in the first position.

Dragging 'view3' view node on the left side of 'view2' view node.     View 'view3' has been placed before 'view2'.

Right-click on Employees page node, and press View in Browser.

View in Browser context menu option for Employees page node in the Project Explorer.

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.

Employees page no longer has the Employee Territories data view.

No comments:

You can find more about Code OnTime Generator, Data Aquarium Framework, and other great products here.

© 2010 Code OnTime LLC. Intelligent code generation software for ASP.NET. Visit us at