Blog: Posts from January, 2013

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
Posts from January, 2013
Wednesday, January 16, 2013PrintSubscribe
“When Client Script” Property for Actions

Suppose that you have implemented the Order Form Sample with transactions using a “Status” field. The Status will partition “draft” orders from “committed” orders. However, the user may still navigate to the Orders page and change the values and details of a committed order.

Committed order values can be changed on the 'Orders' page.

Let’s prevent users from activating any Edit action when the Status field is set to “Committed”. This will be implemented with the help of “When Client Script” property. When the specified JavaScript expression evaluates to true, the action will be displayed. When it evaluates to false, the action will be hidden.

Adding Status Field

First, the Status field must be added to the controller so that the field value can be used in the JavaScript expression. Start the web application generator, and click on the project name. Press Refresh, and check the box next to Orders controller. Press Refresh, and confirm.

Refreshing the Orders controller.

On the Summary page, click on Design to activate the Project Designer. In the Project Explorer, switch to the Controllers tab and expand Orders / Fields node. Drop Status (String(50)) node onto Orders / Views / grid1. The field will be instantiated as a data field in the view.

Dropping 'Status' field onto 'grid1' view of Orders controller.     Data field for 'Status' created in 'grid1' view.

Double-click on Orders / Fields / Status (String(50)) node.

Status field of Orders controller.

Mark the field as hidden.

Property New Value
The field is hidden from users. true

Press OK to save.

Hiding Edit Fields

In the Project Explorer, double-click on Orders / Actions / ag1 (Grid) / a2 – Edit action node.

Action 'a2 - Edit' of action group 'ag1'.

Make the following change:

Property New Value
When Client Script
[Status] != 'Committed'

Press OK to save.

Make the same change to Orders / Actions / ag2 (Form) / a1 – Edit action node, and Orders / Actions / ag4 (ActionBar) – Edit/Delete / a1 – Edit, editForm1 action node.

Two Edit actions highlighted in Orders controller.

Viewing the Results

On the Project Explorer toolbar, press Browse. Navigate to Customers | Orders page.

Note that the Edit action is no longer available on the action bar or in the row context menu.

Edit actions on context menu and action bar are no longer available.

In addition, the form will no longer have an Edit button.

Edit button is no longer present in the form.

Monday, January 14, 2013PrintSubscribe
“Filter Source” Property for Data Views

The Filter Source property specifies the master data view that will pass down a filtering parameter to the child data view. The child data field that will be filtered is declared in the Filter Field property, and must be specified in addition to Filter Source in order for master-detail relationships to work. When the master data view has a compound primary key, up to 5 Filter Fields must be specified in order.

Let’s configure a master-detail relationship between Customers and Orders controllers in the Northwind database.

Adding the Page

Start the Project Designer. On the Project Explorer toolbar, click on the New Page icon.

Adding a new page to the project.

Assign a name:

Property Value
Name Customers and Orders

Press OK to save. Switch to the Controllers tab. Select Customers controller node. While holding Ctrl key, select Orders node. Right-click on Orders, and press Copy.

Copying the 'Orders' and 'Customers' controllers.

Switch back to the Pages tab. Right-click on Customers and Orders node, and press Paste. The two controllers will be instantiated as data views in separate containers.

Pasting the copied controllers onto 'Customers and Orders' page node.     'Customers' and 'Orders' data views have been instantiated as data views in separate containers.

Configuring Master-Detail Relationship

However, these data views are currently independent from each other – a master-detail relationship must be configured.

Expand Customers and Orders / c102 / view2 (Orders) / grid1 node. Drop CustomerID –> CustomerCompanyName data field node onto Customers and Orders / c101 / view1 (Customers) node.

Dropping 'CustomerID' data field onto 'view1'.     A master-detail relationship has been configured between 'view2' and 'view1'.

Because CustomerID field is matched to a similar field in Customers controller, a master-detail relationship is automatically configured. Filter Source is changed  to “view1”, and Filter Field is changed to “CustomerID”. In addition, the Page Size has been changed to “5”, Auto Hide has been changed to “Container”, Show Modal Forms has been enabled, and Text is changed to the name of the controller, “Orders”.

On the Project Designer toolbar, press Browse. When generation is complete, navigate to the Customers and Orders page.

The page will initially only show a list of customers.

A list of customers is displayed.

When a row is selected, a list of orders will appear underneath. These orders will be filtered by the value of “CustomerID” field in the selected row of “view1” data view (Customers).

A list of orders filtered by 'CustomerID' is displayed below.

