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.
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.
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”.
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.
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.
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.
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.
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.
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.
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 |
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.
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.
