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:

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

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.