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.

<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>