Database Lookups

The picture below shows the schema for a sample web application. The Cars database stores several makes of cars, which each have several models. Each model has various styles.

When a user creates a car, they must select a make, a model, and a style in that order.  The default web application generated from this database will allow the user to select any make, model, or style at any time. The preferred behavior would be to cascade each lookup selection as a filter to the next lookup. When a user changes a master lookup, all child lookups should be cleared.

The script to create the database is attached at the end of this article.

Implementing a Cascading Lookup

Start the Project Designer. In the Project Explorer, switch to the Controllers tab. Double-click on Cars / Fields / MakeID* (Guid) –> Make field node.

Change the following properties. The Copy property will clear the dependent lookups when the value of this field changes.

Press OK to save. Double-click on Cars / Fields / ModelID*(Guid) –> Model field node.

Change the following properties. The Context Fields property will pass a filter to the lookup.

Press OK to save. Double-click on Cars / Fields / StyleID* (Guid) –> Style field node.

Change the following:

Save the field. On the toolbar, press Browse.

Navigate to the Cars page and create a new record. Activate the lookup for Make Name. Select one of the choices.

Activate the lookup for Model Name. The list of models has been filtered by the previous lookup selection.

The same filtering will occur on the next lookup.

Change the make or model. The lookups under the changed value will be cleared.

Script for Cars Database

Paste the script below in SQL Server Management Studio and press Execute to create the database.

CREATE DATABASE [CarsExample]
GO
USE [CarsExample]
GO

CREATE TABLE [dbo].[Cars](
    [CarID] [uniqueidentifier] NOT NULL PRIMARY KEY DEFAULT (newid()),
    [MakeID] [uniqueidentifier] NOT NULL,
    [ModelID] [uniqueidentifier] NOT NULL,
    [StyleID] [uniqueidentifier] NOT NULL)
GO
CREATE TABLE [dbo].[Make](
    [MakeID] [uniqueidentifier] NOT NULL PRIMARY KEY DEFAULT (newid()),
    [MakeName] [nvarchar](50) NOT NULL)
GO
CREATE TABLE [dbo].[Model](
    [ModelID] [uniqueidentifier] NOT NULL PRIMARY KEY DEFAULT (newid()),
    [ModelName] [nvarchar](50) NOT NULL,
    [MakeID] [uniqueidentifier] NOT NULL)
GO
CREATE TABLE [dbo].[Style](
    [StyleID] [uniqueidentifier] NOT NULL PRIMARY KEY DEFAULT (newid()),
    [StyleName] [nvarchar](50) NOT NULL,
    [ModelID] [uniqueidentifier] NOT NULL)
GO

ALTER TABLE [dbo].[Cars]  WITH CHECK ADD  CONSTRAINT [FK_Cars_Make] FOREIGN KEY([MakeID])
REFERENCES [dbo].[Make] ([MakeID])
GO
ALTER TABLE [dbo].[Cars]  WITH CHECK ADD  CONSTRAINT [FK_Cars_Model] FOREIGN KEY([ModelID])
REFERENCES [dbo].[Model] ([ModelID])
GO
ALTER TABLE [dbo].[Cars]  WITH CHECK ADD  CONSTRAINT [FK_Cars_Style] FOREIGN KEY([StyleID])
REFERENCES [dbo].[Style] ([StyleID])
GO
ALTER TABLE [dbo].[Model]  WITH CHECK ADD  CONSTRAINT [FK_Model_Make] FOREIGN KEY([MakeID])
REFERENCES [dbo].[Make] ([MakeID])
GO
ALTER TABLE [dbo].[Style]  WITH CHECK ADD  CONSTRAINT [FK_Style_Model] FOREIGN KEY([ModelID])
REFERENCES [dbo].[Model] ([ModelID])
GO

Let’s make similar changes to the Order Details controller. We’ll make the ProductID lookup open in search mode upon creating a new order detail. When selecting a product, the Unit Price will also copy over into the order detail.

On the Controllers tab of the Project Explorer, double-click on OrderDetails / Fields / ProductID (Int32) –> Products field node.

Change the following settings:

Press OK to save the field, and press Browse in the top left corner.

Navigate to the Order Form page, and select any order. Create a new order detail record. A lookup will open prompting you to select a product.

Select a product, and you will see that Unit Price of the product has been copied into the order detail.