How can I create dynamic select boxes in CF? 1

[Muhsin]

Hi,

I am trying to create multiple, dynamic select boxes.
EX:
I have 4 select boxes in succession.
If I select an item in box1, I want to dynamically update the content in box2 based on my selection in box1. Similarly, If I select a value in box2, I want to dynamically update the list in box3, and so on..

Can anyone direct me as how to achieve this? Is it a combination of Javascript and ColdFusion. A simple 2 select box example would suffice.

Thank you.

 

[tlhawkins]

I ran accross this Code somewhere on the Javascript Forum. I wish I could remmember who wrote it so I could attribute it to them but I don't.

This is how they did it. I shortened it and adapted it for CF but it works fine:

</script>
<SCRIPT Language=&quot;JavaScript&quot;>
<!--

function updatesel(form)
{
form.Two.length = 1;
form.Two.selectedIndex = 0;
choice = form.One.options[form.One.selectedIndex].value;
if (choice == &quot;first&quot
{
<CFLOOP index=&quot;x&quot; From=&quot;1&quot; to=&quot;4&quot;>
<CFOUTPUT>
(form.Two.length)++;
form.Two.options[form.Two.length - 1].text = &quot;SubChoice #x#&quot;;
form.Two.options[form.Two.length - 1].value = &quot;SC#x#&quot;;
</CFOUTPUT>
</CFLOOP>
}
if (choice == &quot;second&quot
{
<CFLOOP index=&quot;x&quot; From=&quot;1&quot; to=&quot;6&quot;>
<CFOUTPUT>
(form.Two.length)++;
form.Two.options[form.Two.length - 1].text = &quot;Second SubChoice #x#&quot;;
form.Two.options[form.Two.length - 1].value = &quot;SC#x#&quot;;
</CFOUTPUT>
</CFLOOP>
}
}
//-->
</script>


<FORM METHOD=&quot;POST&quot; ACTION=&quot;look2.cfm&quot; NAME=&quot;myform&quot;>
<SELECT NAME=&quot;One&quot;
OnChange=&quot;updatesel(this.form)&quot;
WIDTH=&quot;250&quot;>
<OPTION VALUE = &quot;%&quot;>All</OPTION>
<OPTION VALUE = &quot;first&quot;>First choice</OPTION>
<OPTION VALUE = &quot;second&quot;>Second choice</OPTION>
</SELECT>
Look
<SELECT NAME=&quot;Two&quot; WIDTH=&quot;250&quot; >
<OPTION VALUE = &quot;%&quot;>All</OPTION>
<CFLOOP index=&quot;x&quot; From=&quot;1&quot; to=&quot;6&quot;>
<CFOUTPUT>
<OPTION VALUE = &quot;&quot;>000000000000000000000000</OPTION>
</CFOUTPUT>
</CFLOOP>
</select>
</FORM>

The Script is clear enough , IF choice = this then change form.Two.options = this
form.Two.value = this

The thing is that you have to have all the information available when the page is served because you can't requery the Database in a Javascript without updating the page. So you need to gather all the information before hand and just make the <options> change.
Depending on how Dynamic it all is that might not be so easy. But that should get you started.

Have fun.

 

[Muhsin]

Thanks alot tlhawkins.

One question, can the contents of the drop down list be dynamically retrieved fromt the database?

I mean that if I select a value from select box 1, can it query the database and get a unique list of values for select box 2?

Any input would be greatly appreciated.

 

[tlhawkins]

Yes,
Let's walk through it:

<FORM METHOD=&quot;POST&quot; ACTION=&quot;look2.cfm&quot; NAME=&quot;myform&quot;>
<SELECT NAME=&quot;One&quot; 
        OnChange=&quot;updatesel(this.form)&quot; 
        WIDTH=&quot;250&quot;>
  <OPTION VALUE = &quot;%&quot;>All</OPTION>
  <OPTION VALUE = &quot;first&quot;>First choice</OPTION>
  <OPTION VALUE = &quot;second&quot;>Second choice</OPTION>
</SELECT>
[code]
This part hard codes the options for the first list as &quot;First Choice&quot; and &quot;Second Choice&quot;.  You could just as easily do this:
[code]
<SELECT NAME=&quot;One&quot; 
        OnChange=&quot;updatesel(this.form)&quot; 
        WIDTH=&quot;250&quot;>
  <OPTION VALUE = &quot;%&quot;>All</OPTION> 
  <CFOUTPUT Query=&quot;FirstOptions&quot;>
    <OPTION VALUE = &quot;#ID#&quot;>#OptionName#</OPTION>
  </CFOUTPUT>  
</SELECT>
[code]
This is assuming you have a query named FirstOptions with fields named ID and OptionName.  

In this Case your updatesel() function would look like this:

[code]
</script>
  <SCRIPT Language=&quot;JavaScript&quot;>
  <!--

    function updatesel(form)
    {
      form.Two.length = 1;
      form.Two.selectedIndex = 0;
      choice = form.One.options[form.One.selectedIndex].value;
<CFLOOP Index=&quot;count&quot; from=&quot;1&quot; to=&quot;#FirstOptions.recordcount#&quot;>
<CFOUTPUT>
 if (choice == &quot;#FirstOptions.ID[count]#&quot;)
 {
   <CFLOOP index=&quot;x&quot; From=&quot;1&quot; to=&quot;#Evaluate('Opt#count#.recordcount')#&quot;>
   
     (form.Two.length)++;
     form.Two.options[form.Two.length - 1].text   = &quot;#Evaluate('Opt#count#.OptText[x]')#&quot;;
     form.Two.options[form.Two.length - 1].value = &quot;#Evaluate('Opt#count#.OptID[x]')#&quot;;
  
   </CFLOOP>
 }
</CFOUTPUT>
</CFLOOP>
}
//-->
</script>
[code]

So this Loops through your First Query named &quot;FirstOptions&quot; and then Loops through Queryies that have been made from those options.  This Requires some tricky Querying and depends totaly on your database.  This is how I did it and how it will work with this code:

[code]
<CFQUERY name=&quot;FirstOptions&quot; Datasource=&quot;MYDS&quot;>
  Select *
  From tblFirstOptions
</CFQUERY>
<CFLOOP index=&quot;x&quot; From = &quot;1&quot; to = &quot;#FirstOptions.recordcount#&quot;>
  <CFQUERY name=&quot;Opt#x#&quot; datasource=&quot;MYDS&quot;>
    Select *
    From tblSecondOptions
    Where OptID = #FirstOptions.ID[x]#
  </CFQUERY>
</CFLOOP>
[code]
You now have #FirstOptions.recordcount# Queries named:
Opt1
Opt2
Opt3 ... and so on.

Those Queries are used in the <CFLOOP>s that power the second DropDown box.

This is a little confusing but if you get the concept you will be able to do it with your setup.  The main thing for this to work is you need a Linked Field between your two Queries.  Like this:

tblFirstOptions
  ID
  OptName

tblSecondOptions
  ID
  FirstOptID
  OptText
.
I'm going crazy trying to explain this.  Let me know if you get it.  IF not, post your Database structure and what you're looking for and I'll see if I can make that work.  I know this works because I did it with some live data on my database while testing this code and it worked good.  In fact I think you've talked me into using it (now that I've written it)   :)

Hope this helps.

 

[CFHub]

Nice work tlhawkings

(I really liked the &quot;remove duplicate function too)


I just thought I would mention that there are a couple of tags at Allaire that work very well... CF_TwoSelectsRelated and (for the truly imaginative) CF_ThreeSelectsRelated.

Both Database powered and very customizable.

 

[Muhsin]

This helps a lot.
Thank you very much.
I really appreciate it.