• Home
• Tutorials
• Forums
• Reference
• Developers
• Advertise
• About Us

Multiple related select boxes through ColdFusion and JavaScript

Multiple Related Selects with ColdFusion and JavaScript
By Steve Durette

Coworkers come to me for ideas on how to do different things in SQL or ColdFusion all of the time. I think that it has something to do with the fact that I love the work I do and therefore I am always reading about it.

This time when one of them came to me, his problem was, ?I have some select boxes that are related and I need to know the best way to do it?. My first thought was the two selects related custom tag, and then he hit me with the twists. 

First, there were three select boxes that were related and second, there could be multiple sets of these selects. I started thinking that I had heard of a three selects related, but at the same time I thought that in the last few months many people had come to me with related select questions that I redirected them to the custom tags. So then I thought that maybe I should investigate how this could be done.

My first stop was to easycfm.com of course! I found three articles relating to this subject. They were:
· The easiest method for multiple dynamic and dependant drop down lists, period by Ryan M. BeMiller
· Multiple dynamic drop-down selection boxes by Mike Corbridge
· In search of Dynamic Dependant Lists by Megan Garrison

These were all great articles on the subject, but they didn?t quite work for what I needed. Ryan?s solution (one that I use quite often) didn?t work because it keeps resubmitting the page to itself. This is great in most instances and was tried by my coworker but didn?t fit our needs, as you will read below. Mike?s and Megan?s solutions helped get me started but they still weren?t quite dynamic enough, using them we would have had to write multiple JavaScript functions to deal with all of the related select boxes.

So I decided to create my own solution for a multi-related select situation that could be repeated multiple times on a page without duplication of the JavaScript that creates it. In this example we will deal with three related select boxes for state, city and address. Some basic ColdFusion and JavaScript knowledge is needed for this article, but I?m hoping that it is written so that most can use it.

The original solution that my co-worker had come up with was to cause the select box to submit the page back to itself every time a change was made (The same as Ryan?s solution). This can be a great tool, but with multiple selects and multiple instances of these selects on the page, the queries and the amount of data that had to keep being passed to the page caused it to slow down.

I knew that we would have a query to get the information; we would need the data from the query moved into JavaScript variables and the JavaScript to change the selects. 

To make things easier, there should only be one query that gets us all of the information we need. We could probably use multiple queries, but I wanted to keep this simple. For our test we were using a MSSQL 2000 table with the information that could have multiple records with the same city, state and address (it was tied to an employee table). The query we came up with was:

<cfquery name=?qryLocationInfo? datasource=?#ourDS#?>
    select distinct city, state, address
    from employee_table
    group by state, city, address order by state, city, address
</cfquery>

The distinct along with the group by is probably a little bit of overkill, but I needed to make sure that the returned table was only as big as it absolutely needed to be.

Ok, so we now have the data, it?s time to get it into JavaScript so that we can use it to populate the selects. For what we were about to do I decided to use an array of arrays. 

