Friday, April 27, 2012
Using Check Box List for New Many-to-Many Relationship

The “Check Box List” lookup item style can be used to enter multi-value or many-to-many fields.

In the Multiple Value Field tutorial, we created a Shipping Methods field in the Shippers table that stored a comma separated list of values. This may be sufficient for some people, but it would be standard operating procedure to normalize this list into a separate table in order to store the values externally.

Let’s create a separate table by the name of ShipMethods, and a table to store the many-to-many relationship called ShippersShipMethods.

In the Object Explorer, right-click on Databases / Northwind / Database Diagrams and select New Database Diagram. Select Yes to install diagram support in the database.

When the Add Table screen opens, highlight Shippers and press Add.

Add 'Shippers' table to diagram

Press Close to close the screen. Click on the Shippers table. Mouse over the Table View property, and change it to Standard.

Right-click on white space in the diagram and press New Table. Give the table the name of “ShipMethods”. Press OK to create a table with this name.

Enter 'ShipMethods' table name

Give this table the following fields:

Primary Key? Column Name Data Type Allow Nulls
Yes ShipMethodID int No
  ShipMethodName nvarchar(50) No

Highlight the ShipMethodID column. In the Properties window, change (Is Identity) to “Yes”.

Create another table with the name of “ShippersShipMethods”.

Enter 'ShippersShipMethods' table name

Give this table the following fields:

Primary Key? Column Name Data Type Allow Nulls
Yes ShipperID int No
Yes ShipMethodID int No

Drag the ShipperID column from ShippersShipMethods to Shippers table. Keep the default Relationship name and press OK twice to save the relationship.

Drag the ShipMethodID column from ShippersShipMethods to ShipMethods table. Press OK twice to save.

Save this diagram to apply the changes. Your diagram should look like the one below.

Shippers, ShipMethods, and ShippersShipMethods table relationship

Start Code On Time web application generator, click on the project name, and select the Refresh option. Toggle the checkboxes next to the tables dbo.ShipMethods, dbo.ShippersShipMethods, and the controller Shippers. Press Refresh, and press Yes to confirm the operation.

Refresh the ShipMethods, Shippers, ShippersShipMethods tables

When the Refresh is complete, select the Design option on the Summary page.

In the Project Explorer, switch to the Controllers tab. Right-click on Shippers / Fields node and select New Field.

New Field option for Shippers controller

Give this field the following settings:

Property Value
Name ShipMethods
Allow Null Values True
The value of this field is computed at run-time by SQL Expression True
SQL Formula
NULL
Label Ship Methods
Items Style Check Box List
Items Data Controller ShipMethods
Data Value Field ShipMethodID
Data Text Field ShipMethodName
Target Controller ShippersShipMethods

Press OK to save the field. In the Project Explorer, right-click on Shippers / Views / editForm1 / c1 – Shippers category node and select New Data Field.

New Data Field in 'editForm1'

Give this data field the following settings:

Property Value
Field Name ShipMethods
Columns 3

Press OK to save the data field. Right-click on Shippers / Views / createForm1 / c1 – New Shippers category node and choose New Data Field.

New Data Field in 'createForm1'

Give this data field the following settings:

Property Value
Field Name ShipMethods
Columns 3

Press OK to save the data field. Let’s display a read-only list of fields in the grid view as well. Right-click on Shippers / Views / grid1 view node and select New Data Field.

New Data Field for 'grid1'

Give this data field the following settings:

Property Value
Field Name ShipMethods
Columns 3
Values of this field cannot be edited Yes

Press OK to save the data field. On the tool bar, select Browse to generate the web application.

When it opens in your default web browser, navigate to the Ship Methods page. Create three new shipping methods:

Ship Method Name
Land
Air
Sea

Three records created in Ship Methods table

Next, navigate to Shippers page. You will see all shippers have “N/A” Ship Methods. Edit a record, and a check box list will appear with the available options.

Three Ship Methods options presented as check box list

Select a few ship methods for each shipper. When not in edit mode, the check box list will be replaced with a comma separated list of values. The data field on the grid view will stay in read only mode when a record is being edited.

Ship Methods shown as comma separated list in grid view