Data Sources

Picklists are commonly used in web applications in order to allow a user to pick one item from a list of available options. These lists prevent duplication of similar values, such as alternate spellings. Picklists are physically different from database lookup fields since there is no foreign key relationship between the tables. The application generator automatically configures lookup fields. Developers can configure a picklist on any field in a web application.

For example, let’s look at the City and Country fields in the Customers edit form. Both fields are simple text boxes – the user can type any value.

Let’s convert both fields to picklists.

Creating Cities and Countries Tables

Start SQL Server Management Studio. In the Object Explorer, right-click on Databases / Northwind / Tables node, and select New Table option.

Give this table the following columns:

Right-click on the CountryID column, and select Make Primary ID.

Under Column Properties, change the following setting:

Press Ctrl+S to save the table. Give the table the name of “Countries”.

Create another table with the following columns:

Right-click on the CityID column, and select Make Primary ID.

Under Column Properties, change the following setting:

Save the table, and give it the name of “Cities”.

Start Code On Time web application generator. Refresh, and add the two new tables to the web application.

Finish generating the application.

Populate the Tables

Next, populate the Cities and Countries table using the values currently existing in the Customers table. A full list of values can be found by activating the dropdown on the column header and clicking the “Filter…” option.

Insert all City values in the Cities table, and all Country values in the Countries table.

Converting Country and City fields into a Picklist

Start the Project Designer. In the Project Explorer, switch to the Controllers tab. Double-click on Customers / Fields / City field node.

Change the following properties:

Press OK to save the field. Double-click on Customers / Fields / Country field node.

Make the following changes:

Press OK to save the field. On the toolbar, press Browse.

Navigate to the Customers page, and edit a record. The City and Country fields will be rendered as lookup links.

Click on the link for City field, and a lookup modal window will display a list of cities.

Select a lookup item from the list. Save the record, and select the record again. Instead of CityName being inserted into the field, the CityID was inserted.

The same behavior occurs for Country lookup field.

This is happening because the primary key of the lookup is automatically used as the value of the selected record.

You can address this in two different ways.

Changing the Primary Key of the Lookup Controller

Switch back to the Project Designer. In the Project Explorer, right-click on Countries / Fields node, and select List option.

Change the field properties:

You can repeat this procedure for Cities controller, or configure the lookup properties of the City field as explained next.

Specifying the Data Value Field and Data Text Field

In the Project Explorer, double-click on Customers / Fields / City field node.

Make the following changes:

Press OK to save the field.

The client library always uses Data Value Field and Data Text Field to determine which lookup field represents the key of the selected item, and which field represents the text. When the Data Value Field is not specified, then the value of the primary key field of the selected lookup item will become a lookup value. When the Data Text Field is not specified, the text will be derived from the first visible column.

Trying it Out

On the toolbar, press Browse. Navigate to Customers page and edit a record. Change the City and Country fields, save, and select the record again. The names of City and Country will be the values inserted into the respective fields.

This can be confirmed by checking the record in SQL Server Management Studio.

Custom result sets can be produced by stored procedures and web services. A quick look at the sample output cache table and output caching script will give you a few ideas about introducing optimization in the caching.

The datable table CustOrderHist_Cache stores cached output created by concurrent users in the process of interacting with the application. The highlighted columns are the actual output columns produced by the stored procedure.

The output caching script filters out the data by CacheUserName column to segregate the user-specific result sets. The performance will improve if the column indexed.

The same consideration applies to the column CachExpires.

A single index that includes both CacheUserName and CacheExpires will likely work well in most situations.

The primary key column CacheID is provided to allow selecting data rows in the application user interface. Our sample result set could be changed to use the ProductName  and CustomerID as a compound primary key. It is possible since the stored procedure CustOrderHist produces a list of unique products purchased by a customer. In that case you can delete the CacheID column and simplify the output caching script.

Another area of optimization if the length of time the custom result set remains cached. Most applications will tolerate the data to be “stale” for at least a few minutes if not hours.

The output cache table may end up containing orphan cached data if users are not working with the app on a daily basis. It is a good idea to enhance the caching script with a simple delete statement that wipes out any cached data rows with the cache expiration overdue by a few days.