<script language=?JavaScript?>
   <!--
        //create the initial array to hold the different records.
        var aryLocation = new Array();
        //Now to bring in the data. This is a combination of 
        //ColdFusion and JavaScript
        <!--- We set a variable for our loop to insert the data. The loop starts at zero because JavaScript arrays start at zero. --->
        <cfset Variables.JSLoop = 0>
        <cfoutput>
            //The next lines create an array of 3 items in the
            //outer array. It could hold more than three items
            //if we needed more than 3 selects related.
            <cfloop query=?qryLocationInfo?>
                aryLocation[#Variables.JSLoop#] = new Array(?#qryLocationInfo.state#?, ?#qryLocationInfo.city#?, ?#qryLocationInfo.address#?);
            <cfset Variables.JSLoop = Variables.JSLoop + 1>
            </cfloop>
        </cfoutput>

The array is declared and populated outside of any functions. This will make it available for any other functions that we may need to create in the future, plus we will only take the time to set it when the page is initially loaded. Not every time the function is called.

Next step is to create the function that will update the selects. As you may have noticed in the previous code block, the script tag was never closed. That is because the function will be created in the same JavaScript code block.

        function tsrUpdSelects(frstSel, scndSel, thrdSel, thisSel) {
           //This function takes four arguments.

           /*
                 They will be the frstSel (the state select box), the scndSel (the city select box),
                 the thrdSel (the address select box) and thisSel (the select box that
                  actually called for the change to occur).
           */

            //local variables
            var i; var chkCty = ??;

            //see if we just changed the first select box (state).
            if(thisSelect.name == frstSelect.name) {
                 //set the length of the other selects to zero to empty them
                 scndSel.options.length = 0;
                 thrdSel.options.length = 0;

            //set the first option for each (being messages to do a select).
                 scndSel.options[scndSel.length] = new Option(?Choose City?, ??);
                 thrdSel.options[thrdSel.length] = new Option(?Choose Address?, ??);

            //Now loop through and set the second option list (city).
            //If a state was chosen. There is also code to prevent duplicate
            //cities. This could happen if a city had multiple addresses.
                if(thisSel.options[thisSel.selectedIndex].value != ??) {
                     for (i = 0; i < aryLocation.length; i++) {
                             if(aryLocation[I][0] == thisSel.options[thisSel.selectedIndex].value && chkCty.lastIndexOf(aryLocation[i][1] == -1) { scndSel.options[scndSel.length] = new Option(aryLocation[i][1], aryLocation[i][1]); chkCty = chkCty + ?,? + aryLocation[i][1];
                             }
                     }
                 }
             }
             //see if we just changed the second select box(city).
                if(thisSel.name == scndSel.name) {
                     //set the length of the third select to zero.
                        thrdSel.options.length = 0;
                        //set the first option.
                            thrdSel.options[thrdSel.length] = new Option(?Choose Address?, ??);
                            //set the rest of the values if a city was chosen.
                                for(i=0; i < aryLocation.length; i++) {
                                    if(aryLocation[i][0] == frstSel.options[frstSel.selectedIndex].value && aryLocation[i][1] == scndSel.options[scndSel.selectedIndex].value) { thrdSel.options[thrdSel.length] = new Option(aryLocation[i][2], aryLocation[i][2])
                                    }
                                 }
         }
    }
//-->
</script>

The function looks kind of large, but if you remove some of the comments and don?t split the lines like I did for this document, then it actually turns out to be quite small. Even though I say that it is quite small, it is still doing a lot of work. It starts with the fact that I built it to receive the three selects as attributes. This ensures that we don?t need to rewrite the function for every set of related selects we want to do. Next, it determines which select did the call to the function. If it was the state, it clears the city and address selects and then populates the city. If it was the city then it clears and populates the address select. If it is the address, we don?t call the function at all. You will also notice that if it is a city it also checks to see if we have already added the current city (in the loop) to the array by using the chkCity variable, this prevents duplicates of the cities.

Now that the JavaScript is complete we can start into the html. We will do 5 sets of selects for testing.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
 <title>Multiple three selects related</title>
 <!-- Place the JavaScript code here. -->
</head>
<body>

<cfoutput>
  <form method=?post? name=?testForm?>
  <table>
   <tr>
    <th>State</th>
    <th>City</th>
    <th>Address</th>
   </tr>
   <cfloop from=?1? to=?5? index=?Variables.myLoop?>
   <tr>
     <td>
     <cfset stateList = ??>
    <select name=?myState#myLoop#? onChange=?tsrUpdSelects(this, testForm.myCity#myLoop#, testForm.myAddr#myLoop#, this);?>
       <option value=?? selected>Choose State</option>
       <cfloop query=?qryLocationInfo?>
        <cfif not listFindNoCase(stateList, qryLocationInfo.state, ?,?)>
        <option value=?#qryLocationInfo.state#?> #qryLocationInfo.state# </option>
        <cfset stateList = listAppend(stateList, qryLocationInfo.state, ?,?)>
        </cfif>
        </cfloop>
     </select>
     </td>
     <td>
     <select name=?myCity#myLoop#? onChange=?tsrUpdSelect(testForm.myState#myLoop#, this, testForm.myAddr#myLoop#, this);?>
      <option value=?? selected>Choose City</option>
      </select>
      </td>
      <td>
        <select name=?myAddr#myLoop#?>
            <option value=?? selected>Choose Address</option>
        </select>
      </td>
    </tr>
    </cfloop>
  </table>
  </form>
  </cfoutput>
</body>
</html>

That completes all of the code. All you have to do is save it to a file (I used tsrtest.cfm). Make sure that you change the query to suit your needs.

All that you have to do to make it do more selects is add extra ifs to the JavaScript and add extra arguments.

Hopefully this document was of some help to you.