Note that the filtered “CustomerID” data field in Orders view is hidden in order to prevent display of duplicate data. An SQL action may be implemented if the hidden foreign key field needs to be changed.

Sunday, January 13, 2013PrintSubscribe
Validation with SQL Business Rules

It is a requirement for every database web application to perform data validation. SQL business rules can be used to extend the functionality of basic database validation to ensure that the user enters values that conform to business requirements.

Server-side validation has the benefit of direct access to database and sophisticated APIs of the server-side operating system and database engine. One major limitation is the inability to have a conversation with the user – the server-side code cannot ask questions of the user without complex chained internet callbacks. If the business rule implementation requires confirming certain aspects of validation by directly requesting information from the application user, then a validating JavaScript business rule must be implemented. If a “conversational” validation business logic requires access to the database, then consider utilizing the RESTful application server of your web app.

Consider the following example below. The default Northwind web application allows users to select a product and enter any unit price, quantity, and discount. The database engine will validate the constraints and raise an exception if an error occurs.

Basic database engine validation in action.

However, the database server will not test for logical violations, such as absurd unit price or quantity.

Let’s implement an SQL business rule that will perform multi-step validation.

Configuring Fields

Start the Project Designer. Switch to the Controllers tab in the Project Explorer. Double-click on OrderDetails / Fields / ProductID* (Int32) –> Products node.

Field 'ProductID' of Order Details controller.

Change the following:

Property Value
Copy UnitPrice=UnitPrice

Press OK to save. Next, double-click on UnitPrice* (Decimal) field node.

'UnitPrice' field of Order Details controller.

Make the following changes:

Property Value
The value of this field is calculated by a business rule expression. true
Context Fields ProductID, UnitPrice, Quantity, Discount

Press OK to save.

Configuring Business Rule

Right-click on OrderDetails / Business Rules node, and press New Business Rule.

Creating a new business rule for OrderDetails controller.

Assign the following values:

Property Value
Type SQL
Command Name Calculate|Insert|Update
Phase Execute

Paste in the following script:

-- 1. If the collected values are not valid then do not enforce the rule.
--    The client library will instruct the user to correct the input.
declare @Success nvarchar (5) = 'true'

-- 2. Reset the base price for calculation of price limits
--    if the product selection has changed or if an existing
--    data row has been selected for editing
if (@ProductID != @Session_ProductID or @Session_ProductID is null)
begin
    declare @BasePrice decimal = @UnitPrice
    if (@Arguments_Trigger != 'ProductID')
    begin
        set @BasePrice = @UnitPrice_OldValue
    end
    set @Session_UnitPrice = @BasePrice
    set @Session_ProductID = @ProductID
    if (@Arguments_Trigger = 'ProductID')
    begin
        set @Quantity = 1
        set @Discount = 0
        set @Result_Focus = 'Quantity'
    end
end

-- 3. Adjusting base price for an existing record
declare @OriginalUnitPrice float = @Session_UnitPrice
if (@Session_UnitPrice is null)
begin
    set @OriginalUnitPrice = @UnitPrice_OldValue
    set @Session_UnitPrice = @OriginalUnitPrice
end

-- 4. Validate Unit Price Field
declare @MinPrice float = @OriginalUnitPrice
declare @MaxPrice float = (@OriginalUnitPrice * 1.05)
if (@UnitPrice is null)
    set @Result_Focus = 'UnitPrice, Please enter the price.'
else
    if (@UnitPrice < @MinPrice or @UnitPrice >= @MaxPrice)
        set @Result_Focus = 'UnitPrice, The price must be between $' 
                        + cast(convert(money, @MinPrice) as nvarchar(50)) + ' and $' 
                        + cast(convert(money, @MaxPrice) as nvarchar(50)) + '.'
        set @Success = 'false'

-- 5. Validate Quantity Field
if (@Quantity is not null and @Quantity <= 0)
begin
    set @Result_Focus = 'Quantity, The quantity must be greater than zero.'
    set @Success = 'false'
end

-- 6. Validate Discount Field
if (@Discount > 1)
    set @Discount = (@Discount / 100)

-- 7. Confirm Discount is between 0.00 and 0.99
if (@Discount is not null and (@Discount < 0.00 or @Discount > 0.99))
begin
    set @Result_Focus = 'Discount, The discount must be between 0.00 and 0.99 (0% - 99%).'
    set @Success = 'false'
end

-- 8. Wrapping up
if (@Arguments_CommandName = 'Calculate' or @Success = 'false')
    set @BusinessRules_PreventDefault = 1

