Tutorials

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 Designer, and double-click on OrderDetails / Fields / ExtendedPrice node.

Change the following settings:

Convert.ToDecimal(unitPrice) * Convert.ToDecimal(quantity) * (1 - Convert.ToDecimal(discount))

Press OK to save the field. Generate the application, and wait for the Preview to load. Select an order, and create a new order detail. Select a product, and you will see that the Extended Price field has been calculated. Change any field and shift focus, and the Extended Price field will be recalculated every time.

To handle this calculation, the web application generator created a business rules class. It can be found in the project’s App_Code/Rules folder.

C#:

namespace MyCompany.Rules
{
    public partial class OrderDetailsBusinessRules : MyCompany.Data.BusinessRules
    {
        
        [ControllerAction("OrderDetails", "Calculate", "ExtendedPrice")]
        public void CalculateOrderDetails(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("ExtendedPrice", Convert.ToDecimal(unitPrice) * 
                Convert.ToDecimal(quantity) * (1 - Convert.ToDecimal(discount)));
        }
        
        [RowBuilder("OrderDetails", RowKind.New)]
        public void BuildNewOrderDetails()
        {
            UpdateFieldValue("Quantity", 1);
            UpdateFieldValue("Discount", 0);
        }
    }
}

VB:

Namespace MyCompany.Rules
    
    Partial Public Class OrderDetailsBusinessRules
        Inherits MyCompany.Data.BusinessRules
        
        <ControllerAction("OrderDetails", "Calculate", "ExtendedPrice")> _
        Public Sub CalculateOrderDetails(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("ExtendedPrice", Convert.ToDecimal(unitPrice) *
                             Convert.ToDecimal(quantity) *
                             (1 - Convert.ToDecimal(discount)))
        End Sub
        
        <RowBuilder("OrderDetails", RowKind.New)>  _
        Public Sub BuildNewOrderDetails()
            UpdateFieldValue("Quantity", 1)
            UpdateFieldValue("Discount", 0)
        End Sub
    End Class
End Namespace

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 select 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. Go back to the field. Select the Data Fields tab. On the action bar, press New | New Tab.

Set the following settings for the data field:

Press OK to save the data field. Use the same method to create two more data fields:

Regenerate the application, and navigate to Order Form page. Select an order. For each order detail, you will see a calculated Extended Price field.