?
Solved

Creating Drop Downs BAsed on Previous Drop Downs

Posted on 2006-05-15
26
Medium Priority
?
173 Views
Last Modified: 2013-12-24
Experts,

I have a few different tables that I need to use and create drop downs on a form.  I need the drop downs to appear based on what they select in a prior drop down.  For instance, on my Cold Fusion form, I have a drop down called 'states', and based on the 'state' that someone selects the next drop down that should appear is 'facilitator'.  Based on the 'facilitator' that is selected a list of 'Coherts' will populate the next drop down list.  Based on the 'cohert' select a list of 'names' should appear, and they would then be able to select their names.  Any help would be appreciated.

Thanks in advance
Nick
0
Comment
Question by:nmarano
  • 16
  • 10
26 Comments
 
LVL 35

Expert Comment

by:mrichmon
ID: 16683677
This is called related drop downs.

The simple solution is to donwload the tag called 2 related selects or three related selects.

2 Related Selects:
http://tinyurl.com/2fa2k

3 Related Selects :
http://tinyurl.com/ys839


The more complex solution (not that hard), but more customizable solution is to write it yourself.

http://www.experts-exchange.com/Web/WebDevSoftware/ColdFusion/Q_21292451.html
0
 
LVL 2

Author Comment

by:nmarano
ID: 16683747
Thanks for the links, I'll give it a shot and let you know.

Thanks
Nick
0
 
LVL 2

Author Comment

by:nmarano
ID: 16690521
mrichmon,

I haven't had a chance to try these out,but hope to do so later today.  I do have a question, is there a benefit for using one or the other solutions you provided above?

Thanks
Nick
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
LVL 35

Expert Comment

by:mrichmon
ID: 16691341
Personally I use the hand written one.  The advantage is that it can be customized and expanded, whereas the 2 related selects or 3 related selects are different tags for each - are less customizable, and not expandable very easily.
0
 
LVL 2

Author Comment

by:nmarano
ID: 16692210
Thanks for the response


Nick
0
 
LVL 2

Author Comment

by:nmarano
ID: 16736071
mrichmon,

I tweaked the code for the links that you provided.  My first select box populates all of the states, but once I select a state in the list, the second select doesn't get populated. I posted my code below, please let me know what I am missing.  

Thanks
Nick

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<cfquery name="Get_states" datasource="aptvre">
SELECT ws_state.statename, ws_state.stateID
FROM ws_state
ORDER BY ws_state.statename
</cfquery>

<cfquery name="Get_Workshops" datasource="aptvre">
SELECT ws_workshop.name, ws_workshop.workshopID, ws_workshop.stateID, ws_workshop.facilitatorID
FROM ws_workshop
ORDER BY ws_workshop.name
</cfquery>

<cfquery name="Get_facilitators" datasource="aptvre">
SELECT ws_facilitator.lastName, ws_facilitator.firstName, ws_facilitator.facilitatorID, ws_facilitator.stateID
FROM ws_facilitator
ORDER BY ws_facilitator.lastName
</cfquery>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Three Selects Related</title>

<script type="text/javascript" language="JavaScript">
<!--
/* Dynamically populate Workshop select box based on seleted stateID and then Facilitators based on workshopID */
// Create an array to hold Workshops and Facilitators
var WorkshopArray = new Array;
var FacilitatorArray = new Array;

// Define a custom Javascript object type to represent a single Workshop
function Workshop(workshopid, stateid, name)
{
     this.workshopid = workshopid;
     this.stateid = stateid;
     this.name = name;
}

// Define a custom Javascript object type to represent a single Facilitator
function Facilitator(facilitatorid, workshopid, lastName)
{
     this.facilitatorid = facilitatorid;
     this.workshopid = workshopid;
     this.lastName = lastName;
}

/* Fill the Workshop select box based on the stateID*/
function fillWorkshops()
{
     // Stop if there is no selected ParentID
     if (document.formWorkshops.stateID.selectedIndex == -1)
     {
          return;
     }
     
     var stateID = document.formWorkshops.stateID.options[document.formWorkshops.stateID.selectedIndex].value;
     
     // Remove all options in the Workshop select box
     document.formWorkshops.WorkshopID.options.length = 0;
     
     // For each item in the Workshoparray ...
     for (var i = 0; i < WorkshopArray.length; i++)
     {
          // If the Workshop's stateIDFK is the same as the currently selected stateID
          if (WorkshopArray[i].stateid == stateID)
          {
               // Put a new option in the Workshop select box
               document.formWorkshops.WorkshopID.options[document.formWorkshops.WorkshopID.options.length] = new Option(WorkshopArray[i].workshop, WorkshopArray[i].workshopid);
          }
     }
}

