Blog: Posts from October, 2012

Currently, the new fields Total and Subtotal do not allow sorting or filtering. You can see this on the grid of Order Form – the Total column does not have a dropdown available, and the field is not displayed as an option in the advanced search bar.

Let’s enable sorting and filtering for these two fields. Switch back to the Project Explorer, and double-click on Orders / Fields / Subtotal* (Currency, read-only).

Change the following settings:

Press OK to save the field. Now, double-click on Total field node.

Make the same changes:

Save the field, and regenerate the application. On the Order Form page, you can see that the Subtotal and Total fields are now available as options on the advanced search bar.

Both columns also have sorting and filtering options available on the column header dropdown.

Let’s add a Total field to the Orders controller. This field will add Subtotal and Freight together to show the final cost of the whole order.

Adding the Field

In the Project Explorer, right-click on Orders / Fields node. Press New Field.

Give this new field the following settings:

(select sum(UnitPrice*Quantity*(1-Discount)) 
from "Order Details" 
where "Order Details".OrderID = Orders.OrderID) + Orders.Freight

Press OK to save this field.

Binding the Field to Views

Drop Orders / Fields / Total* (Currency, read-only) field node onto Orders / Views / grid1 node.

Drop Total field node onto Orders / Views / editForm1 view node.

SQL Business Rule

If you used an SQL Business Rule to handle Subtotal, double-click on Orders / Business Rules / Calculate (Sql / Execute) node.

Append the following highlighted line to the Script property:

select @Subtotal = sum(unitprice * quantity * (1 - discount)) 
from [Order Details] 
where OrderID = @OrderID

set @Total = @Subtotal + @Freight

Code Business Rule

If you are using C# or Visual Basic to handle the Subtotal field, right-click on Orders / Business Rules and press Edit Rule in Visual Studio.

Make the following changes to the code base:

C#:

using System;
using MyCompany.Data;

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

        [Rule("r101")]
        public void r101Implementation(
                    int? orderID,
                    string customerID,
                    string customerCompanyName,
                    int? employeeID,
                    string employeeLastName,
                    DateTime? orderDate,
                    DateTime? requiredDate,
                    DateTime? shippedDate,
                    int? shipVia,
                    string shipViaCompanyName,
                    decimal? freight,
                    string shipName,
                    string shipAddress,
                    string shipCity,
                    string shipRegion,
                    string shipPostalCode,
                    string shipCountry,
                    decimal? subtotal)
        {
            using (SqlText calc = new SqlText(@"select sum(unitprice * 
                    quantity * (1 - discount)) from 
                    [Order Details] where OrderID= @OrderID"))
            {
                calc.AddParameter("@OrderID", orderID);
                object total = calc.ExecuteScalar();
                if (DBNull.Value.Equals(total))
                {
                    UpdateFieldValue("Subtotal", 0);
                    UpdateFieldValue("Total", 0);
                }
                else
                {
                    UpdateFieldValue("Subtotal", Convert.ToDecimal(total));
                    UpdateFieldValue("Total", Convert.ToDecimal(total) + freight);
                }
            }
        }
    }
}

Visual Basic:

Imports MyCompany.Data
Imports System

Namespace MyCompany.Rules

    Partial Public Class OrdersBusinessRules
        Inherits MyCompany.Data.BusinessRules

        <Rule("r101")> _
        Public Sub r101Implementation( _
                    ByVal orderID As Nullable(Of Integer), _
                    ByVal customerID As String, _
                    ByVal customerCompanyName As String, _
                    ByVal employeeID As Nullable(Of Integer), _
                    ByVal employeeLastName As String, _
                    ByVal orderDate As Nullable(Of DateTime), _
                    ByVal requiredDate As Nullable(Of DateTime), _
                    ByVal shippedDate As Nullable(Of DateTime), _
                    ByVal shipVia As Nullable(Of Integer), _
                    ByVal shipViaCompanyName As String, _
                    ByVal freight As Nullable(Of Decimal), _
                    ByVal shipName As String, _
                    ByVal shipAddress As String, _
                    ByVal shipCity As String, _
                    ByVal shipRegion As String, _
                    ByVal shipPostalCode As String, _
                    ByVal shipCountry As String, _
                    ByVal subtotal As Nullable(Of Decimal))
            Using calc As SqlText = New SqlText(
                    "select sum(unitprice * quantity * (1 - discount)) " +
                    "from [Order Details] where OrderID=@OrderID")
                calc.AddParameter("@OrderID", orderID)
                Dim total As Object = calc.ExecuteScalar()
                If DBNull.Value.Equals(total) Then
                    UpdateFieldValue("Subtotal", 0)
                    UpdateFieldValue("Total", 0)
                Else
                    UpdateFieldValue("Subtotal", Convert.ToDecimal(total))
                    UpdateFieldValue("Total", Convert.ToDecimal(total) + freight)
                End If
            End Using
        End Sub
    End Class
End Namespace

JavaScript Business Rule

You can also implement a JavaScript business rule that will use the returned value of Subtotal and sum it with Freight to calculate the value of Total.

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

Assign the following values:

[Total] = [Subtotal] + [Freight];

Viewing the Results

On the toolbar, press Browse to regenerate the application. Navigate to the Order Form page, and select any order. You will see a Total field that sums up Subtotal and Freight.

When any of the order detail records are changed, the Subtotal and Total will be updated.