Database Lookups

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
Database Lookups
Tuesday, July 24, 2012PrintSubscribe
Tooltips

Tooltips, also called hints, are commonly used to clarify application element functions to the end user.

Let’s apply a tooltip to the Unit Price data field in the New Order Details form.

Start the Project Designer. In the Project Explorer, switch to the Controllers tab and double-click on OrderDetails / Views / createForm1 / c1 – New Order Details / UnitPrice data field node.

UnitPrice data field in createForm1 of OrderDetails controller in the Project Explorer.

Change the Tooltip property:

Property New Value
Tooltip Please specify the agreed upon price per unit in this order.

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

Navigate to the Order Details page, and create a new order detail. Mouse over the Unit Price input, and the specified tooltip will appear.

Specified tooltip displayed when user mouses over Unit Price input box.

Friday, June 15, 2012PrintSubscribe
Working with Picklists

Picklists are commonly used in web applications in order to allow a user to pick one item from a list of available options. These lists prevent duplication of similar values, such as alternate spellings. Picklists are physically different from database lookup fields since there is no foreign key relationship between the tables. The application generator automatically configures lookup fields. Developers can configure a picklist on any field in a web application.

For example, let’s look at the City and Country fields in the Customers edit form. Both fields are simple text boxes – the user can type any value.

City and Country fields rendered as simple text boxes.

Let’s convert both fields to picklists.

Creating Cities and Countries Tables

Start SQL Server Management Studio. In the Object Explorer, right-click on Databases / Northwind / Tables node, and select New Table option.

Create New Table in Northwind database using SQL Server Management Studio.

Give this table the following columns:

Column Name Data Type Allow Nulls
CountryID int No
CountryName nvarchar(50) No

Right-click on the CountryID column, and select Make Primary ID.

Set CountryID column as a primary key of Countries table.

Under Column Properties, change the following setting:

Property Value
(Is Identity) Yes

Press Ctrl+S to save the table. Give the table the name of “Countries”.

Create another table with the following columns:

Column Name Data Type Allow Nulls
CityID int No
CityName nvarchar(50) No

Right-click on the CityID column, and select Make Primary ID.

Set CityID column as a primary key of Cities table.

Under Column Properties, change the following setting:

Property Value
(Is Identity) Yes

Save the table, and give it the name of “Cities”.

Start Code On Time web application generator. Refresh, and add the two new tables to the web application.

Refresh the project to add the Cities and Countries tables.

Finish generating the application.

Populate the Tables

Next, populate the Cities and Countries table using the values currently existing in the Customers table. A full list of values can be found by activating the dropdown on the column header and clicking the “Filter…” option.

List of City values.

Insert all City values in the Cities table, and all Country values in the Countries table.

Populated Countries table using values extracted from Customers.

Converting Country and City fields into a Picklist

Start the Project Designer. In the Project Explorer, switch to the Controllers tab. Double-click on Customers / Fields / City field node.

City field of Customers controller in Project Designer.

Change the following properties:

Property Value
Items Style Lookup
Items Data Controller Cities

Press OK to save the field. Double-click on Customers / Fields / Country field node.

Country field of Customers controller.

Make the following changes:

Property Value
Items Style Lookup
Items Data Controller Countries

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

Navigate to the Customers page, and edit a record. The City and Country fields will be rendered as lookup links.

City and Country fields on Customers edit form are rendered as lookups.

Click on the link for City field, and a lookup modal window will display a list of cities.

Clicking on the City lookup link will activate a modal window displaying a list of cities.

Select a lookup item from the list. Save the record, and select the record again. Instead of CityName being inserted into the field, the CityID was inserted.

CityID inserted into the City field.

The same behavior occurs for Country lookup field.

CountryID of the lookup item inserted into the Country field.

This is happening because the primary key of the lookup is automatically used as the value of the selected record.

You can address this in two different ways.

Changing the Primary Key of the Lookup Controller

Switch back to the Project Designer. In the Project Explorer, right-click on Countries / Fields node, and select List option.

Show List of all Fields in the Countries controller.

Change the field properties:

Name Is Primary Key
CountryID No
CountryName Yes

You can repeat this procedure for Cities controller, or configure the lookup properties of the City field as explained next.

Specifying the Data Value Field and Data Text Field

In the Project Explorer, double-click on Customers / Fields / City field node.

City field in the Customers controller.

Make the following changes:

Property Value
Data Value Field CityName
Data Text Field CityName

Press OK to save the field.