/* Fill the Facilitators select box based on the WorkshopID*/
function fillFacilitators()
{
     // Stop if there is no selected ParentID
     if (document.formWorkshops.WorkshopID.selectedIndex == -1)
     {
          return;
     }
     
     var WorkshopID = document.formWorkshops.WorkshopID.options[document.formWorkshops.WorkshopID.selectedIndex].value;
     
     // Remove all options in the Facilitators select box
     document.formWorkshops.FacilitatorID.options.length = 0;
     
     // For each item in the Facilitatorarray ...
     for (var i = 0; i < FacilitatorArray.length; i++)
     {
          // If the Facilitator's WorkshopIDFK is the same as the currently selected WorkshopID
          if (FacilitatorArray[i].workshopid == WorkshopID)
          {
               // Put a new option in the Facilitator select box
               document.formWorkshops.FacilitatorID.options[document.formWorkshops.FacilitatorID.options.length] = new Option(FacilitatorArray[i].lastName, FacilitatorArray[i].facilitatorid);
          }
     }
}
-->
</script>
</head>
<body>
<form name="formWorkshops" action="fhome.cfm">
     
     <select name="stateID" onChange="fillWorkshops()" style="width: 200px;">
          <option value="">Select state</option>
          <cfoutput query="Get_states"><option value="#Get_states.stateID#">#Get_states.statename#</option></cfoutput>
     </select>

     <select name="WorkshopID" onChange="fillFacilitators()" style="width: 200px;"></select>

     <select name="FacilitatorID" style="width: 200px;"></select>
</form>
</body>
</html>
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 16736260
What you are missing is the lines of code to actually populate the FacilitatorArray and the WorkshopArray

It should look something like this:

<!--- For each Workshop, append a new Workshop object to the array of Workshops --->
<cfoutput query="GetWorkshops">
     WorkshopArray[WorkshopArray.length] = new Workshop("#JSStringFormat(WorkshopId)#", "#JSStringFormat(StateId)#", "#JSStringFormat(WorkshopName)#");
</cfoutput>

<!--- For each Facilitator, append a new Facilitator object to the array of Facilitators --->
<cfoutput query="GetFacilitators">
     FacilitatorArray[FacilitatorArray.length] = new Facilitator("#JSStringFormat(FacilitatorId)#", "#JSStringFormat(WorkshopId)#", "#JSStringFormat(FacilitatorName)#");
</cfoutput>
0
 
LVL 2

Author Comment

by:nmarano
ID: 16736290
One more stupid question then, where in the code would I stick this?

Thanks
Nick
0
 
LVL 2

Author Comment

by:nmarano
ID: 16736383
OKay,

I might have my relations wrong.  My tables are related as follows

state.stateid =workshop.stateid workshop.facilitatorID=facilitator.facilitatorID

I posted all the code below

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<cfquery name="Get_states" datasource="aptvre">
SELECT ws_state.statename, ws_state.stateID
FROM ws_state
ORDER BY ws_state.statename
</cfquery>

<cfquery name="Get_Workshops" datasource="aptvre">
SELECT ws_workshop.name, ws_workshop.workshopID, ws_workshop.stateID, ws_workshop.facilitatorID
FROM ws_workshop
ORDER BY ws_workshop.name
</cfquery>

<cfquery name="Get_facilitators" datasource="aptvre">
SELECT ws_facilitator.lastName, ws_facilitator.firstName, ws_facilitator.facilitatorID, ws_facilitator.stateID
FROM ws_facilitator
ORDER BY ws_facilitator.lastName
</cfquery>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Three Selects Related</title>
<link rel="stylesheet" type="text/css" href="../../styles/intasc_form.css" />
<script type="text/javascript" language="JavaScript">
<!--
/* Dynamically populate Workshop select box based on seleted stateID and then Facilitators based on workshopID */
// Create an array to hold Workshops and Facilitators
var WorkshopArray = new Array;
var FacilitatorArray = new Array;

// Define a custom Javascript object type to represent a single Workshop
function Workshop(workshopid, stateid, name)
{
     this.workshopid = workshopid;
     this.stateid = stateid;
     this.name = name;
}

// Define a custom Javascript object type to represent a single Facilitator
function Facilitator(facilitatorid, workshopid, lastName)
{
     this.facilitatorid = facilitatorid;
     this.workshopid = workshopid;
     this.lastName = lastName;
}

<!--- For each Workshop, append a new Workshop object to the array of Workshops --->
<cfoutput query="Get_Workshops">
     WorkshopArray[WorkshopArray.length] = new Workshop("#JSStringFormat(WorkshopId)#", "#JSStringFormat(StateId)#", "#JSStringFormat(Name)#");
</cfoutput>

<!--- For each Facilitator, append a new Facilitator object to the array of Facilitators --->
<cfoutput query="Get_Facilitators">
     FacilitatorArray[FacilitatorArray.length] = new Facilitator("#JSStringFormat(FacilitatorId)#", "#JSStringFormat(facilitatorId)#", "#JSStringFormat(lastName)#");
</cfoutput>

