Database Lookups

Database Lookups
Sunday, July 2, 2017PrintSubscribe
Advanced Search 3.0, Integrated Deployment, Enhanced Baskets & Lookups

Release is here! While the revision number is minor, some major features are contained in this release.
First on the headline is brand new Advanced Search 3.0 for Touch UI. Major upgrades were required for the Form Rendering Engine to support dynamically created forms. Survey capabilities have been greatly expanded in order to support the functionality required for Advanced Search. 
One important aspect of every app is dealing with deployment. The new Publish capabilities introduced in make it easy to get your apps running on the cloud or a dedicated server. Read on below for more information.
A large number of other enhancements and fixes are also included in this release.

Advanced Search 3.0

The original implementation of Advanced Search for Touch UI was a custom solution. The introduction of modal pages in release necessitated a rewrite of the functionality. The new implementation in release brings integration with the latest features available in Touch UI - lookups, basket lookups, date inputs, modal forms, surveys, and custom form templates.
The new default layout for Advanced Search will read a story to the user. Use Tab, Enter, or arrow keys to quickly navigate through field values. Push Enter key to perform the Search.
Advanced Search 3.0 now correctly handles typed inputs. A date picker will be displayed for date fields. Lookups are used for every field to allow the user to select existing values.
For fields in a “Match All” group, the lookups will be cross-dependent on each other and apply filters to available values based on the current selection. This greatly helps the user explore and understand the dataset without having to perform searches.

Integrated Publishing/Deployment

A major step of every app is to deploy it to the web. Release makes it easy to bring the app to your users with the push of a button.
The “Publish” action in previous releases would simply compile the app and open the target folder. In the new release, the action will now display the list of options below.

The “File System” option allows publishing directly a folder in the file system.
The “FTP” option allows publishing the app directly to an FTP-enabled server.

Publishing to Microsoft Azure is covered in great details in the brand new tutorials at

Integrated publishing to the cloud if the foundation of the mobile application deployment in the upcoming release  Only Code On Time will allow creating a server-side application that becomes automatically mobile and capable of working entirely offline in the release Consult our roadmap for more details at

Each Publish option offers the ability to specify overrides for app settings that will be applied when Publish is complete. Here are some of the options:

  • Primary and membership connection strings
  • Enabling remote debug messages
  • Blob adapter configurations

Support for Microsoft Visual Studio 2017

The app generator now fully supports Visual Studio 2017.

Rich Text Format

Support for rich text has been now introduced in the grid, list, and cards presentation styles. Forms display a keyboard freindly rich text editor. We will be making a few enhancements in the follow-up releases (toolbar, formattings options, etc.) in the coming weeks.

Identity Server OAuth Provider

You can now use Identity Server 4 for the purpose of user authentication based on Microsoft Identity technology.

Microsoft Graph OAuth Provider (Windows Live, Outlook, Office, SharePoint, Azure AD)

This releases also introduces new options that allow authentication of users via Microsoft Azure Active Directory. We are preparing video tutorials that will explain how that works.

Additional Enhancements

  • Baskets are now displaying the selected items inline with the text input for a more compact presentation. Selected options are eliminated from the list of options available for selection.
  • Powerful client-side caching in database lookups brings performance of applications to a new level.
  • Changing models will no longer rebuild data model from the database - much faster for users using remote database servers.
  • Project backups are now zipped, reducing Backup folder size by ~90%.
  • Published projects are now backed up and zipped.
  • New ServiceRequestHandler class allows extending “_invoke” API with new custom handlers.
    Web.config modification instructions now support “SetAttribute” command. See example below:
        SetAttribute: /configuration/system.web/pages
  • Azure Blob Adapter updated to use API version 2015-12-11.
  • Sitemaps defined in CMS now support “CSS Class” property.
  • It is now possible to control default modal behavior using touch-settings.json file using “ui.modal.max” and “ui.modal.when” properties.. See example below:
      "ui": {
        "modal": {
          "max": "lg",
          "when": "sm"
  • Added ability to set thumbnail size in touch-settings.json using the “ui.thumbnail.width” and “ui.thumbnail.height” properties.
  • New icons in the app generator.
  • Warning shown under connection string when it differs from the membership connection string.
  • Only one connection string is created in web.config if primary and membership connection string are equal.
  • Updated translations for Portuguese - thank you Nielsen Batista!
  • Updated translations for German - thank you Peter Teutsch!
  • Added tag “lookup-collapsible” to automatically collapse ListBox and RadioButtonList controls when the user makes a selection. A  chevron is displayed to expand the lookup again.
  • Custom button support in surveys.
  • ExportBase.ToClientUrl() is now overridable to allow customization of IQY files.
  • Calendar Input will focus next field after the date is selected on DateTime fields on desktop devices.
  • Basket lookups will hide values that have already been selected.
  • Lookup controls will expand faster to ensure text does not scroll as the user types.
  • Enhanced support for conversion of strings to date values. For example, type in “011215” to get Jan 12, 2015.
  • Close button added to Calendar Input when mouse is primary pointer.
  • Blob Adapter values are stored in the web.config as app settings.
  • Many-to-many field processing is moved before “After” business rules, and after “Before” business rules, to ensure rules use the correct values.
  • Tag “open-on-tap” will open a lookup dropdown instead of focusing on text input.
  • Tag “lookup-distinct” allows reducing the available lookup options to distinct values.
  • Custom JavaScript files will now be read and appended to the framework when placed under ~/js folder. ApplicationServices.ConfigureScripts() allows controlling which scripts are included.
  • Custom Cascading Stylesheet files (CSS) will be read and appended to the library when placed under ~/css folder.
  • Surveys are now loaded from ~/js/surveys folder. When using survey called “mysurvey”, API will pick up files in this order:
  • Survey definition: mysurvey.min.js, mysurvey.js
  • Survey rules: mysurvey.rules.min.js, mysurvey.rules.js
  • Survey template: mysurvey.html

Miscellaneous Fixes:

  • Fixed dedicated login redirect issue with projects using ASPX page implementation.
  • Fixed issue “Error 500: Dangerous request in form” when a form is submitted with HTML formatted values.
  • Fixed issue with Membership Manager not updating LoweredRoleName column.
  • Fixes with page sizing after device rotation.
  • Custom Membership supports optional PasswordQuestion/Answer.
  • Charts are now supported with custom controllers.
  • Blob Adapters with Source Field value containing spaces is now supported.
  • MyProfileBusinessRules properly inherits from SharedBusinessRules when the feature is enabled.
  • Fixed “Unable to get property ‘1’ of undefined” error in Project Designer.
  • Disabled discard changes prompt in MyProfile controller.
  • Fixed issue with Model class objects using Turkish “i” in field names.
  • Export action ignores DataView fields.
  • Tag “action-call-disabled” now works.
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.

Continue to Items Data Controller