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(UnitPrice*Quantity*(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.
