I saw some tutorials on many sites which show how to develop related select boxes using ColdFusion and JavaScript. Many of them were titles "Multiple Select Boxes" which was a bit un-appropriate as they did not really show how many levels of hierarchy they would cover. One of them which really inspired me was here itself on EasyCFM.com. You can see it at multiple related select boxes through ColdFusion and JavaScript (By Steve Durette). It looked good to me and the code really gave me some good ideas though to be honest I’m not great at JavaScript. But what I wanted was something simple which would let me re-use the code over and over again without re-visiting it and that’s what smart programming is all about. The drawback I found in this tutorial was that it stopped at 3 levels of hierarchy. What I desired was "n" levels.

Well, somehow I got into a situation where I had to find a multi-level related select solution to please one of my clients. And as they say necessity is the mother of all inventions I came up with some sort of a solution.

This tutorial might seem a bit confusing to new cold fusion developers but they can still copy the complete code shown at the end of the tutorial and that should provide them sufficient information to use this custom tag.

Ok, so let’s start then.
I would take the following query as a simple example. If queries a single flat database table to get the hierarchical data.

<cfquery name="GetLocations" datasource="#dsn#" username="#username#" password="#password#">
   select CountryID,Country, StateID, State, CityID, City, Zip
   from Locations
   group by Country, State, City, Zip
   order by Country, State, City, Zip
</cfquery>

Normally you would derive such information from multiple tables using joins. To make it simple I am using a single table here. Also note here I have not specified any "Distinct" keyword in the query and surely I would have multiple duplicate records for at least Country, State and City. I don’t need to use the "distinct" keyword as the custom tag would automatically handle that on its own. Also if you did not my "Zip" has not "ZipID". Don’t worry it’s not a mistake and I would explain a bit later why I did so.
Now, I would like to use the query above to show 4 select boxes on my screen namely ddlCountryID, ddlState, ddlCity, ddlZip. I have used ddl as a prefix here just to make it clear where I am referencing the drop down list control of HTML forms. Well, now you would call the custom tag as follows:

<form name="Form1">
  <table width="100%">
    <tr>
       <td>Country:</td>
       <td>

       <CF_MultipleRelatedSelects
              SELECTBOXES = "4"
              QUERY="GetLocations"
              FORMNAME = "Form1"
              FIELDNAME1="ddlCountry"
              FIELDNAME2="ddlState"
              FIELDNAME3="ddlCity"
              FIELDNAME4="ddlZip"
              DISPLAY1="Country"
              DISPLAY 2="State"
              DISPLAY 3="City"
              VALUE1="CountryID"
              VALUE2="StateID"
               VALUE3="CityID"
              VALUE4="Zip"
              HTMLAFTER1="<tr><td>State:</td><td>"
              HTMLAFTER2="<tr><td>City:</td><td>"
              HTMLAFTER3="<tr><td>Zip:</td><td>"

     </td>
   </tr>
</table>

</form>

I have only used a partial set of attributes of the custom tag in this example. Here is the complete list.

  1. SELECTBOXES (Required) - This is the total number of select boxes in that you would like to display.
  2. QUERY (Required) - The name of the query you want to use.
  3. FORMNAME (Required) - The name of form which these select boxes will be a part of. Please make sure you enter the case sensitive name.
  4. FIELDNAME1, FIELDNAME2………FIELDNAMEN (Required) - For each of the select boxes you wish to display you would need to specify a name. So if you specify 5 in the SELECTBOXES attribute then you must specify FIELDNAME1, FIELDNAME2, FIELDNAME3, FIELDNAME4 and FIELDNAME5
  5. VALUE1, VALUE2,……. VALUEN (Required) - Just like the fieldnames above you also need to specify a column name from your query which would specify the values for each select box. For e.g. "CountryID" in our code sample specified that the select box ddlCountries would have values from the CountryID column of our query.
  6. SELECTEDVALUE1,SELECTEDVALUE2,…..SELECTEDVALUEN (Optional) - For each of the select boxes you can also optionally specify a selected value.
  7. HTMLAFTER1,HTMLAFTER2,…..HTMLAFTERN (Optional) - This is simply the HTML code you wish to insert after each select box.
  8. FUNCTIONNAME (Optional) - By default the tag would you "Sel" as a JavaScript function name. If you wish to specify a different name you could do that using this attribute.
    Now in the code above I have not specified any "Display4" attribute for the custom tag. This is so as I want to use "Zip" (Column of my query GetLocations) as the value as well as the display for the ddlZip select box.