/* Fill the Workshop select box based on the stateID*/
function fillWorkshops()
{
     // Stop if there is no selected ParentID
     if (document.formWorkshops.stateID.selectedIndex == -1)
     {
          return;
     }
     
     var stateID = document.formWorkshops.stateID.options[document.formWorkshops.stateID.selectedIndex].value;
     
     // Remove all options in the Workshop select box
     document.formWorkshops.WorkshopID.options.length = 0;
     
     // For each item in the Workshoparray ...
     for (var i = 0; i < WorkshopArray.length; i++)
     {
          // If the Workshop's stateIDFK is the same as the currently selected stateID
          if (WorkshopArray[i].stateid == stateID)
          {
               // Put a new option in the Workshop select box
               document.formWorkshops.WorkshopID.options[document.formWorkshops.WorkshopID.options.length] = new Option(WorkshopArray[i].workshop, WorkshopArray[i].workshopid);
          }
     }
}

/* Fill the Facilitators select box based on the WorkshopID*/
function fillFacilitators()
{
     // Stop if there is no selected ParentID
     if (document.formWorkshops.WorkshopID.selectedIndex == -1)
     {
          return;
     }
     
     var WorkshopID = document.formWorkshops.WorkshopID.options[document.formWorkshops.WorkshopID.selectedIndex].value;
     
     // Remove all options in the Facilitators select box
     document.formWorkshops.FacilitatorID.options.length = 0;
     
     // For each item in the Facilitatorarray ...
     for (var i = 0; i < FacilitatorArray.length; i++)
     {
          // If the Facilitator's WorkshopIDFK is the same as the currently selected WorkshopID
          if (FacilitatorArray[i].workshopid == WorkshopID)
          {
               // Put a new option in the Facilitator select box
               document.formWorkshops.FacilitatorID.options[document.formWorkshops.FacilitatorID.options.length] = new Option(FacilitatorArray[i].lastName, FacilitatorArray[i].facilitatorid);
          }
     }
}
-->
</script>
</link>
</head>

<body>
<center>
  <div id="survey">
    <div id="logo">&nbsp;<a href="http://www.intasc.org"><img src="../../images/form_logo2005.gif" alt="intasc" width="68" height="20" border="0" /></a></div>
<form name="formWorkshops" action="fhome.cfm">
     
     <select name="stateID" onChange="fillWorkshops()" style="width: 120px;">
          <option value="">Select State</option>
          <cfoutput query="Get_states"><option value="#Get_states.stateID#">#Get_states.statename#</option></cfoutput>
     </select>

     <select name="WorkshopID" onChange="fillFacilitators()" style="width: 200px;"></select>

     <select name="FacilitatorID" style="width: 200px;"></select>
</form>
</div>
</body>
</html>
0
 
LVL 2

Author Comment

by:nmarano
ID: 16736773
I made some more changes, but still can't get it to work, as I said in my previous post, I had the relations wrong.  state.stateid=workshop.stateid and workshop.facilitatorid=facilitator.facilitatorid.

Any ideas what I am missing in the code below

Thanks
Nick

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<cfquery name="Get_states" datasource="aptvre">
SELECT ws_state.statename, ws_state.stateID
FROM ws_state
ORDER BY ws_state.statename
</cfquery>

<cfquery name="Get_Workshops" datasource="aptvre">
SELECT ws_workshop.name, ws_workshop.workshopID, ws_workshop.stateID, ws_workshop.facilitatorID
FROM ws_workshop
ORDER BY ws_workshop.name
</cfquery>

<cfquery name="Get_facilitators" datasource="aptvre">
SELECT ws_facilitator.lastName, ws_facilitator.firstName, ws_facilitator.facilitatorID, ws_facilitator.stateID
FROM ws_facilitator
ORDER BY ws_facilitator.lastName
</cfquery>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Three Selects Related</title>
<link rel="stylesheet" type="text/css" href="../../styles/intasc_form.css" />
<script type="text/javascript" language="JavaScript">
<!--
/* Dynamically populate Workshop select box based on seleted stateID and then Facilitators based on facilitatorID */
// Create an array to hold Workshops and Facilitators
var WorkshopArray = new Array;
var FacilitatorArray = new Array;

// Define a custom Javascript object type to represent a single Workshop
function Workshop(workshopid, stateid, name)
{
     this.workshopid = workshopid;
     this.stateid = stateid;
     this.name = name;
}

// Define a custom Javascript object type to represent a single Facilitator
function Facilitator(facilitatorid, lastName)
{
     this.facilitatorid = facilitatorid;
     this.lastName = lastName;
}

<!--- For each Workshop, append a new Workshop object to the array of Workshops --->
<cfoutput query="Get_Workshops">
     WorkshopArray[WorkshopArray.length] = new Workshop("#JSStringFormat(WorkshopId)#", "#JSStringFormat(StateId)#", "#JSStringFormat(Name)#");
</cfoutput>

