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, October 23, 2012PrintSubscribe
Cascading Lookups

The picture below shows the schema for a sample web application. The Cars database stores several makes of cars, which each have several models. Each model has various styles.

Cars database diagram. Make, model, and style foreign key fields are present to the respective tables.

When a user creates a car, they must select a make, a model, and a style in that order.  The default web application generated from this database will allow the user to select any make, model, or style at any time. The preferred behavior would be to cascade each lookup selection as a filter to the next lookup. When a user changes a master lookup, all child lookups should be cleared.

The script to create the database is attached at the end of this article.

Implementing a Cascading Lookup

Start the Project Designer. In the Project Explorer, switch to the Controllers tab. Double-click on Cars / Fields / MakeID* (Guid) –> Make field node.

Selecting the MakeID field of Cars controller in the Project Designer.

Change the following properties. The Copy property will clear the dependent lookups when the value of this field changes.

Property New Value
Copy

ModelID = null
ModelModelName = null
StyleID = null
StyleStyleName = null

Press OK to save. Double-click on Cars / Fields / ModelID*(Guid) –> Model field node.

ModelID field node of Cars controller.

Change the following properties. The Context Fields property will pass a filter to the lookup.

Property New Value
Context Fields MakeID=MakeID
Copy

StyleID = null
StyleStyleName = null

Press OK to save. Double-click on Cars / Fields / StyleID* (Guid) –> Style field node.

StyleID field of Cars data controller.

Change the following:

Property New Value
Context Fields ModelID=ModelID

Save the field. On the toolbar, press Browse.

Navigate to the Cars page and create a new record. Activate the lookup for Make Name. Select one of the choices.

Selecting a lookup value for make.

Activate the lookup for Model Name. The list of models has been filtered by the previous lookup selection.

Selecting a lookup value for model.

The same filtering will occur on the next lookup.

Selecting a lookup value for style.

Change the make or model. The lookups under the changed value will be cleared.

Changing the make will clear the model and style.

Script for Cars Database

Paste the script below in SQL Server Management Studio and press Execute to create the database.

CREATE DATABASE [CarsExample]
GO
USE [CarsExample]
GO

CREATE TABLE [dbo].[Cars](
    [CarID] [uniqueidentifier] NOT NULL PRIMARY KEY DEFAULT (newid()),
    [MakeID] [uniqueidentifier] NOT NULL,
    [ModelID] [uniqueidentifier] NOT NULL,
    [StyleID] [uniqueidentifier] NOT NULL)
GO
CREATE TABLE [dbo].[Make](
    [MakeID] [uniqueidentifier] NOT NULL PRIMARY KEY DEFAULT (newid()),
    [MakeName] [nvarchar](50) NOT NULL)
GO
CREATE TABLE [dbo].[Model](
    [ModelID] [uniqueidentifier] NOT NULL PRIMARY KEY DEFAULT (newid()),
    [ModelName] [nvarchar](50) NOT NULL,
    [MakeID] [uniqueidentifier] NOT NULL)
GO
CREATE TABLE [dbo].[Style](
    [StyleID] [uniqueidentifier] NOT NULL PRIMARY KEY DEFAULT (newid()),
    [StyleName] [nvarchar](50) NOT NULL,
    [ModelID] [uniqueidentifier] NOT NULL)
GO

ALTER TABLE [dbo].[Cars]  WITH CHECK ADD  CONSTRAINT [FK_Cars_Make] FOREIGN KEY([MakeID])
REFERENCES [dbo].[Make] ([MakeID])
GO
ALTER TABLE [dbo].[Cars]  WITH CHECK ADD  CONSTRAINT [FK_Cars_Model] FOREIGN KEY([ModelID])
REFERENCES [dbo].[Model] ([ModelID])
GO
ALTER TABLE [dbo].[Cars]  WITH CHECK ADD  CONSTRAINT [FK_Cars_Style] FOREIGN KEY([StyleID])
REFERENCES [dbo].[Style] ([StyleID])
GO
ALTER TABLE [dbo].[Model]  WITH CHECK ADD  CONSTRAINT [FK_Model_Make] FOREIGN KEY([MakeID])
REFERENCES [dbo].[Make] ([MakeID])
GO
ALTER TABLE [dbo].[Style]  WITH CHECK ADD  CONSTRAINT [FK_Style_Model] FOREIGN KEY([ModelID])
REFERENCES [dbo].[Model] ([ModelID])
GO
Tuesday, October 23, 2012PrintSubscribe
Order Form Sample–Part 16

Let’s make similar changes to the Order Details controller. We’ll make the ProductID lookup open in search mode upon creating a new order detail. When selecting a product, the Unit Price will also copy over into the order detail.

On the Controllers tab of the Project Explorer, double-click on OrderDetails / Fields / ProductID (Int32) –> Products field node.

ProductID field in OrderDetails controller in the Project Explorer.

Change the following settings:

Property New Value
Data Value Field ProductID
Data Text Field ProductName
Copy UnitPrice=UnitPrice
Search on Start True
Activate if Blank True
Lookup window description Select a product.

Press OK to save the field, and press Browse in the top left corner.

Navigate to the Order Form page, and select any order. Create a new order detail record. A lookup will open prompting you to select a product.

When creating a new order detail, the product lookup opens in search mode.

Select a product, and you will see that Unit Price of the product has been copied into the order detail.

When a product is selected, the Unit Price of the product is also copied over.

Saturday, September 29, 2012PrintSubscribe
Rows of List Box

The List Box allows lookup value selection from a scrolling box with a list of values. For example, the Employee Last Name field in Orders edit form has been configured as a List Box.

Employee Last Name lookup field configured as a List Box with 5 rows.

By default, there are 5 options displayed in the List Box at one time. This number can be changed using the Rows property.

Start the Project Designer. In the Project Explorer, switch to the Controllers tab and double-click on Orders / Views / editForm1 / c1 – Orders / EmployeeID node.

EmployeeID data field node in view 'editForm1' of Orders controller in the Project Explorer.

Change the Rows property:

Property New Value
Rows 8

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

Navigate to the Orders page, and edit a record. The Employee Last Name list box is now longer and displays more items at one time.

Employee Last Name lookup field configured as a List Box with 8 rows.