Transcript Combo Box Magic!

Combo Box Magic!
Dynamic Combo Box
Design and Implementation
Paul Dormody
651-208-9600
[email protected]
The Database Backbone
• The first step is to create 2 tables –
– LookupType
• ID
• cbName
int
4
varchar 50
0
1
int
4
varchar 20
varchar 50
4
1
tinyint 1
0
1
1
– Lookups
•
•
•
•
•
ID
abbrev
Description
lookUpTypeIDint
sortOrder
1
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.
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.
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:
<cfquery datasource="#AppDSN#" username="#appUName#" password="#appPass#"
name="request.qlookups" cachedwithin="#CreateTimeSpan(0,0,15,0)#">
Select * From lookups
</cfquery>
– Now use a query of queries to populate each combo box
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:
• <cfquery name="getStates" dbtype="query">
•
Select ID, description FROM request.qlookups WHERE lookupTypeID = 1
•
order by sortOrder
• </cfquery>
Combo Box Code
• Each combo box is built using code similar to the following:
•
•
•
•
•
•
<select name = "stateLUID" class="default" tabindex=“[if needed]">
<cfoutput query = "getStates">
<option value = "#ID#"<cfif NOT CompareNoCase(ID,
"#getStates.stateLUID#")>selected</cfif>>#description#
</option>
</cfoutput>
</select>
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
It’s a Wrap!
• This technique demonstrates an elegant way to
manage combo boxes
• Main points:
–
–
–
–
–
1.
2.
3.
4.
5.
Straightforward database implementation
Effective use of query caching
Use of request or application scope
Use of query of queries for combo box population
Easily manageable application wide combo box administration