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.
![New Field context menu option for Orders data controller. New Field context menu option for Orders data controller.](/blog/2012/10/order-form-samplepart-23/image02.png)
Give this new field the following settings:
Property | Value |
Name | Total |
Type | Currency |
The value of this field is computed at run-time by SQL Expression | true |
SQL Formula | (select sum(UnitPrice*Quantity*(1-Discount))
from "Order Details"
where "Order Details".OrderID = Orders.OrderID) + Orders.Freight
|
The value of this field is calculated by a business rule expression |
true |
Label |
Total |
Values of this field cannot be edited |
True |
Data Format String |
c |
Context Fields |
OrderDetails, 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.
![Total data field created in view 'grid1'. Total data field created in view 'grid1'.](/blog/2012/10/order-form-samplepart-23/image06.png)
Drop Total field node onto Orders / Views / editForm1 view node.
![Total data field created in view 'editForm1'. Total data field created in view 'editForm1'.](/blog/2012/10/order-form-samplepart-23/image10.png)
SQL Business Rule
If you used an SQL Business Rule to handle Subtotal, double-click on Orders / Business Rules / Calculate (Sql / Execute) node.
![Calculate SQL Business Rule in Orders controller. Calculate SQL Business Rule in Orders controller.](/blog/2012/10/order-form-samplepart-23/image12.png)
Append the following highlighted line to the Script property:
Property |
New Value |
Script |
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.
![Edit Rule in Visual Studio context menu option for a code business rule. Edit Rule in Visual Studio context menu option for a code business rule.](/blog/2012/10/order-form-samplepart-23/image14.png)
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.
![New Business Rule context menu option for Orders controller. New Business Rule context menu option for Orders controller.](/blog/2012/10/order-form-samplepart-23/image16.png)
Assign the following values:
Property |
Value |
Type |
JavaScript |
Command Name |
Calculate |
Phase |
After |
Script |
[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.
![Total field present in the Orders edit form. Total field present in the Orders edit form.](/blog/2012/10/order-form-samplepart-23/image18.png)
When any of the order detail records are changed, the Subtotal and Total will be updated.
![Total field updated when order details change. Total field updated when order details change.](/blog/2012/10/order-form-samplepart-23/image20.png)