Blog: Posts from October, 2012

Let’s add an Extended Price field to show the final price of each line item in the order. This field will multiply price by quantity, and factor in the discount.

Switch to the Project Explorer, and activate the Controllers tab. Expand OrderDetails, and right-click on Fields. Select New Field option.

For the new field, enter the following settings:

OrderDetails.UnitPrice * OrderDetails.Quantity * (1 - OrderDetails.Discount)

Press OK to save the field. The OrderDetails alias used in the SQL Expression field above is referring to “command1” of the Order Details controller. The text of this command can be found at Order Details / Commands / command1 node.

Your application will incorporate the SQL Formula in the SELECT statements composed at runtime. The statement will be equivalent to the following simplified example.

select *, 
    OrderDetails.UnitPrice * OrderDetails.Quantity * 
    (1 - OrderDetails.Discount) ExtendedPrice
from "Order Details" OrderDetails

This is the output produced in SQL Management Studio if you try executing the simplified statement.

To make sure that the field will be displayed in the application pages, we’ll bind the field to the data view using data fields.

In the Project Explorer, drop OrderDetails / Fields / ExtendedPrice* (Currency, read-only) field node on OrderDetails / Views / grid1 view node.

Again, drop the field node onto OrderDetails / Views / editForm1 / c1 – Order Details category node.

Finally, drop the field node onto OrderDetails / Views / createForm view node.

On the toolbar, press Browse, and navigate to the Order Form page. Select an order from the list – the list of order details will have an Extended Price column.

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%).