Context-Sensitive Data 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
Saturday, November 1, 2008PrintSubscribe
Context-Sensitive Data Lookups

Data Aquarium Framework now supports context-sensitive lookups.

A common situation in a data entry application may require limiting the possible foreign key values of fields based on other fields in a data table row.

Consider Northwind database table Products. It stores a product inventory. Each product has a category and a supplier. If you where to build a data entry screen for this table then you may soon realize that it would be nice to limit the number of categories displayed to a user based on a supplier selection. Suppliers typically offer a few categories of products and it may improve data entry efficiency if we were to automatically hide categories that do not have any products that were previously purchased from a supplier.

Here is a picture of a product entry screen automatically created by Code OnTime Generator.

image

Let's implement a context-sensitive category lookup.

We have to create a database view to allow us implementation of category lookup discussed above. Please execute the following script to create view SupplierCategories in the Northwind database.

create view [dbo].[SupplierCategories]
as
select distinct
    "Categories"."CategoryID" "CategoryID"
    ,"Categories"."CategoryName" "CategoryName"
    ,"Suppliers"."SupplierID" "SupplierID"
    ,"Suppliers"."CompanyName" "SupplierCompanyName"
from
"dbo"."Categories" "Categories"
  inner join "dbo"."Products" "Products" on
    "Products"."CategoryID" = "Categories"."CategoryID"
  inner join "dbo"."Suppliers" "Suppliers" on
    "Suppliers"."SupplierID" = "Products"."SupplierID"

The view provide us with all product categories provided by our suppliers.

image

Next we will create a new data controller descriptor SuppliersCategories.xml as a copy of Categories.xml.

Change command command1 of new data controller descriptor as shown in this snippet.

    <command id="command1" type="Text">
      <text>
        <![CDATA[
select
    "Categories"."CategoryID" "CategoryID"
    ,"Categories"."CategoryName" "CategoryName"
    ,"Categories"."Description" "Description"
    ,"SupplierCategories"."SupplierID" "SupplierID"
    ,"SupplierCategories"."SupplierCompanyName" "SupplierCompanyName"
from "dbo"."Categories" "Categories"
  inner join "dbo"."SupplierCategories" "SupplierCategories" on
    "SupplierCategories"."CategoryID" = "Categories"."CategoryID"
]]>
      </text>
    </command>

Change fields element to provide definitions for fields SupplierID and SupplierCompanyName.

<fields>
  <field name="CategoryID" type="Int32" allowNulls="false" isPrimaryKey="true" label="Category#" readOnly="true" />
  <field name="CategoryName" type="String" allowNulls="false" label="Category Name" />
  <field name="Description" type="String" allowQBE="false" allowSorting="false" label="Description" />
  <field name="SupplierID" type="Int32" label="Supplier#">
  <field name="SupplierCompanyName" type="String" label="Supplier Company Name"/>
</fields>

Change view grid1 to include field SupplierID under alias SupplierCompanyName.

<dataFields>
  <dataField fieldName="CategoryName" columns="15" />
  <dataField fieldName="Description" rows="5" />
  <dataField fieldName="SupplierID" aliasFieldName="SupplierCompanyName"/>
</dataFields>

The purpose of these changes is to allow filtering by SupplierID when grid view grid1 is presented to a user.

Now we are ready to modify the data controller descriptor Products.xml to use SupplierCategories instead of Categories in the lookup definition of field CategoryID.

<field name="CategoryID" type="Int32" label="Category#" contextFields="SupplierID">
  <items style="Lookup" dataController="SupplierCategories" newDataView="createForm1" />
</field>

We have replaced data controller reference from Categories to SupplierCategories.

Another important change is a new attribute contextFields. This attribute specifies a comma-separated list of fields defined in this data controller descriptor that will provide the source of filter values for the fields in the view of SupplierCategories data controller that have matching names when a lookup is displayed. The filter is automatically set as an external, which hides the filter field from end-user. SupplierID is never presented by is used in filtering.

Here is how it looks in a real application. Only two categories of products are available from supplier Exotic Liquids.

image

Let's select supplier Plutzer Lebensmittelgroßmärkte AG and check out the available categories. Five categories are available for selection.

image

Context-sensitive lookups are available to premium project subscribers.