<!--- For each Facilitator, append a new Facilitator object to the array of Facilitators --->
<cfoutput query="Get_Facilitators">
     FacilitatorArray[FacilitatorArray.length] = new Facilitator("#JSStringFormat(FacilitatorId)#" "#JSStringFormat(lastName)#");
</cfoutput>

/* Fill the Workshop select box based on the stateID*/
function fillWorkshops()
{
     // Stop if there is no selected ParentID
     if (document.formWorkshops.stateID.selectedIndex == -1)
     {
          return;
     }
     
     var stateID = document.formWorkshops.stateID.options[document.formWorkshops.stateID.selectedIndex].value;
     
     // Remove all options in the Workshop select box
     document.formWorkshops.WorkshopID.options.length = 0;
     
     // For each item in the Workshoparray ...
     for (var i = 0; i < WorkshopArray.length; i++)
     {
          // If the Workshop's stateID is the same as the currently selected stateID
          if (WorkshopArray[i].stateid == stateID)
          {
               // Put a new option in the Workshop select box
               document.formWorkshops.WorkshopID.options[document.formWorkshops.WorkshopID.options.length] = new Option(WorkshopArray[i].workshop, WorkshopArray[i].workshopid);
          }
     }
}

/* Fill the Facilitators select box based on the FacilitatorID*/
function fillFacilitators()
{
     // Stop if there is no selected ParentID
     if (document.formWorkshops.FacilitatorID.selectedIndex == -1)
     {
          return;
     }
     
     var facilitatorID = document.formWorkshops.facilitatorID.options[document.formWorkshops.facilitatorID.selectedIndex].value;
     
     // Remove all options in the Facilitators select box
     document.formWorkshops.FacilitatorID.options.length = 0;
     
     // For each item in the Facilitatorarray ...
     for (var i = 0; i < FacilitatorArray.length; i++)
     {
          // If the Facilitator's facilitatorsID is the same as the currently selected FacilitatorID
          if (FacilitatorArray[i].facilitatorid == FacilitatorID)
          {
               // Put a new option in the Facilitator select box
               document.formWorkshops.FacilitatorID.options[document.formWorkshops.FacilitatorID.options.length] = new Option(FacilitatorArray[i].lastName, FacilitatorArray[i].facilitatorid);
          }
     }
}
-->
</script>
</link>
</head>

<body>
<center>
  <div id="survey">
    <div id="logo">&nbsp;<a href="http://www.intasc.org"><img src="../../images/form_logo2005.gif" alt="intasc" width="68" height="20" border="0" /></a></div>
<form name="formWorkshops" action="fhome.cfm">
     
     <select name="stateID" onChange="fillWorkshops()" style="width: 120px;">
          <option value="">Select State</option>
          <cfoutput query="Get_states"><option value="#Get_states.stateID#">#Get_states.statename#</option></cfoutput>
     </select>

     <select name="WorkshopID" onChange="fillFacilitators()" style="width: 200px;"></select>

     <select name="FacilitatorID" style="width: 200px;"></select>
</form>
</div>
</body>
</html>
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 16736842
You need the workshop Id in the facilitator array - so that facilitators can be linked to workshops.

Also document.formWorkshops.WorkshopID.options[document.formWorkshops.WorkshopID.options.length] = new Option(WorkshopArray[i].workshop, WorkshopArray[i].workshopid);
should be

document.formWorkshops.WorkshopID.options[document.formWorkshops.WorkshopID.options.length] = new Option(WorkshopArray[i].name, WorkshopArray[i].workshopid);
0
 
LVL 2

Author Comment

by:nmarano
ID: 16736976
mrichmon,

When I added workshopid to the facilitator Array, I got a Variable WorkshopID is undefined.
Should the facilitatorArray have the workshopID since the facilitator and the workshops are linked by facilitatorID?  My facilitator table doesn't have a workshopID, but my workshop table has a facilitatorID

Thanks again for all the help
Nick
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 16737364
You need to determine the hierarchy.

FIrst a person selects a state.  Then the workshops are based off of state??  Or are the facilitators based off state?

Then you do the next level.

So if you want people to first select a state, then a workshop in that state, then a facilitator in that workshop, then you need to have workshops records have a state ID and facilitators have a workshop Id

So if you want people to first select a state, then a facilitator in that state, then a workshop by that facilitator, then you need to have facilitator records have a state ID and workshops have a facilitator Id
0
 
LVL 2

Author Comment

by:nmarano
ID: 16738283
Correct a person selects a state and the workshop is based off of the state.  Then they select the facilitator off of the workshop.  The way I have the relations, and maybe this is what's wrong, is the state is related to the workshop based off of the stateid the worksjop is then related to the facilitator based on facilitatorID.  So are you saying that my facilitator table MUST have a workshopID?  I believe that's what you're saying by the two scenarios.

Again Thanks for the help and the patience
Nick
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 16738588
Yes.

If you select a facilitator based off of the workshop then you need to tie facilitators to the workshop.

