Blog: Posts from November, 2012

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 November, 2012
Thursday, November 29, 2012PrintSubscribe
Items Data Controller

The Items Data Controller property allows the population of lookup values from another controller in the web app.

For example, suppose that you have configured the static lookup items for Reorder Level field in the Products table of Northwind database.

Reorder Level drop down list with static values.

Providing a list of static lookup items offers many advantages, such as limiting user input while providing a clear list of options.

The functionality can be extended by creating a table to store these values externally. This will allow user management of lookup items.

Creating the Reorder Level Table

Start SQL Server Management Studio. In the Object Explorer, right-click on Databases / Northwind / Database Diagrams, and press New Database Diagram.

Creating a new database diagram for 'Northwind' database in SQL Server Management Studio.

If prompted to create support objects required to use database diagramming, press Yes.

Press 'Yes' to confirm creation of support objects required to use database diagramming.

When the Add Table window appears, select Products table and press Add. Then press Close.

Adding Products table to the diagram.

Right-click on white space and press New Table.

Creating a new table in the database diagram

Enter the name of “ReorderLevels” for the table and press OK.

Assigning a name of 'ReorderLevel' for the new table.

Give this table the following columns:

Is Primary Key Column Name Data Type Allow Nulls
Yes ReorderLevelNumber smallint No
No ReorderLevelText nvarchar(50) No

Save the diagram.

In the Object Explorer, right-click on Databases / Tables / dbo.ReorderLevel node, and press Edit Top 200 Rows.

Context menu option 'Edit Top 200 Rows' for ReorderLevel table in the Northwind database.

Insert the following rows:

ReorderLevelNumber ReorderLevelText
0 Zero
5 Five
10 Ten
15 Fifteen
20 Twenty
25 Twenty-five
30 Thirty

Go back to the database diagram. Drag ReorderLevelNumber column from ReorderLevel table onto ReorderLevel column in Products table.

Creating a foreign key relationship between Products and ReorderLevel tables.

Confirm that the right fields are configured and press OK twice to save the foreign key relationship.

Foreign key relationship between Products and ReorderLevel tables.

Finally, add the controller to the project by refreshing the project settings with ReorderLevel table selected.

Refreshing the project to add ReorderLevel table.

Configuring the Lookup

Start the Project Designer. In the Project Explorer, expand Products / Fields /  ReorderLevel field n0de. Highlight all items underneath the field node, right-click, and press Delete.

Deleting static lookup items for 'ReorderLevel' field.

Double-click on Products / Fields / ReorderLevel node.

'ReorderLevel' field in Products controller.

Change the Items Data Controller in order to populate the dropdown with data from the selected controller:

Property Value
Items Data Controller ReorderLevel

Press OK to save. On the toolbar, press Browse.

Navigate to the Products page, and edit a record. The Re0rder Level field will be populated with values from the ReorderLevel controller.

'Reorder Level' field is populated with values from 'ReorderLevel' table.

Values can be added, edited, or deleted by navigating to the Reorder Level page.

Creating a new Reorder Level record.

Note that using Drop Down List is not the most efficient way to render a lookup – the client library will populate the drop down when the form is initialized. If the controller has a lot of data, it may impact the database server. It would be more advisable to use Auto Complete or Lookup style.
Thursday, November 29, 2012PrintSubscribe
Multi-Field Copy with a C# / Visual Basic Business Rule

It may be necessary to copy similar values from a lookup to a record.

For simple copy operations, the Copy property can be used. For more complex operations, a business rule can be implemented. Let’s implement a C# / Visual Basic business rule to copy shipping information from a customer to an order in a sample Northwind web application.

Start the Project Designer. In the Project Explorer, switch to the Controllers tab and double-click on Orders / Fields / ShipName node.

ShipName field in the Orders controller in Code On Time web application designer.

Change the following propeties.

Property Value
The value of the field is calculated by a business rule expression true
Context Fields CustomerID

Press OK to save the field.

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

New Business Rule context menu option for Orders controller in the Project Explorer.

Give this rule the following properties.

Property Value
Type C# / Visual Basic
Command Name New|Calculate
Phase Execute

Press OK to save the business rule. On the toolbar, press Browse to generate the web application and business rule file.

When complete, right-click on Orders / Business Rules / New|Calculate node, and press Edit Rule in Visual Studio.

Edit Rule in Visual Studio context menu option for a business rule will open the file in Visual Studio.

The business rule handler will open in Visual Studio. Replace the default implementation with the following:

C#:

using System;
using MyCompany.Data;

namespace MyCompany.Rules
{
    public partial class OrdersBusinessRules : MyCompany.Data.BusinessRules
    {

        /// <summary>
        /// This method will execute in any view for an action
        /// with a command name that matches "New|Calculate".
        /// </summary>
        [Rule("r100")]
        public void r100Implementation(
                    int? orderID,
                    string customerID,
                    string customerCompanyName,
                    int? employeeID,
                    string employeeLastName,
                    DateTime? orderDate,
                    DateTime? requiredDate,
                    DateTime? shippedDate,
                    int? shipVia,
                    string shipViaCompanyName,
                    decimal? freight,
                    string shipName,
                    string shipAddress,
                    string shipCity,
                    string shipRegion,
                    string shipPostalCode,
                    string shipCountry)
        {
            // This is the placeholder for method implementation.
            if (!String.IsNullOrEmpty(customerID))
                using (SqlText findCustomer = new SqlText(
                    "select " +
                    "    ContactName, " +
                    "    Address, " +
                    "    City, " +
                    "    Region, " +
                    "    PostalCode, " +
                    "    Country " +
                    "from Customers " +
                    "where  " +
                    "    CustomerID = @CustomerID "))
                {
                    findCustomer.AddParameter("@CustomerID", customerID);
                    if (findCustomer.Read())
                    {
                        UpdateFieldValue("ShipName", findCustomer["ContactName"]);
                        UpdateFieldValue("ShipAddress", findCustomer["Address"]);
                        UpdateFieldValue("ShipCity", findCustomer["City"]);
                        UpdateFieldValue("ShipRegion", findCustomer["Region"]);
                        UpdateFieldValue("ShipPostalCode", findCustomer["PostalCode"]);
                        UpdateFieldValue("ShipCountry", findCustomer["Country"]);
                    }
                }
        }
    }
}

