Friday, April 27, 2012

Many-to-Many Relationship in the Northwind database

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.

Employees, Territories, and EmployeeTerritories tables and relationships

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.

Employee Territories data view showing the Territories for the selected Employee record

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.

New Field for Employees controller

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.

'New Data Field' for Territories field

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.

Territories field in the Employees data controller

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.

Comma separated list of territories for the selected Employee record

If you edit the employee, you will see a check box list of all options.

Check box list of all Territories in edit mode of Employees form

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 http://codeontime.com