Order Form Sample–Part 21

Labels
AJAX(112) App Studio(7) Apple(1) Application Builder(245) Application Factory(207) ASP.NET(95) ASP.NET 3.5(45) ASP.NET Code Generator(72) ASP.NET Membership(28) Azure(18) Barcode(2) Barcodes(3) BLOB(18) Business Rules(1) Business Rules/Logic(140) BYOD(13) Caching(2) Calendar(5) Charts(29) Cloud(14) Cloud On Time(2) Cloud On Time for Windows 7(2) Code Generator(54) Collaboration(11) command line(1) Conflict Detection(1) Content Management System(12) COT Tools for Excel(26) CRUD(1) Custom Actions(1) Data Aquarium Framework(122) Data Sheet(9) Data Sources(22) Database Lookups(50) Deployment(22) Designer(177) Device(1) DotNetNuke(12) EASE(20) Email(6) Features(101) Firebird(1) Form Builder(14) Globalization and Localization(6) How To(1) Hypermedia(2) Inline Editing(1) Installation(5) JavaScript(20) Kiosk(1) Low Code(3) Mac(1) Many-To-Many(4) Maps(6) Master/Detail(36) Microservices(4) Mobile(63) Mode Builder(3) Model Builder(3) MySQL(10) Native Apps(5) News(18) OAuth(9) OAuth Scopes(1) OAuth2(13) Offline(20) Offline Apps(4) Offline Sync(5) Oracle(11) PKCE(2) Postgre SQL(1) PostgreSQL(2) PWA(2) QR codes(2) Rapid Application Development(5) Reading Pane(2) Release Notes(183) Reports(48) REST(29) RESTful(29) RESTful Workshop(15) RFID tags(1) SaaS(7) Security(81) SharePoint(12) SPA(6) SQL Anywhere(3) SQL Server(26) SSO(1) Stored Procedure(4) Teamwork(15) Tips and Tricks(87) Tools for Excel(2) Touch UI(93) Transactions(5) Tutorials(183) Universal Windows Platform(3) User Interface(338) Video Tutorial(37) Web 2.0(100) Web App Generator(101) Web Application Generator(607) Web Form Builder(40) Web.Config(9) Workflow(28)
Archive
Blog
Friday, October 26, 2012PrintSubscribe
Order Form Sample–Part 21

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.

New Field context menu option for Orders data controller.

The new field’s settings will be:

Property Value
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.

Simplified statement of 'Subtotal' SQL Formula calculation in SQL Management Studio

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.

Dropping Subtotal field node onto 'grid1' view node.     Subtotal data field node has been created in 'grid1'.

Drop Subtotal field node onto Orders / Views / editForm1 node.

Dropping Subtotal field node onto 'editForm1' view node.     Subtotal data field node has been created in 'editForm1'.

Drop Subtotal field node onto Orders / Views / createForm1 view node.

Dropping Subtotal field node onto 'createForm1' view node.    Subtotal data field node has been created in 'createForm1'.

Regenerate the application, and select any order on the Order Form page. You will see the Subtotal field showing the sum of extended prices.

Subtotal data field is present in editForm1 of Orders.