By having a facilitator Id in the workshop table it says to me that there is no choice.  There is only ever one facilitator associated witha workshop. Hence no need for this to even be a drop down.

Hope that helps.
0
 
LVL 2

Author Comment

by:nmarano
ID: 16738685
Unfortunately the faciliatators do teach multiple workshops and their are also workshops with multiple facilitators.  This project is being done in multiple states, so the drop downs, will eliminate the amount of choices that they are able to make based on their state, and then their workshop, and then whichever facilitator they are assigned to.
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 16738790
Yes then really there should not be a facilitator Id in the workshop table.  If I were creating it would look something like this:

StateTable
----------
StateID
StateName


WorkshopTable
-----------------
WorkshopId
WorkshopName
WorkshopDescription
StateId
StartDateTime
EndDateTime
Location


FacilitatorTable
------------------
FacilitatorId
FirstName
LastName

FacilitatorToWorkshopTable
--------------------------------
WorkshopId
FacilitatorId

Then the queries would look like:

<cfquery ... name="GetStates">
SELECT * FROM StateTable
ORDER BY StateName
</cfquery>

<cfquery ... name="GetWorkshops">
SELECT * FROM WorkshopTable
ORDER BY StateId, WorkshopName
</cfquery>

<cfquery ... name="GetFacilitators">
SELECT FacilitatorTable .*, WorkshopId FROM FacilitatorTable
INNER JOIN FacilitatorToWorkshopTable ON FacilitatorTable.FacilitatorId = FacilitatorToWorkshopTable.FacilitatorId
ORDER BY WorkshopId, LastName
</cfquery>

Then the part to generate the javascript arrays:

<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Three Selects Related</title>
<link rel="stylesheet" type="text/css" href="../../styles/intasc_form.css" />
<script type="text/javascript" language="JavaScript">
<!--
/* Dynamically populate Workshop select box based on seleted stateID and then Facilitators based on workshopID */
// Create an array to hold Workshops and Facilitators
var WorkshopArray = new Array;
var FacilitatorArray = new Array;

// Define a custom Javascript object type to represent a single Workshop
function Workshop(workshopid, stateid, name)
{
     this.workshopid = workshopid;
     this.stateid = stateid;
     this.name = name;
}

// Define a custom Javascript object type to represent a single Facilitator
function Facilitator(facilitatorid, workshopid, lastName)
{
     this.facilitatorid = facilitatorid;
     this.workshopid = workshopid;
     this.lastName = lastName;
}

<!--- For each Workshop, append a new Workshop object to the array of Workshops --->
<cfoutput query="GetWorkshops">
     WorkshopArray[WorkshopArray.length] = new Workshop("#JSStringFormat(WorkshopId)#", "#JSStringFormat(StateId)#", "#JSStringFormat(Name)#");
</cfoutput>

<!--- For each Facilitator, append a new Facilitator object to the array of Facilitators --->
<cfoutput query="GetFacilitators">
     FacilitatorArray[FacilitatorArray.length] = new Facilitator("#JSStringFormat(FacilitatorId)#", "#JSStringFormat(WorkshopId)#", "#JSStringFormat(lastName)#");
</cfoutput>

/* Fill the Workshop select box based on the stateID*/
function fillWorkshops()
{
     // Stop if there is no selected ParentID
     if (document.formWorkshops.stateID.selectedIndex == -1)
     {
          return;
     }
     
     var stateID = document.formWorkshops.stateID.options[document.formWorkshops.stateID.selectedIndex].value;
     
     // Remove all options in the Workshop select box
     document.formWorkshops.WorkshopID.options.length = 0;
     
     // For each item in the Workshoparray ...
     for (var i = 0; i < WorkshopArray.length; i++)
     {
          // If the Workshop's stateIDFK is the same as the currently selected stateID
          if (WorkshopArray[i].stateid == stateID)
          {
               // Put a new option in the Workshop select box
               document.formWorkshops.WorkshopID.options[document.formWorkshops.WorkshopID.options.length] = new Option(WorkshopArray[i].name, WorkshopArray[i].workshopid);
          }
     }
}

/* Fill the Facilitators select box based on the WorkshopID*/
function fillFacilitators()
{
     // Stop if there is no selected ParentID
     if (document.formWorkshops.WorkshopID.selectedIndex == -1)
     {
          return;
     }
     
     var WorkshopID = document.formWorkshops.WorkshopID.options[document.formWorkshops.WorkshopID.selectedIndex].value;
     
     // Remove all options in the Facilitators select box
     document.formWorkshops.FacilitatorID.options.length = 0;
     
     // For each item in the Facilitatorarray ...
     for (var i = 0; i < FacilitatorArray.length; i++)
     {
          // If the Facilitator's WorkshopIDFK is the same as the currently selected WorkshopID
          if (FacilitatorArray[i].workshopid == WorkshopID)
          {
               // Put a new option in the Facilitator select box
               document.formWorkshops.FacilitatorID.options[document.formWorkshops.FacilitatorID.options.length] = new Option(FacilitatorArray[i].lastName, FacilitatorArray[i].facilitatorid);
          }
     }
}
-->
</script>
</link>
</head>