So that was all about how to use the tag. I think any beginner user with good enough SQL knowledge should be able to implement this tag with the information provided above. Below I have provided the full source code for the custom tag for those of you who know ColdFusion at an advanced level. Please feel free to use this code and modify it to your specific requirements. I would not go in details of how this code works but feel free to email me if you have any questions regarding any part of this code. I do not support this code officially though I would try to answer all queries I receive.

<cfparam name="attributes.Query">
<cfparam name="attributes.SelectBoxes" default=0>
<cfparam name="msg" default="">
<cfparam name="attributes.FunctionName" default="Sel">

<cfif len(attributes.Query) eq 0>
    <CFSET msg= msg & "<br>Please specify a query to be processed.">
</cfif>

<cfif not isnumeric(attributes.SelectBoxes) or attributes.SelectBoxes eq 0>
    <CFSET msg= msg & "<br>Please enter a positive integer for the number of select boxes you want.">
</cfif>

<cfif not isdefined("attributes.FormName")>
<CFSET msg= msg & "<br>Please specify a form name.">
</cfif>

<cfloop from="1" to="#attributes.SelectBoxes#" index="sel">
  <cfif not isdefined("attributes.Value#sel#")>
     <cfset msg=msg & "<br>Please specify the value attribute for each select box. For eg. Value1, Value2 .....till the number of SelectBoxes">
  </cfif>

  <cfif not isdefined("attributes.FieldName#sel#")>
     <cfset msg=msg & "<br>Please specify the form field name attribute for each select box. For eg. FieldName1, FieldName1 .....till the number of SelectBoxes">
  </cfif>
</cfloop>

 

<cfif len(msg) eq 0>
   <!---Tag Code--->
   <cfset qry=#evaluate("caller." & attributes.Query)#>

  <script language="JavaScript">
       var optValArr = new Array();
        <cfset loopCtr = 0>

         <cfoutput query="qry">
optValArr[#loopCtr#] = new Array(<cfloop from="1" to="#attributes.Selectboxes#" index="item">new Array("<cfif isdefined("attributes.Display#item#")>#evaluate(evaluate("attributes.Display" & item))#<cfelse>#evaluate(evaluate("attributes.Value" & item))#</cfif>","#evaluate(evaluate("attributes.Value" & item))#") <cfif item lt attributes.Selectboxes>,</cfif></cfloop>);

        <cfset loopCtr=incrementValue(loopCtr)>
        </cfoutput>

        <cfoutput>

        <cfset selList="">
                <cfloop from="1" to="#attributes.SelectBoxes#" index="item">
                        <cfset selList=ListAppend(selList,"Sel" & item)>
                </cfloop>

       function #attributes.FunctionName#(thisSel,pos,nextSel,subNodes) {
           for (i = 0; i < subNodes.length; i++) {
                      subNodes[i].options.length=0;
                      subNodes[i].options[0]=new Option(" ","");
           }

           if(thisSel.options[thisSel.selectedIndex].value != "") {
                      optPos=1;
                      lastVal="";
                      for (i = 0; i < optValArr.length; i++) {
                                 if(optValArr[i][pos][1]==thisSel.options[thisSel.selectedIndex].value){
                                 if(optValArr[i][eval(pos+1)][1]!=lastVal){
                                            lastVal=optValArr[i][eval(pos+1)][1];
                                            subNodes[0].options[optPos]=new Option(optValArr[i][eval(pos+1)][0],optValArr[i][eval(pos+1)][1]);
                                            optPos++;
                                 }
                      }
           }
    }
}

</cfoutput>
</script>

<cfloop from="1" to="#attributes.SelectBoxes#" index="SelBox">

<cfoutput>
   <select name="#evaluate("attributes.FieldName" & SelBox)#" <cfif SelBox lt attributes.SelectBoxes>onchange="#attributes.FunctionName#(this,#evaluate(SelBox-1)#,this.form.#evaluate("attributes.FieldName" & (SelBox+1))#,new Array(<cfloop from="#(SelBox+1)#" to="#attributes.SelectBoxes#" index="nextSel">this.form.#evaluate("attributes.FieldName" & (nextSel))#<cfif nextSel lt attributes.SelectBoxes>,</cfif></cfloop>));"</cfif>></cfoutput>
<cfif SelBox eq 1>
<option value="">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</option>
<cfoutput query="qry" Group="#evaluate("attributes.Value" & SelBox)#">
    <option value="#evaluate(evaluate("attributes.value" & SelBox))#" <cfif isdefined("attributes.SelectedValue" & SelBox) and evaluate("attributes.SelectedValue" & SelBox) eq evaluate(evaluate("attributes.value" & SelBox))>SELECTED</cfif>><cfif isdefined("attributes.Display#SelBox#")>#evaluate(evaluate("attributes.Display" & SelBox))#<cfelse>#evaluate(evaluate("attributes.Value" & SelBox))#</cfif></option>
