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:
|The value of this field is computed at run-time by SQL Expression||True|
select sum(UnitPrice*Quantity*(1-Discount)) from "Order Details" where "Order Details".OrderID = Orders.OrderID
|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.