Database Lookups

Database Lookups
Tuesday, April 7, 2015PrintSubscribe
Data Fields: Hiding the Lookup Details Arrow

Lookup data fields in forms will automatically show a lookup details arrow button to the far right of the field. The screenshot below shows the lookup details arrow for the Customer Company Name field of the Orders controller in the sample Northwind web app.

The Orders page showing the lookup details arrow next to the Customer Company Name field.

Clicking the arrow will allow the user to directly access the lookup record.

The customer record has been opened.

Sometimes, it may be necessary to prevent the user from accessing the lookup record.

Start the Project Designer. In the Project Explorer, switch to the Controllers tab and double click on Orders / Views / editForm1 / CustomerID data field node.

The CustomerID field of the Orders controller.

Append the following tag to the Tags property.

Property Value
Tags lookup-details-hidden

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

When the app opens in the browser, navigate to the Orders page and select a record. Note that the lookup details arrow next to the Customer Company Name field is now hidden.

The lookup details arrow next to Customer Company Name field is no longer displayed.

Wednesday, February 13, 2013PrintSubscribe
Setting Default Value for Lookup Fields

A common enhancement in line-of-business web applications is to provide default values when a new record is being created. Default values may be set using an SQL business rule. However, lookup fields require two fields to be populated – one value for the actual foreign key column in the table (typically a unique identifier or Guid) and another value for the alias field (typically the name of the lookup item).

For example, the ReportsTo column of the Employees table in the Northwind sample database contains the EmployeeID of the manager employee. A default web application created from this database creates an alias field ReportsToLastName. This virtual field is selected from the parent table and displayed as the value.

ReportsTo data field in editForm1 of Employees controller has an alias of 'ReportsToLastName'.

If a default value is set for the ReportsTo field, the display text will not be updated and will appear to be null. However, when the user saves the record, the ReportsToLastName field value will be calculated and displayed properly.

To ensure that the last name is displayed, let’s create an SQL business rule that will populate both ReportsTo and ReportsToLastName.

Start the Project Designer. In the Project Explorer, switch to the Controllers tab. Right-click on Employees / Business Rules node, and press New Business Rule.

Creating a new business rule for Employees controller.

Select one of the following methods below.

SQL Business Rule

Assign the following values:

Property Value
Type SQL
Command Name New
Phase Execute
set @ReportsTo = 1
set @ReportsToLastName = 'Fuller'

Press OK to save the business rule.

JavaScript Business Rule

Assign the following values:

Property Value
Type JavaScript
Command Name New
Phase After
set @ReportsTo = 1
set @ReportsToLastName = 'Fuller'

Press OK to save the business rule.

C# / Visual Basic Business Rule

Assign the following values:

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

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

When complete, right-click on Employees / Business Rules / New (Code / After) – r100 and press Edit Rule in Visual Studio.

Editing the rule in Visual Studio.

The file will open in Visual Studio. Replace the code base with the following:


using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text.RegularExpressions;
using System.Web;
using System.Web.Security;
using MyCompany.Data;

namespace MyCompany.Rules
    public partial class EmployeesBusinessRules : MyCompany.Data.BusinessRules
        public void r100Implementation(
                    int? employeeID, 
                    string lastName, 
                    string firstName, 
                    string title, 
                    string titleOfCourtesy, 
                    DateTime? birthDate, 
                    DateTime? hireDate, 
                    string address, 
                    string city, 
                    string region, 
                    string postalCode, 
                    string country, 
                    string homePhone, 
                    string extension, 
                    string notes, 
                    int? reportsTo, 
                    string reportsToLastName, 
                    string photoPath)
            UpdateFieldValue("ReportsTo", 1);
            UpdateFieldValue("ReportsToLastName", "Fuller");

Visual Basic:

Imports MyCompany.Data
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Linq
Imports System.Text.RegularExpressions
Imports System.Web
Imports System.Web.Security

Namespace MyCompany.Rules
    Partial Public Class EmployeesBusinessRules
        Inherits MyCompany.Data.BusinessRules
        <Rule("r100")>  _
        Public Sub r100Implementation( _
                    ByVal employeeID As Nullable(Of Integer),  _
                    ByVal lastName As String,  _
                    ByVal firstName As String,  _
                    ByVal title As String,  _
                    ByVal titleOfCourtesy As String,  _
                    ByVal birthDate As Nullable(Of DateTime),  _
                    ByVal hireDate As Nullable(Of DateTime),  _
                    ByVal address As String,  _
                    ByVal city As String,  _
                    ByVal region As String,  _
                    ByVal postalCode As String,  _
                    ByVal country As String,  _
                    ByVal homePhone As String,  _
                    ByVal extension As String,  _
                    ByVal notes As String,  _
                    ByVal reportsTo As Nullable(Of Integer),  _
                    ByVal reportsToLastName As String,  _
                    ByVal photoPath As String)
            UpdateFieldValue("ReportsTo", 1)
            UpdateFieldValue("ReportsToLastName", "Fuller")
        End Sub
    End Class
End Namespace

Viewing the Results

On the toolbar, press Browse.

Navigate to the Employees page. On the action bar, press New Employees. Note that the default value for Reports To field is correctly displayed.

Default value for Reports To has been populated and displays correctly.

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.
Continue to Cascading Lookups