Designer

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 assign default values to fields Quantity and Discount of a new order detail record. These fields already have server defaults that are added when the order detail is saved to the database. It would be better to have these defaults displayed to the user before insertion, and have Discount formatted as percentage.

There are multiple ways of assigning a default value to the field. The following sections will describe each method.

SQL Business Rule

Switch to the Project Designer, and right-click on OrderDetails / Business Rules node. Select New Business Rule option.

Assign the following values:

set @Quantity = 1
set @Discount = 0

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

Navigate to the Order Form page, and select an order. Create a new order detail, and select a product. The Quantity and Discount fields will be populated with default values.

Code Business Rule

The Quantity and Discount fields can also be assigned defaults using C#or Visual Basic.

If you have created the SQL business rule in the previous section, delete it now.

Right-click on OrderDetails / Business Rules node, and press New Business Rule.

Assign the following values:

Press OK to save the business rule. On the toolbar, press Browse to regenerate the web application and create the business rule file.

When complete, right-click on OrderDetails / Business Rules / New (Code / Execute) – r100 node, and press Edit Rule in Visual Studio.

The file will open in Visual Studio. Replace the body of the predefined business rule method with the following:

C#:

using System;
using MyCompany.Data;

namespace MyCompany.Rules
{
    public partial class OrderDetailsBusinessRules : MyCompany.Data.BusinessRules
    {

        [Rule("r100")]
        public void r100Implementation(int? orderID, 
            string orderCustomerID, 
            string orderCustomerCompanyName, 
            string orderEmployeeLastName, 
            string orderShipViaCompanyName, 
            int? productID, 
            string productProductName, 
            string productCategoryCategoryName, 
            string productSupplierCompanyName, 
            decimal? unitPrice, 
            short? quantity, 
            float? discount)
        {
            UpdateFieldValue("Quantity", 1);
            UpdateFieldValue("Discount", 0);
        }
    }
}

Visual Basic:

Imports MyCompany.Data
Imports System

Namespace MyCompany.Rules
    
    Partial Public Class OrderDetailsBusinessRules
        Inherits MyCompany.Data.BusinessRules
        
        <Rule("r100")> _
        Public Sub r100Implementation(ByVal orderID As Nullable(Of Integer),
                                      ByVal orderCustomerID As String,
                                      ByVal orderCustomerCompanyName As String,
                                      ByVal orderEmployeeLastName As String,
                                      ByVal orderShipViaCompanyName As String,
                                      ByVal productID As Nullable(Of Integer),
                                      ByVal productProductName As String,
                                      ByVal productCategoryCategoryName As String,
                                      ByVal productSupplierCompanyName As String,
                                      ByVal unitPrice As Nullable(Of Decimal),
                                      ByVal quantity As Nullable(Of Short),
                                      ByVal discount As Nullable(Of Single))
            UpdateFieldValue("Quantity", 1)
            UpdateFieldValue("Discount", 0)
        End Sub
    End Class
End Namespace

Save the file. The default values will function as described earlier when an order detail record is created.

JavaScript Business Rule

The third method of implementation is by using JavaScript.

If you have created the SQL or Code business rules described above, delete them now.

Right-click on OrderDetails / Business Rules node, and press New Business Rule.

Assign the following values:

[Quantity] = 0;
[Discount] = 1;

Press OK to save. If you browse the application and create a new order detail record, the Quantity and Discount fields will be assigned default values as described earlier.

Formatting Discount as Percentage

In the Project Explorer, double-click on OrderDetails / Fields / Discount node.

Change the Data Format String property:

Press OK to save the field, and regenerate the project. Navigate to the Order Form page. Select an order, and create a new order detail. Select a product for the order detail. Discount field is now formatted as a percentage (0.00%).