The business rule will be fired when a user inserts a new record, updates an existing record, and every time a field value is changed. The client library will first ensure that the values are valid. Then the script will be sent to the database server. The field values are bound by the application framework as @FieldName. Session variables are introduced to the web application by using @Session_FieldName. The name of the field that triggered the command is recorded in @Arguments_Trigger, and the command is saved in @Arguments_Command. The client library is instructed to display messages next to fields using @Result_Focus. The parameter @BusinessRules_PreventDefault will prevent the default behavior of the web application from continuing.

There are eight distinct steps to this business rule:

1. Initial Input Validation

When a user enters an invalid value in the Order Details form, the client library will display an error and prevent execution of the business rule – no code needs to be written for this to occur.

The variable @Success is declared in order to remember the status of validation. By default, it is set to “true”. If any of the later steps fail to validate, this variable will be changed to “false” and the last step will ensure that the user cannot apply changes.

declare @Success nvarchar (5) = 'true'

The picture below shows an example of automatic client-side validation.

Example of automatic client-side validation.

2. Determination of the “Base” Price

This query will memorize the last ProductID and UnitPrice values in session variables, as well as the UnitPrice of the selected product when it is copied over. This UnitPrice will be saved as @BasePrice, and will be used to prevent price inflation by more than 5%. When the ProductID is changed, the Quantity and Discount fields will be reset to default values of 1 and 0.

if (@ProductID != @Session_ProductID or @Session_ProductID is null)
begin
    declare @BasePrice decimal = @UnitPrice
    if (@Arguments_Trigger != 'ProductID')
    begin
        set @BasePrice = @UnitPrice_OldValue
    end
    set @Session_UnitPrice = @BasePrice
    set @Session_ProductID = @ProductID
    if (@Arguments_Trigger = 'ProductID')
    begin
        set @Quantity = 1
        set @Discount = 0
        set @Result_Focus = 'Quantity'
    end
end

The screenshot below shows the form after the product has been changed.

Selecting a new product will reset Unit Price, Quantity, and Discount.

3. Adjusting Base Price for Existing Records

If the user is editing an existing record, then the original UnitPrice value is memorized using a session variable.

declare @OriginalUnitPrice float = @Session_UnitPrice
if (@Session_UnitPrice is null)
begin
    set @OriginalUnitPrice = @UnitPrice_OldValue
    set @Session_UnitPrice = @OriginalUnitPrice
end

4. Price Validation

The minimum and maximum price is calculated. If the price is blank, the user is directed to enter a value. If the price is out of range, the user is forced to correct the problem.

declare @MinPrice float = @OriginalUnitPrice
declare @MaxPrice float = (@OriginalUnitPrice * 1.05)
if (@UnitPrice is null)
    set @Result_Focus = 'UnitPrice, Please enter the price.'
else
    if (@UnitPrice < @MinPrice or @UnitPrice >= @MaxPrice)
        set @Result_Focus = 'UnitPrice, The price must be between ' 
                        + cast(@MinPrice as nvarchar(50)) + ' and ' 
                        + cast(@MaxPrice as nvarchar(50)) + '.'
        set @Success = 'false'

The form below shows price validation in action.

Price validation in form view on Unit Price field.

Here is validation in data sheet view.

Price validation in Order Details data sheet view.

5. Quantity Validation

Validation will ensure that a positive value is entered into Quantity field.

if (@Quantity is not null and @Quantity <= 0)
begin
    set @Result_Focus = 'Quantity, The quantity must be greater than zero.'
    set @Success = 'false'
end
Validation of a positive integer in Quantity field.

6. Automatic Conversion of Discount

When the user enters a value greater than 1, the business rule will automatically convert the value to a fraction of 100.

if (@Discount > 1)
    set @Discount = (@Discount / 100)

For example, a value of “25” has been entered in the New Order Details screen shown below.

Discount field value of '25' is entered.

When focus is shifted from the field, the value is converted to a percentage.

The value of Discount is converted into a percentage.

7. Discount Range Validation

A validation error will be displayed if the Discount value is not within range after automatic conversion.

if (@Discount is not null and (@Discount < 0.00 or @Discount > 0.99))
begin
    set @Result_Focus = 'Discount, The discount must be between 0.00 and 0.99 (0% - 99%).'
    set @Success = 'false'
end
Percentage validation of 'Discount' field.

8. Wrapping Up

The default behavior of the application server will be to continue processing the Insert or Update action triggered by the user. If any of the validations have failed or the command was Calculate, the default behavior will be halted.

-- 8. Wrapping up
if (@Arguments_CommandName = 'Calculate' or @Success = 'false')
    set @BusinessRules_PreventDefault = 1