</cfoutput>
<cfelse>
<option value="">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</option>

<cfif isdefined("attributes.SelectedValue" & SelBox)>
   <!---Get the values available for this set--->
   <cfoutput query="qry" Group="#evaluate("attributes.Value" & SelBox)#">
      <cfif evaluate("attributes.SelectedValue" & (SelBox-1)) eq evaluate(evaluate("attributes.value" & (SelBox-1)))>
      <option value="#evaluate(evaluate("attributes.value" & SelBox))#" <cfif isdefined("attributes.SelectedValue" & SelBox) and evaluate("attributes.SelectedValue" & SelBox) eq evaluate(evaluate("attributes.value" & SelBox))>SELECTED</cfif>><cfif isdefined("attributes.Display#SelBox#")>#evaluate(evaluate("attributes.Display" & SelBox))#<cfelse>#evaluate(evaluate("attributes.Value" & SelBox))#</cfif></option>
      </cfif>
   </cfoutput>
</cfif>
</cfif>
</select>
<cfoutput>
    <cfif isdefined("attributes.HtmlAfter#SelBox#")>#evaluate("attributes.HtmlAfter" & SelBox)#</cfif></cfoutput>
</cfloop>
<!---tag code ends--->
<cfelse>
    <CFTHROW detail="#msg#" message="Error encountered while processing the CF_MultiRelatedSelects tag.">
</cfif>

About This Tutorial
Author: Saurabh Gulati
Skill Level: Intermediate 
 
 
 
Platforms Tested: CFMX
Total Views: 102,738
Submission Date: February 11, 2004
Last Update Date: June 05, 2009
All Tutorials By This Autor: 1
Discuss This Tutorial
  • The instructions are a little hard to follow, but the tag works fine once you fix a couple of things: In the SQL statement, I had to omit the "group by Country, State, City, Zip" clause to avoid an error from my Access database: "You tried to execute a query that does not include the specified expression 'CountryID' as part of an aggregate function." Also, in the sample CF_MultipleRelatedSelects tag, you need to close up the space in the attributes "DISPLAY 2" and "DISPLAY 3" to avoid a ColdFusion error. With these corrections, the tag ran in ColdFusion 7. I'm going to keep this one in my bag of tricks. Could come in very handy.

  • this tag is AWESOME!! thank you! you have saved me countless hours of trying to figure something out to solve this problem. i just have one question, I would like to be able to use the

    with the HTMLAFTER is that possible??

  • Hi, I get this error msg Please specify the value attribute for each select box. For eg. Value1, Value2 .....till the number of SelectBoxes Please specify the value attribute for each select box. For eg. Value1, Value2 .....till the number of SelectBoxes Please specify the value attribute for each select box. For eg. Value1, Value2 .....till the number of SelectBoxes I have value specified but they are text in the database - does that make a different?? Thanks Jonas

  • Exactly what I was looking for. This has saved me a lot of time and frustration. Great Tutorial!!

  • I have the tag working except for field 3. This field is not displaying values for Field 2, it displays all the values in the query. Values should be: 5 15 But shows: 5 15 10 5 Any ideas on fixing this?

  • ...oh silly me. If you output the value you used for your "FIELDNAME" (eg ddlCountry) it will return the "VALUE" that you attached to it (eg CountryID) ONCE you process the FORM. ...oh such joy. That custom tag is very good - THANKYOU!!!! For sharing it.

  • I'm a newbie and NEW to custom tags. I used this code in CFMX7 and it works fine! My question is: What "variable" should I use to output the SELECTED data? ie if I want to output the selected data in a different part of the page? I guess it will be something like -cfoutput FORM.Selected_Country /cfoutput but I'm just not sure what?

  • This is a FANTASTIC alternative to using javascript! Thank you so much for creating it.

  • I have used ThreeSelectsRelated and twoSelectsrelated and decided to give your tag a try but cannot get it to work in MX 7. Is this a known bug or do you have any suggestions. I have been doing CF since v. 1.5 and have verified the code several times on CF 5.0 so I know the other tags work with the query and setup. thanks g

  • What a great tutorial! Please try and explain your code a little better...it would help the newbies!

Advertisement

Sponsored By...
$39.00 - 50 Minute Deep Tissue Massage Dripping Springs, Texas!