<body>
<center>
  <div id="survey">
    <div id="logo">&nbsp;<a href="http://www.intasc.org"><img src="../../images/form_logo2005.gif" alt="intasc" width="68" height="20" border="0" /></a></div>
<form name="formWorkshops" action="fhome.cfm">
     
     <select name="stateID" onChange="fillWorkshops()" style="width: 120px;">
          <option value="">Select State</option>
          <cfoutput query="GetStates"><option value="#GetStates.stateID#">#GetStates.statename#</option></cfoutput>
     </select>

     <select name="WorkshopID" onChange="fillFacilitators()" style="width: 200px;"></select>

     <select name="FacilitatorID" style="width: 200px;"></select>
</form>
</div>
</body>
</html>

0
 
LVL 2

Author Comment

by:nmarano
ID: 16738804
GEEZ!  I will give this a try and see what happens.  Thank you very much for all your time.  

Thanks
Nick
0
 
LVL 2

Author Comment

by:nmarano
ID: 16741917
mrichmon,

We're just about there.  I switched my table structure and tweaked the code that you provided.  The State list is populated and when I pick a state, the Workshops get populated, but once I pick a workshop, the facilitators do not get populated.  I'm posting the code below.

Thanks
Nick



<cfquery name="GetWorkshops" datasource="aptvre">
SELECT * FROM ws_workshop
ORDER BY StateId, WorkshopName
</cfquery>

<cfquery name="GetFacilitators" datasource="aptvre">
SELECT ws_facilitator .*, ws_facilitator.WorkshopId FROM ws_facilitator
INNER JOIN ws_facilitatortoworkshop ON ws_facilitator.FacilitatorId = ws_facilitatortoworkshop.FacilitatorId
ORDER BY WorkshopId, LastName
</cfquery>

<!--Then the part to generate the javascript arrays:-->

<cfquery name="GetStates" datasource="aptvre">
SELECT * FROM ws_state
ORDER BY StateName
</cfquery>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Three Selects Related</title>
<link rel="stylesheet" type="text/css" href="../../styles/intasc_form.css" />
<script type="text/javascript" language="JavaScript">
<!--
/* Dynamically populate Workshop select box based on seleted stateID and then Facilitators based on workshopID */
// Create an array to hold Workshops and Facilitators
var WorkshopArray = new Array;
var FacilitatorArray = new Array;

// Define a custom Javascript object type to represent a single Workshop
function Workshop(workshopid, stateid, workshopname)
{
     this.workshopid = workshopid;
     this.stateid = stateid;
     this.workshopname = workshopname;
}

// Define a custom Javascript object type to represent a single Facilitator
function Facilitator(facilitatorid, workshopid, lastName)
{
     this.facilitatorid = facilitatorid;
     this.workshopid = workshopid;
     this.lastName = lastName;
}

<!--- For each Workshop, append a new Workshop object to the array of Workshops --->
<cfoutput query="GetWorkshops">
     WorkshopArray[WorkshopArray.length] = new Workshop("#JSStringFormat(WorkshopId)#", "#JSStringFormat(StateId)#", "#JSStringFormat(WorkshopName)#");
</cfoutput>

<!--- For each Facilitator, append a new Facilitator object to the array of Facilitators --->
<cfoutput query="GetFacilitators">
     FacilitatorArray[FacilitatorArray.length] = new Facilitator("#JSStringFormat(FacilitatorId)#", "#JSStringFormat(WorkshopId)#", "#JSStringFormat(lastName)#");
</cfoutput>

/* Fill the Workshop select box based on the stateID*/
function fillWorkshops()
{
     // Stop if there is no selected ParentID
     if (document.formWorkshops.stateID.selectedIndex == -1)
     {
          return;
     }
     
     var stateID = document.formWorkshops.stateID.options[document.formWorkshops.stateID.selectedIndex].value;
     
     // Remove all options in the Workshop select box
     document.formWorkshops.WorkshopID.options.length = 0;
     
     // For each item in the Workshoparray ...
     for (var i = 0; i < WorkshopArray.length; i++)
     {
          // If the Workshop's stateIDFK is the same as the currently selected stateID
          if (WorkshopArray[i].stateid == stateID)
          {
               // Put a new option in the Workshop select box
               document.formWorkshops.WorkshopID.options[document.formWorkshops.WorkshopID.options.length] = new Option(WorkshopArray[i].workshopname, WorkshopArray[i].workshopid);
          }
     }
}

