Let’s add an Extended Price field to show the final price of each line item in the order. This field will multiply price by quantity, and factor in the discount.
Switch to the Project Explorer, and activate the Controllers tab. Expand OrderDetails, and right-click on Fields. Select New Field option.
For the new field, enter the following settings:
| Property | Value |
| Name | ExtendedPrice |
| Type | Currency |
| The value of this field is computed at run-time by SQL Expression | True |
| SQL Formula | OrderDetails.UnitPrice * OrderDetails.Quantity * (1 - OrderDetails.Discount)
|
| Label |
Extended Price |
| Values of this field cannot be edited |
True |
| Data Format String |
c |
Press OK to save the field. The OrderDetails alias used in the SQL Expression field above is referring to “command1” of the Order Details controller. The text of this command can be found at Order Details / Commands / command1 node.
Your application will incorporate the SQL Formula in the SELECT statements composed at runtime. The statement will be equivalent to the following simplified example.
select *,
OrderDetails.UnitPrice * OrderDetails.Quantity *
(1 - OrderDetails.Discount) ExtendedPrice
from "Order Details" OrderDetails
This is the output produced in SQL Management Studio if you try executing the simplified statement.
To make sure that the field will be displayed in the application pages, we’ll bind the field to the data view using data fields.
In the Project Explorer, drop OrderDetails / Fields / ExtendedPrice* (Currency, read-only) field node on OrderDetails / Views / grid1 view node.
Again, drop the field node onto OrderDetails / Views / editForm1 / c1 – Order Details category node.
Finally, drop the field node onto OrderDetails / Views / createForm view node.
On the toolbar, press Browse, and navigate to the Order Form page. Select an order from the list – the list of order details will have an Extended Price column.
