Code On Time: Tutorials

Saturday, March 17, 2012Print Subscribe

Total and Subtotal: Creating Order Total Field

Let’s add a Total field to the Orders controller. This field will add Subtotal and Freight together to show the final cost of the whole order.

In the Project Explorer, select the Orders controller, and right-click on Fields. Press New Field.

Give this new field the following settings:

Property	Value
Name	Total
Type	Currency
The value of this field is computed at run-time by SQL Expression	(select sum(unitpricequantity(1-discount)) from "order details" where "Order Details".OrderID = Orders.OrderID) + Orders.Freight
The value of this field is calculated by a business rule expression	CalculateOrderDetailsTotal(orderID) + freight
Label	Total
Data Format String	c
Values of this field cannot be edited	True
Context Fields	OrderDetails, Freight

Press OK to save this field.

We’ll need to bind this field to the data views. In the Project Designer, switch to Data Fields tab. On the action bar, press New | New Data Field. Use the following settings:

Properties	Value
View	editForm1
Category	Orders

Press OK to save the data field. Double-click on Total field again, and create another data field with the following settings:

Properties	Value
View	grid1
Category	N/A

Save the data field, and regenerate the application. Navigate to the Order Form page, and select any order. You will see a Total field that sums up Subtotal and Freight.

Labels: Tutorials, Web Application Generator

Friday, March 16, 2012Print Subscribe

Total and Subtotal: Creating Subtotal Field

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(unitpricequantity(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.

Labels: Tutorials, Web Application Generator

Tuesday, March 13, 2012Print Subscribe

Order Details: Reshaping Grid View

The Order Details grid view displays several unnecessary reference fields, such as Order Customer Company Name, Order Employee Last Name, and Order Ship Via Company Name. We will get rid of these redundant fields. Let’s also display the average Unit Price, total Quantity, average Discount, and total sum of Extended Price.

In the Designer, expand to Order Form / c100 / dv101 / grid1 node. Right-click on OrderCustomerCompanyName data field and press Delete. Also, delete the data fields called OrderEmployeeLastName and OrderShipViaCompanyName.

Now that the redundant data fields have been removed, let’s assign aggregates. Double-click on UnitPrice. Change the Aggregate Function property field to “Average”.

Press OK to save the data field.

Next, double-click on Quantity and change Aggregate Function to “Sum”.

Save the data field, and double-click on Discount. Change Aggregate Function to “Average”.

Press OK, and double-click on ExtendedPrice. Change Aggregate Function to “Sum”.

Press OK to save Extended Price data field. Generate the application, and navigate to Order Form page. Select any order. You will see that the list of order details will not display redundant order information, and displays aggregates at the bottom of the grid.

Labels: Tutorials, Web Application Generator