/* Fill the Facilitators select box based on the WorkshopID*/
function fillFacilitators()
{
     // Stop if there is no selected ParentID
     if (document.formWorkshops.WorkshopID.selectedIndex == -1)
     {
          return;
     }
     
     var WorkshopID = document.formWorkshops.WorkshopID.options[document.formWorkshops.WorkshopID.selectedIndex].value;
     
     // Remove all options in the Facilitators select box
     document.formWorkshops.FacilitatorID.options.length = 0;
     
     // For each item in the Facilitatorarray ...
     for (var i = 0; i < FacilitatorArray.length; i++)
     {
          // If the Facilitator's WorkshopIDFK is the same as the currently selected WorkshopID
          if (FacilitatorArray[i].workshopid == WorkshopID)
          {
               // Put a new option in the Facilitator select box
               document.formWorkshops.FacilitatorID.options[document.formWorkshops.FacilitatorID.options.length] = new Option(FacilitatorArray[i].lastName, FacilitatorArray[i].facilitatorid);
          }
     }
}
-->
</script>
</link>
</head>

<body>
<center>
<div id="survey">
  <div id="logo">&nbsp;<a href="http://www.intasc.org"><img src="../../images/form_logo2005.gif" alt="intasc" width="68" height="20" border="0" /></a></div>
  <form name="formWorkshops" action="fhome.cfm">
    <select name="stateID" onChange="fillWorkshops()" style="width: 120px;">
      <option value="">Select State</option>
      <cfoutput query="GetStates">
        <option value="#GetStates.stateid#">#GetStates.statename#</option>
      </cfoutput>
    </select>
    <select name="WorkshopID" onChange="fillFacilitators()" style="width: 400px;">
    </select>
    <select name="FacilitatorID" style="width: 200px;">
    </select>
  </form>
</div>
</body>
</html>
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 16744456
I don't see anything that jumps out as wrong.  DO you have a link or the source that is generated that I could look at?
0
 
LVL 2

Author Comment

by:nmarano
ID: 16744577
The link is below, but there isn't an error message  You can either pick NH or WV because I know that the facilitators for those states are populated

http://corvus.bc.edu/projects/aptvre/teacher/login2.cfm

One thing that I noticed and maybe I am incorrect, but in this query

<cfquery name="GetFacilitators" datasource="aptvre">
SELECT ws_facilitator .*, ws_facilitator.WorkshopId FROM ws_facilitator
INNER JOIN ws_facilitatortoworkshop ON ws_facilitator.FacilitatorId = ws_facilitatortoworkshop.FacilitatorId
ORDER BY WorkshopId, LastName
</cfquery>

ws_facilitator.workshopID From ws_facilitator.  The ws_facilitator.workshopID does not exist because in the earlier post where you defined the tables, you didn't put the workshopID in the ws_facilitator table.  Is that what's missing?  If so, I can easily create that field.

Thanks
Nick
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 16744613
Oh it should be this:
<cfquery name="GetFacilitators" datasource="aptvre">
SELECT ws_facilitator .*, ws_facilitatortoworkshop.WorkshopId FROM ws_facilitator
INNER JOIN ws_facilitatortoworkshop ON ws_facilitator.FacilitatorId = ws_facilitatortoworkshop.FacilitatorId
ORDER BY WorkshopId, LastName
</cfquery>
0
 
LVL 2

Author Comment

by:nmarano
ID: 16744699
Initially it gave me an error message saying WorkshopID is ambiguous, so I removed WorkshopID in the order by, and it allowed me to select the state and it populated the workshop list but not the facilitators.  SO I then made it ws_facilitatortoworkshop.workshopID and got the same results.  It has to be something small that I am missing

Thanks
Nick
0
 
LVL 2

Author Comment

by:nmarano
ID: 16744813
I think I found it!  In the function

function Facilitator(facilitatorid, workshopid, lastName)
{
     this.facilitatorid = facilitatorid;
     this.workshopid = workshopid;
     this.lastName = lastName;
}

It's looking for a workshopID that doesn't exist.  I put a couple of WorkshopID's in the ws_facilitator table and it seems to work.  With that said, should I put a workshopID field in the ws_facilitator table or am I incorrect?
0
 
LVL 35

Accepted Solution

by:
mrichmon earned 2000 total points
ID: 16744877
No the workshop Id should be in the ws_facilitatortoworkshop table  And then joined.

However, looking at the results you are getting a blank workshopId for most facilitators.  That should not happen with an INNER JOIN unless you have null entries.

Double check your tables - especially ws_facilitatortoworkshop
0
 
LVL 2

Author Comment

by:nmarano
ID: 16745016
mrichmon,

I had a workshopID field in the facilitator table that wasn't populated and that was the issue.  Once I removed it, everything worked.  Here is the code for reference. Again thanks for all the help

Nick



<cfquery name="GetWorkshops" datasource="aptvre">
SELECT * FROM ws_workshop
ORDER BY StateId, WorkshopName
</cfquery>

