If you need to store multiple values in the same field, then set its items style to Check Box List. The values will be stored as a comma-separated list, but rendered as a check box list of all available options when in edit mode.
Let’s introduce such a field in the Northwind database.
Run Microsoft SQL Server Management Studio and right-click on Databases / Northwind / Tables / dbo.Shippers. Select Design option.
Add a new field with the following properties.
|Column Name||Data Type||Allow Nulls|
Save the table, and refresh settings of the Northwind web application. Make sure to select the Shippers in the list of data controllers in the Refresh dialog.
Click Design option to activate the Project Designer.
In the Project Explorer, switch to the Controllers tab. Select the Shippers / Fields / ShipMethods field node.
Change the following setting:
|Items Style||Check Box List|
Press OK to save the field.
In the Project Explorer, right-click on the ShipMethods field, and select Show Value/Text Items.
On the action bar, press New | New Item. Create three new items with the following properties:
On the tool bar, press Browse to generate the application. The application will open in your default web browser. Navigate to the Shippers page.
The ship methods for each shipper will currently say “N/A”. If you edit a shipper, you will see the lookup items presented in a check box list. When you save, your choices will be saved in a comma separated list.
You may also consider creating a many-to-many relationship field for Ship Methods.