Combo Box Magic

1
Combo Box Magic!

• Dynamic Combo Box
• Design and Implementation
• Paul Dormody
• 651-208-9600
• dorms_at_inslink.com

2
The Database Backbone

• The first step is to create 2 tables
• LookupType
• ID int 4 0
• cbName varchar 50 1
• Lookups
• ID int 4 0
• abbrev varchar 20 1
• Description varchar 50 1
• lookUpTypeID int 4 1
• sortOrder tinyint 1 1

3
Database Erata

• Each LookupType name identifies a distinct combo
  box
• Make sure that each cbName is unique and
  descriptive of the combo box that it represents
• Create a one to many relationship between the ID
  field in the LookUpType table and the
  LookuptypeID field in the Lookups table
• With this backbone, you can easily create and/or
  edit any number of line items for each combo box
  used in the application.

4
For Example

• Create a LookupType record
• Call the first record states
• This record will have an ID 1 and a cbName
  value of states
• In the Lookups table
• Create 50 records
• Each record will have an ID, a state
  abbreviation, a state name, a lookupTypeID (in
  this case 1), and a sortorder number. In this
  example, the lookupTypeID will be 1 for each of
  the 50 states.

5
Advantages

• Create and populate unlimited combo boxes with
  only 2 tables
• Easy combo box administration
• Load the lookups table during login and cache the
  query within, possibly to refresh every 15 to 30
  minutes
• Put the query into application or request scope
• ltcfquery datasource"AppDSN"
  username"appUName" password"appPass"
  name"request.qlookups" cachedwithin"CreateTime
  Span(0,0,15,0)"gt
• Select From lookups
• lt/cfquerygt
• Now use a query of queries to populate each combo
  box

6
The fun part

• Information to build all combo boxes now resides
  in memory
• This results in minimal database load
• Forms, even very large forms with many combo
  boxes, load very quickly
• Build your combo boxes
• Run the following type of query at the top of
  the page
• ltcfquery name"getStates" dbtype"query"gt
• Select ID, description FROM request.qlookups
  WHERE lookupTypeID 1
• order by sortOrder
• lt/cfquerygt

7
Combo Box Code

• Each combo box is built using code similar to the
  following
• ltselect name "stateLUID" class"default"
  tabindexif needed"gt
• ltcfoutput query "getStates"gt
• ltoption value "ID"ltcfif NOT
  CompareNoCase(ID, "getStates.stateLUID")gtselecte
  dlt/cfifgtgtdescription
• lt/optiongt
• lt/cfoutputgt
• lt/selectgt

8
Application Demonstration!

• The InsCMS Insurance Client Management System
• The Homeowner Info Form
• 1. An ASP application designed for multiple
  brokers/users
• 2. Many combo boxes on the form
• 3. Users may often request additional combo box
  line items
• Administration
• View of the dynamic combo box admin form

9
Its a Wrap!

• This technique demonstrates an elegant way to
  manage combo boxes
• Main points
• 1. Straightforward database implementation
• 2. Effective use of query caching
• 3. Use of request or application scope
• 4. Use of query of queries for combo box
  population
• 5. Easily manageable application wide combo box
  administration