<cfquery name="GetFacilitators" datasource="aptvre">
SELECT ws_facilitator .*, ws_facilitatortoworkshop.WorkshopId FROM ws_facilitator
INNER JOIN ws_facilitatortoworkshop ON ws_facilitator.FacilitatorId = ws_facilitatortoworkshop.FacilitatorId
ORDER BY ws_facilitatortoworkshop.WorkshopID, LastName
</cfquery>

<!--Then the part to generate the javascript arrays:-->

<cfquery name="GetStates" datasource="aptvre">
SELECT * FROM ws_state
ORDER BY StateName
</cfquery>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Three Selects Related</title>
<link rel="stylesheet" type="text/css" href="../../styles/intasc_form.css" />
<script type="text/javascript" language="JavaScript">
<!--
/* Dynamically populate Workshop select box based on seleted stateID and then Facilitators based on workshopID */
// Create an array to hold Workshops and Facilitators
var WorkshopArray = new Array;
var FacilitatorArray = new Array;

// Define a custom Javascript object type to represent a single Workshop
function Workshop(workshopid, stateid, workshopname)
{
     this.workshopid = workshopid;
     this.stateid = stateid;
     this.workshopname = workshopname;
}

// Define a custom Javascript object type to represent a single Facilitator
function Facilitator(facilitatorid, workshopid, lastName)
{
     this.facilitatorid = facilitatorid;
     this.workshopid = workshopid;
     this.lastName = lastName;
}

<!--- For each Workshop, append a new Workshop object to the array of Workshops --->
<cfoutput query="GetWorkshops">
     WorkshopArray[WorkshopArray.length] = new Workshop("#JSStringFormat(WorkshopId)#", "#JSStringFormat(StateId)#", "#JSStringFormat(WorkshopName)#");
</cfoutput>

<!--- For each Facilitator, append a new Facilitator object to the array of Facilitators --->
<cfoutput query="GetFacilitators">
     FacilitatorArray[FacilitatorArray.length] = new Facilitator("#JSStringFormat(FacilitatorId)#", "#JSStringFormat(WorkshopId)#", "#JSStringFormat(lastName)#");
</cfoutput>

/* Fill the Workshop select box based on the stateID*/
function fillWorkshops()
{
     // Stop if there is no selected ParentID
     if (document.formWorkshops.stateID.selectedIndex == -1)
     {
          return;
     }
     
     var stateID = document.formWorkshops.stateID.options[document.formWorkshops.stateID.selectedIndex].value;
     
     // Remove all options in the Workshop select box
     document.formWorkshops.WorkshopID.options.length = 0;
     
     // For each item in the Workshoparray ...
     for (var i = 0; i < WorkshopArray.length; i++)
     {
          // If the Workshop's stateIDFK is the same as the currently selected stateID
          if (WorkshopArray[i].stateid == stateID)
          {
               // Put a new option in the Workshop select box
               document.formWorkshops.WorkshopID.options[document.formWorkshops.WorkshopID.options.length] = new Option(WorkshopArray[i].workshopname, WorkshopArray[i].workshopid);
          }
     }
}

/* Fill the Facilitators select box based on the WorkshopID*/
function fillFacilitators()
{
     // Stop if there is no selected ParentID
     if (document.formWorkshops.WorkshopID.selectedIndex == -1)
     {
          return;
     }
     
     var WorkshopID = document.formWorkshops.WorkshopID.options[document.formWorkshops.WorkshopID.selectedIndex].value;
     
     // Remove all options in the Facilitators select box
     document.formWorkshops.FacilitatorID.options.length = 0;
     
     // For each item in the Facilitatorarray ...
     for (var i = 0; i < FacilitatorArray.length; i++)
     {
          // If the Facilitator's WorkshopIDFK is the same as the currently selected WorkshopID
          if (FacilitatorArray[i].workshopid == WorkshopID)
          {
               // Put a new option in the Facilitator select box
               document.formWorkshops.FacilitatorID.options[document.formWorkshops.FacilitatorID.options.length] = new Option(FacilitatorArray[i].lastName, FacilitatorArray[i].facilitatorid);
          }
     }
}
-->
</script>
</link>
</head>

<body>
<center>
<div id="survey">
  <div id="logo">&nbsp;<a href="http://www.intasc.org"><img src="../../images/form_logo2005.gif" alt="intasc" width="68" height="20" border="0" /></a></div>
  <form name="formWorkshops" action="fhome.cfm">
    <select name="stateID" onChange="fillWorkshops()" style="width: 120px;">
      <option value="">Select State</option>
      <cfoutput query="GetStates">
        <option value="#GetStates.stateid#">#GetStates.statename#</option>
      </cfoutput>
    </select>
    <select name="WorkshopID" onChange="fillFacilitators()" style="width: 400px;">
    </select>
    <select name="FacilitatorID" style="width: 200px;">
    </select>
  </form>
</div>
</body>
</html>
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
Suggested Courses
Course of the Month9 days, 17 hours left to enroll

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question