Visual Basic:

Imports MyCompany.Data
Imports System

Namespace MyCompany.Rules

    Partial Public Class OrdersBusinessRules
        Inherits MyCompany.Data.BusinessRules

        ''' <summary>
        ''' This method will execute in any view for an action
        ''' with a command name that matches "New|Calculate".
        ''' </summary>
        <Rule("r100")> _
        Public Sub r100Implementation( _
                    ByVal orderID As Nullable(Of Integer), _
                    ByVal customerID As String, _
                    ByVal customerCompanyName As String, _
                    ByVal employeeID As Nullable(Of Integer), _
                    ByVal employeeLastName As String, _
                    ByVal orderDate As Nullable(Of DateTime), _
                    ByVal requiredDate As Nullable(Of DateTime), _
                    ByVal shippedDate As Nullable(Of DateTime), _
                    ByVal shipVia As Nullable(Of Integer), _
                    ByVal shipViaCompanyName As String, _
                    ByVal freight As Nullable(Of Decimal), _
                    ByVal shipName As String, _
                    ByVal shipAddress As String, _
                    ByVal shipCity As String, _
                    ByVal shipRegion As String, _
                    ByVal shipPostalCode As String, _
                    ByVal shipCountry As String)
            'This is the placeholder for method implementation.
            If Not String.IsNullOrEmpty(customerID) Then
                Using findCustomer As SqlText = New SqlText(
                    "select " +
                    "    ContactName, " +
                    "    Address, " +
                    "    City, " +
                    "    Region, " +
                    "    PostalCode, " +
                    "    Country " +
                    "from Customers " +
                    "where  " +
                    "    CustomerID = @CustomerID ")
                    findCustomer.AddParameter("@CustomerID", customerID)
                    If (findCustomer.Read()) Then
                        UpdateFieldValue("ShipName", findCustomer("ContactName"))
                        UpdateFieldValue("ShipAddress", findCustomer("Address"))
                        UpdateFieldValue("ShipCity", findCustomer("City"))
                        UpdateFieldValue("ShipRegion", findCustomer("Region"))
                        UpdateFieldValue("ShipPostalCode", findCustomer("PostalCode"))
                        UpdateFieldValue("ShipCountry", findCustomer("Country"))
                    End If
                End Using
            End If
        End Sub
    End Class
End Namespace

Save the file. Switch back to the Project Designer, and press Browse on the toolbar.

Navigate to Customers page. Select a customer, and a list of orders will appear underneath. On the action bar, press New Orders. The shipping information will be populated using the customer information.

On the New Orders form, the shipping information has been populated using the values from the customer.

Navigate to the Orders page. On the action bar, press New Orders. The shipping fields will be empty.

New Orders form has no customer selected, so no shipping information is populated.

Select a customer using the Customer Company Name lookup. The shipping information will be populated.

Once a customer is selected, the shipping information is copied.

Thursday, November 29, 2012PrintSubscribe
Multi-Field Copy with an SQL Business Rule

It may be necessary for similar values to be copied from a lookup to a record.

When a simple copy operation is required, use the Copy property on the lookup field. For more complex copying operations, a business rule can be implemented. Let’s implement an SQL business rule to copy shipping information from a customer to an order in a sample Northwind web application.

Start the Project Designer. In the Project Explorer, switch to the Controllers tab and double-click on Orders / Fields / ShipName node.

ShipName field in the Orders controller in the Project Explorer.

Change the following properties.

Property Value
The value of the field is calculated by a business rule expression true
Context Fields CustomerID

Press OK to save the field. Right-click on Orders / Business Rules node and press New Business Rule.

New Business Rule context menu option in the Orders controller.

Give this rule the following properties.

Property Value
Type SQL
Command Name New|Calculate
Phase Execute
Script
if @CustomerID is not null
begin
     select 
        @ShipName = ContactName,
        @ShipAddress = Address,
        @ShipCity = City,
        @ShipRegion = Region,
        @ShipPostalCode = PostalCode,
        @ShipCountry = Country
     from Customers 
     where 
         CustomerID = @CustomerID
end

Press OK to save the business rule. On the toolbar, press Browse to generate the web application.

Navigate to the Customers page. Select a customer from the list, and a list of orders will appear underneath. On the action bar, press New Orders. The shipping fields will be prepopulated with the customer shipping information.

New Orders form on the Customers page prepopulated with customer shipping information.

Navigate to the Orders page. On the action bar, press New Orders. The ship fields will be blank.

New Orders form on the Orders page. Customer is not selected, and the shipping fields are blank.

Use the Customer Company Name lookup to select a customer. The ship fields will automatically be populated with the values from the customer.

A customer has been selected. Shipping information has been populated.

Continue to Logging Data Access