Sample Applications / Order Form / Total and Subtotal

Creating Order Subtotal Field

Let’s add a Subtotal field to the order. This field will sum up the extended prices of all order details.

In the Project Explorer, switch to Controllers tab. Right-click on Orders / Fields, and press New Field.

The new field’s settings will be:

Property	Value
Field Name	Subtotal
Type	Currency
The value of this field is computed at run-time by SQL Expression	True
SQL	select sum(UnitPriceQuantity(1-Discount)) from "Order Details" where "Order Details".OrderID = Orders.OrderID
Label	Subtotal
Values of this field cannot be edited	True
Data Format String	c

Press OK to save the field.

Your application will incorporate this SQL Formula in the SELECT statements composed at runtime. This statement will look similar to:

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

This is the output produced when the statement is executed in SQL Management Studio. The actual statement will look more complex, and may include user-defined sorting, filtering, and paging parameters.

We’ll need to bind this field to views, so that the end user can see the field value.

Drop Orders / Fields / Subtotal field node onto Orders / Views / grid1 node.

Drop Subtotal field node onto Orders / Views / editForm1 node.

Drop Subtotal field node onto Orders / Views / createForm1 view node.

Regenerate the application, and select any order on the Order Form page. You will see the Subtotal field showing the sum of extended prices.