Designer

When you create a new order detail, the Extended Price field will not be calculated and will only display N/A until you save the record.

Let’s make the field automatically update and provide instantaneous feedback to the user.

Switch back to the Project Designer, and double-click on OrderDetails / Fields / ExtendedPrice node.

Change the following settings:

Press OK to save the field.

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

The field can be updated using an SQL, Code or JavaScript business rule. Pick one of the following three implementations.

SQL Business Rule

Assign the following values:

set @ExtendedPrice = @UnitPrice * @Quantity * (1 - @Discount)

Press OK to save.

Code Business Rule

Assign the following values:

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

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

The file will be opened in Visual Studio. Replace the body of the rule with the following implementation:

C#:

using System;
using MyCompany.Data;

namespace MyCompany.Rules
{
    public partial class OrderDetailsBusinessRules : MyCompany.Data.BusinessRules
    {
        
        [Rule("r101")]
        public void r101Implementation(int? orderID, 
            string orderCustomerID, 
            string orderCustomerCompanyName, 
            string orderEmployeeLastName, 
            string orderShipViaCompanyName, 
            int? productID, 
            string productProductName, 
            string productCategoryCategoryName, 
            string productSupplierCompanyName, 
            decimal? unitPrice, 
            short? quantity, 
            float? discount, 
            decimal? extendedPrice)
        {
            UpdateFieldValue("ExtendedPrice", 
                Convert.ToDecimal(unitPrice) * Convert.ToDecimal(quantity) * 
                (1 - Convert.ToDecimal(discount)));
        }
    }
}

Visual Basic:

Imports MyCompany.Data
Imports System

Namespace MyCompany.Rules

    Partial Public Class OrderDetailsBusinessRules
        Inherits MyCompany.Data.BusinessRules

        <Rule("r101")> _
        Public Sub r101Implementation(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),
                                      ByVal extendedPrice As Nullable(Of Decimal))
            UpdateFieldValue("ExtendedPrice",
                Convert.ToDecimal(unitPrice) * Convert.ToDecimal(quantity) *
                (1 - Convert.ToDecimal(discount)))
        End Sub
    End Class
End Namespace

Save the file.

JavaScript Business Rule

Assign the following values:

[ExtendedPrice] = [UnitPrice] * [Quantity] * (1 - [Discount]);
this.preventDefault();

Press OK to save the business rule.

Viewing the Results

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 Extended Price will be calculated using the default values.

If you change any of the fields, the Extended Price will be recalculated when the user changes focus from the changed field.

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.