The client library always uses Data Value Field and Data Text Field to determine which lookup field represents the key of the selected item, and which field represents the text. When the Data Value Field is not specified, then the value of the primary key field of the selected lookup item will become a lookup value. When the Data Text Field is not specified, the text will be derived from the first visible column.

Trying it Out

On the toolbar, press Browse. Navigate to Customers page and edit a record. Change the City and Country fields, save, and select the record again. The names of City and Country will be the values inserted into the respective fields.

City and Country Name values will be inserted into the fields.

This can be confirmed by checking the record in SQL Server Management Studio.

SQL Server Management Studio query confirms that the names were inserted into the City and Country fields.

Friday, June 15, 2012PrintSubscribe
Implicit Filters with Dynamic Access Control Rules

The property Context Fields can pass values from the current record to the lookup data view. The value is passed in the format LookupFieldName=FieldNameOfThisView as an external filter. Multiple value mappings can be specified.

If LookupFieldName matches a data field in the lookup view, then an automatic “equals” filter will be applied to the lookup. If the LookupFieldName does not match, then the application framework will not perform filtering. A developer can use the passed external filter field value to create a filter expression or business rule implementing custom filtering.

Let’s create a business rule for a lookup view that takes advantage of values passed in the Context Fields property.

Navigate to the Orders page, and select an order. Create a new order detail, and activate the lookup for ProductID. The Northwind database has 77 products. All products will be available for selection in the Products lookup.

Lookup list of all 77 products available for selection.

Let’s exclude products already associated with order details of the existing order from this view.

Start the Project Designer. In the Project Explorer, switch to the Controllers tab. Double-click on Products controller node.

Products controller selected in the Project Explorer.

Change the Handler property:

Property New Value
Handler ProductsBusinessRules

Press OK to save the controller. Double-click on OrderDetails / Fields / ProductID field node.

ProductID field of OrderDetails controller.

Change the Context Fields property:

Property New Value
Context Fields ExistingOrderID=OrderID

Press OK to save the field. On the toolbar, press Exit to close the Project Designer, and click Generate.

When complete, click on the project name, and select Develop to open Visual Studio.

In the Solution Explorer of Visual Studio, double-click on ~\App_Code\Rules\ProductsBusinessRules.cs(.vb) file.

ProductsBusinessRules file in the Code On Time web application.

Replace the existing code with the following business rule:

C#:

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using MyCompany.Data;

namespace MyCompany.Rules
{
    public partial class ProductsBusinessRules : MyCompany.Data.BusinessRules
    {
        protected override void EnumerateDynamicAccessControlRules(string Products)
        {
            FieldValue orderId = SelectExternalFilterFieldValueObject(
                "ExistingOrderID");
            if (orderId != null && orderId.Value != null)
                RegisterAccessControlRule("ProductID",
                    "[ProductID] in (select ProductID from [Order Details] " +
                    "where OrderID = @OrderID)",
                    AccessPermission.Deny,
                    new SqlParam("@OrderID", orderId.Value));
        }
    }
}

Visual Basic:

Imports MyCompany.Data
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Linq

Namespace MyCompany.Rules

    Partial Public Class ProductsBusinessRules
        Inherits MyCompany.Data.BusinessRules
        Protected Overrides Sub EnumerateDynamicAccessControlRules(Products As String)
            Dim orderId As Object = SelectExternalFilterFieldValueObject(
                "ExistingOrderID")
            If (orderId IsNot Nothing AndAlso orderId.Value IsNot Nothing) Then
                RegisterAccessControlRule("ProductID",
                    "[ProductID] in (select ProductID from [Order Details] " +
                    "where OrderID = @OrderID)",
                    AccessPermission.Deny,
                    New SqlParam("@OrderID", orderId.Value))
            End If
        End Sub
    End Class
End Namespace

The business rule tries to locate the external filter field ExistingOrderID. If it is found, and the value is not null, then the business rule will register an access control rule. The access control rule will deny access to products that are matched to the “Order Details”.“ProductID” column if the column OrderID is equal to the value passed in ExistingOrderID filter field.

Save the file, and run the web application.

Navigate to the Orders page, and select an order. Note the number of order details belonging to the order.

Order Details child view displaying 3 records that belong to the selected order.

Create a new order detail. Click on (select) link in the Product Name lookup.

Product Name lookup on the New Order Details create form.

The Product lookup will open. There will be no products that are already ordered. In the example below, there are only 74 products displayed out of 77 products in the database.

Limited subset of 74 products displayed in the lookup.