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 | Text |
| 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.
On the field’s page, switch to the Data Fields tab. On the action bar, press New | New Data Field. Enter the following settings:
| Property |
Text |
| View |
editForm1 |
| Category |
Orders |
Press OK to save the data field. Double-click on the field in the Project Explorer to go back to the list of data fields. Create another data field with the following settings:
| Property |
Text |
| View |
createForm1 |
| Category |
New Orders |
Regenerate the application, and select any order on the Order Form page. You will see the Subtotal field showing the sum of extended prices.
