Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 458
  • Last Modified:

Trying to use ThreeSelectsRelated Custom Tag

I am trying to use the ThreeSelectsRelated custom tag, and I am not getting the expected results.
When I select a Country, the Region does not pull back all the regions (states) and upon selecting a
Region, Cities only get populated with one option instead of all cities for that region.

I have 3 DB tables:
Countries
Regions
Cities

CountryID -> Regions.CountryID -> Cities.RegionID (relationship)



***CUSTOM TAG CODE:***
<!---
  Filename: ThreeSelectsRelated.cfm
  Modified: 9/18/2002 by Nate Weiss (nate@nateweiss.com)
--->

<!--- REMOVE THIS AND FIRST LINE IF NOT USING CF3.1 OR LATER --->
<CFSETTING ENABLECFOUTPUTONLY="YES">

<!--- TAG PARAMETERS --->
<CFPARAM NAME="Attributes.Query">  
<CFPARAM NAME="Attributes.Type1" DEFAULT="Select">  
<CFPARAM NAME="Attributes.HTMLAfterEachRadio1" DEFAULT="">  
<CFPARAM NAME="Attributes.Name1" DEFAULT="TwoSelectsRelated1">
<CFPARAM NAME="Attributes.Name2" DEFAULT="TwoSelectsRelated2">
<CFPARAM NAME="Attributes.Name3" DEFAULT="TwoSelectsRelated3">
<CFPARAM NAME="Attributes.Value1">
<CFPARAM NAME="Attributes.Value2">
<CFPARAM NAME="Attributes.Value3">
<CFPARAM NAME="Attributes.Display1" DEFAULT="#Attributes.Value1#">
<CFPARAM NAME="Attributes.Display2" DEFAULT="#Attributes.Value2#">
<CFPARAM NAME="Attributes.Display2" DEFAULT="#Attributes.Value3#">
<CFPARAM NAME="Attributes.Default1" DEFAULT="xxxxxxxxxxxx">
<CFPARAM NAME="Attributes.Default2" DEFAULT="xxxxxxxxxxxx">
<CFPARAM NAME="Attributes.Default3" DEFAULT="xxxxxxxxxxxx">
<CFPARAM NAME="Attributes.Multiple3" DEFAULT="No">
<CFPARAM NAME="Attributes.Size1" DEFAULT="1">
<CFPARAM NAME="Attributes.Size2" DEFAULT="1">
<CFPARAM NAME="Attributes.Size3" DEFAULT="1">
<CFPARAM NAME="Attributes.ExtraOptions2" DEFAULT="">
<CFPARAM NAME="Attributes.ExtraOptions3" DEFAULT="">
<CFPARAM NAME="Attributes.Width1" DEFAULT="">
<CFPARAM NAME="Attributes.Width2" DEFAULT="">
<CFPARAM NAME="Attributes.Width3" DEFAULT="">
<CFPARAM NAME="Attributes.ForceWidth1" DEFAULT="">
<CFPARAM NAME="Attributes.ForceWidth2" DEFAULT="">
<CFPARAM NAME="Attributes.ForceWidth3" DEFAULT="">
<CFPARAM NAME="Attributes.ForceWidthChar" DEFAULT="&nbsp;">
<CFPARAM NAME="Attributes.EmptyText1" DEFAULT="">
<CFPARAM NAME="Attributes.EmptyText2" DEFAULT="">
<CFPARAM NAME="Attributes.EmptyText3" DEFAULT="">
<CFPARAM NAME="Attributes.Message1" DEFAULT="You must choose an option for #Attributes.Name1#.">
<CFPARAM NAME="Attributes.Message2" DEFAULT="You must choose an option for #Attributes.Name2#.">
<CFPARAM NAME="Attributes.Message3" DEFAULT="You must choose an option for #Attributes.Name3#.">
<CFPARAM NAME="Attributes.FormName" DEFAULT="forms[0]">
<CFPARAM NAME="Attributes.HTMLAfter1" DEFAULT="">
<CFPARAM NAME="Attributes.HTMLAfter2" DEFAULT="">
<CFPARAM NAME="Attributes.OnChange" DEFAULT="">
<CFPARAM NAME="Attributes.AutoSelectFirst" DEFAULT="Yes">


<CFIF Attributes.Type1 is "Radio">
  <CFSET Attributes.EmptyText1 = "">
  <CFSET Attributes.ForceWidth1 = "">
</CFIF>



<CFSET FunctionName = ReplaceList(Attributes.FormName, "[,]", ",") & "ChangeMenu">
<CFSET FunctionName2 = ReplaceList(Attributes.FormName, "[,]", ",") & "ChangeMenu2">


<!--- "MAGIC" SHORTCUTS FOR THE ONCHANGE HANDLER --->
<CFIF Attributes.OnChange is "Jump!">
  <CFSET Attributes.OnChange = "document.location = this.options[selectedIndex].value;">
<CFELSEIF Attributes.OnChange is "Submit!">
  <CFSET Attributes.OnChange = "this.form.submit();">
</CFIF>



<!--- USE PASSED QUERY WITHIN THIS CODE AS "MyQuery" --->
<CFSET MyQuery = Evaluate("Caller.#Attributes.Query#")>


<!--- BEGIN JAVASCRIPTING --->
<CFOUTPUT>
      <SCRIPT LANGUAGE="JavaScript1.1">
        // javascript code generated by the CF_TwoSelectsRelated Cold Fusion tag (Nate Weiss, 4/98)
            // portions adapted from Nick Heinle's code at http://webreference.com/javascript/960902/select_boxes.html
        var maxlength = 10;
        OneA = new Array;

            var trueLength = OneA.length;  
            var lst = OneA.length;
    var Trak = 0;

    function require_#Attributes.Name1#() {
              with (document.#Attributes.FormName#.#Attributes.Name1#) {
                    RetVal = true;
                    if (Trak == -1) RetVal = false;
                          <CFIF Attributes.Type1 is "Select">
                          else RetVal = !(options[Trak].value == '');
                              <CFELSEIF Attributes.Type1 is "Radio">
                          else { if (document.#Attributes.FormName#.#Attributes.Name1#[Trak].value == '') RetVal = false; else RetVal = true;}
                              </CFIF>
                    if (!RetVal) alert('#Attributes.Message1#');
                        return RetVal;
                  }
            }

    function require_#Attributes.Name2#() {
              with (document.#Attributes.FormName#.#Attributes.Name2#) {
                    RetVal = true;
                    if (selectedIndex == -1) RetVal = false;
                          else RetVal = !(options[selectedIndex].value == '');
                    if (!RetVal) alert('#Attributes.Message2#');
        return eval(RetVal);
                        return RetVal;
                  }
            }
            
    function require_#Attributes.Name3#() {
              with (document.#Attributes.FormName#.#Attributes.Name3#) {
                    RetVal = true;
                    if (selectedIndex == -1) RetVal = false;
                          else RetVal = !(options[selectedIndex].value == '');
                    if (!RetVal) alert('#Attributes.Message3#');
                        return RetVal
                  }
            }
            
            function require_#Attributes.Name1#And#Attributes.Name2#() {
              return ((require_#Attributes.Name1#()) && (require_#Attributes.Name2#()));
            }

            function require_#Attributes.Name2#And#Attributes.Name3#() {
              return ((require_#Attributes.Name2#()) && (require_#Attributes.Name3#()));
            }
            
            function require_#Attributes.Name1#And#Attributes.Name2#And#Attributes.Name3#() {
              return ((require_#Attributes.Name1#And#Attributes.Name2#()) && (require_#Attributes.Name3#()));
            }
                        
            function #FunctionName#() {
               OneA.length = 0;
       var menuNum = document.#Attributes.FormName#.#Attributes.Name1#.selectedIndex;
               if (menuNum == null) return;  
                   Trak = menuNum;
                   <!--- ignore if a blank item gets clicked --->
                   <CFIF Attributes.Type1 is "Select">
                   if (document.#Attributes.FormName#.#Attributes.Name1#.options[menuNum].value == '') return;
                   if (document.#Attributes.FormName#.#Attributes.Name1#.options[menuNum].value == null) return;
                   </CFIF>
</CFOUTPUT>


<!--- COUNTER VARIABLE WILL HOLD NUMBER OF GROUPS (OPTIONS IN FIRST SELECT) --->
<CFSET Counter = IIF(Attributes.EmptyText1 NEQ "", 1, 0)>

<!--- CREATE AN "IF" STATEMENT THAT COVERS EACH ITEM IN THE FIRST SELECT BOX --->
<!--- WITHIN THE "IF" STATMENT, PRE-POPULATE ARRAY WITH CORRESPONDING ITEMS FOR SECOND SELECT  --->
<CFOUTPUT QUERY="MyQuery" GROUP="#Attributes.Display1#">
      if (menuNum == #Counter#) {
        NewOpt = new Array;
            NewVal = new Array;
    <CFIF Attributes.EmptyText2 NEQ "">
      NewOpt[0] = new Option("#JSStringFormat(Attributes.EmptyText2)#", "");
      <CFSET Counter2 = 1>
    <CFELSE>
      <CFSET Counter2 = 0>  
    </CFIF>
            <CFOUTPUT GROUP="#Attributes.Display2#">NewOpt[#Counter2#] = new Option("#JSStringFormat(MyQuery[Attributes.Display2][MyQuery.CurrentRow])#", "#JSStringFormat(MyQuery[Attributes.Value2][MyQuery.CurrentRow])#"); <CFSET Counter2 = Counter2 + 1></CFOUTPUT>
      } <CFSET Counter = Counter + 1>
</CFOUTPUT>


<!--- finish up the ChangeMenu() function --->
<CFOUTPUT>
  tot = NewOpt.length;
      lst = document.#Attributes.FormName#.#Attributes.Name2#.options.length;
      for (i = lst; i > 0; i--) {
        document.#Attributes.FormName#.#Attributes.Name2#.options[i] = null;
      }
  for (i = 0; i < tot; i++) {
        document.#Attributes.FormName#.#Attributes.Name2#.options[i] = NewOpt[i];
      }
  <!--- <CFIF Attributes.AutoSelectFirst is "Yes"> --->
        document.#Attributes.FormName#.#Attributes.Name2#.options[0].selected = true;
      <!--- </CFIF> --->
      #FunctionName2#(0);
}
</CFOUTPUT>




<!--- ALLOW FOR AUTO-SIZING "SHORTCUT" OF SELECT BOXES --->
<CFIF Attributes.Size1 is "Auto">
  <!--- MAKE THE FIRST SELECT BE BIG ENOUGH FOR ALL OF ITS OPTIONS --->
  <CFSET Attributes.Size1 = Counter>
</CFIF>
<CFIF Attributes.Size2 is "Auto">
  <!--- MAKE THE SECOND SELECT BE THE SAME SIZE AS THE FIRST --->
  <CFSET Attributes.Size2 = Attributes.Size1>
</CFIF>
<CFIF Attributes.Size3 is "Auto">
  <!--- MAKE THE SECOND SELECT BE THE SAME SIZE AS THE FIRST --->
  <CFSET Attributes.Size3 = Attributes.Size1>
</CFIF>



<CFOUTPUT>
            function #FunctionName2#() {
               OneA.length = 0;
                   menuNum = Trak;
       <CFIF Attributes.EmptyText1 NEQ "">menuNum = menuNum - 1;</CFIF>
               //menuNum = document.#Attributes.FormName#.#Attributes.Name1#.selectedIndex;
               menuNum2 = document.#Attributes.FormName#.#Attributes.Name2#.selectedIndex;
               if (menuNum == -1) return;  
               if (menuNum2 == null) return;  
                   <!--- ignore if a the blank item gets clicked --->
                   if (document.#Attributes.FormName#.#Attributes.Name2#.options[menuNum2].value == '') return;
</CFOUTPUT>


<!--- COUNTER VARIABLE WILL HOLD NUMBER OF GROUPS (OPTIONS IN FIRST SELECT) --->
<CFSET Counter1 = 0>

<!--- CREATE AN "IF" STATEMENT THAT COVERS EACH ITEM IN THE FIRST SELECT BOX --->
<!--- WITHIN THE "IF" STATMENT, PRE-POPULATE ARRAY WITH CORRESPONDING ITEMS FOR SECOND SELECT  --->
<CFOUTPUT QUERY="MyQuery" GROUP="#Attributes.Display1#">
if (menuNum == #Counter1#) {
<CFSET Counter = IIF(Attributes.EmptyText2 is not "", 1, 0)>
<CFOUTPUT GROUP="#Attributes.Display2#">
      if (menuNum2 == #Counter#) {
        NewOpt = new Array;
            NewVal = new Array;
    <CFSET Counter2 = IIF(Attributes.EmptyText3 is not "", 1, 0)>
    <CFIF Attributes.EmptyText3 is not "">NewOpt[0] = new Option("#JSStringFormat(Attributes.EmptyText3)#", ""); </CFIF>
            <CFOUTPUT>
              NewOpt[#Counter2#] = new Option("#JSStringFormat(MyQuery[Attributes.Display3][MyQuery.CurrentRow])#", "#JSStringFormat(MyQuery[Attributes.Value3][MyQuery.CurrentRow])#");
                  <CFSET Counter2 = Counter2 + 1>
            </CFOUTPUT>
      } <CFSET Counter = Counter + 1>
</CFOUTPUT>      
}      <CFSET Counter1 = Counter1 + 1>
</CFOUTPUT>


<CFOUTPUT>
  tot = NewOpt.length;
      lst = document.#Attributes.FormName#.#Attributes.Name3#.options.length;
      
      for (i = lst; i > 0; i--) {
        document.#Attributes.FormName#.#Attributes.Name3#.options[i] = null;
      }
  for (i = 0; i < tot; i++) {
        document.#Attributes.FormName#.#Attributes.Name3#.options[i] = NewOpt[i];
      }
  <CFIF Attributes.AutoSelectFirst is "Yes">
        document.#Attributes.FormName#.#Attributes.Name3#.options[0].selected = true;
      </CFIF>
}
</SCRIPT>
</CFOUTPUT>

<!--- DONE WITH JAVASCRIPTING.  NOW WE JUST HAVE TO DISPLAY THE FORM ELEMENTS --->







<!--- OUTPUT FIRST FORM ELEMENT --->
<CFIF Attributes.Type1 is "Select">
<CFOUTPUT><SELECT NAME="#Attributes.Name1#" onChange="#FunctionName#(this.selectedIndex)" SIZE="#Attributes.Size1#" <CFIF Attributes.Width1 is not "">STYLE="width:#Attributes.Width1#"</CFIF>></CFOUTPUT>
      <!--- SPECIAL FIRST ITEM, IF REQUESTED --->
      <CFIF Attributes.EmptyText1 is not ""><CFOUTPUT><OPTION VALUE="">#Attributes.EmptyText1#</CFOUTPUT></CFIF>
      <!--- GENERATE REMAINING ITEMS FROM QUERY --->
      <CFOUTPUT QUERY="MyQuery" GROUP="#Attributes.Display1#"><OPTION VALUE="#MyQuery[Attributes.Value1][MyQuery.CurrentRow]#"  <CFIF MyQuery[Attributes.Value1][MyQuery.CurrentRow] is Attributes.Default1>SELECTED</CFIF>>#MyQuery[Attributes.Display1][MyQuery.CurrentRow]#</CFOUTPUT>
      
  <!--- "FORCE WIDTH" OPTION AT BOTTOM, IF REQUESTED --->
      <CFIF Attributes.ForceWidth1 is not ""><CFOUTPUT><OPTION VALUE="">#RepeatString(Attributes.ForceWidthChar, Attributes.ForceWidth1)#</CFOUTPUT></CFIF>
<CFOUTPUT></SELECT></CFOUTPUT>

<CFELSE>
  <!--- RADIO BOXES --->
  <CFSET Counter = 0>
  <CFOUTPUT QUERY="MyQuery" GROUP="#Attributes.Display1#"><INPUT TYPE="Radio" NAME="#Attributes.Name1#" VALUE="#MyQuery[Attributes.Value1][MyQuery.CurrentRow]#" <CFIF MyQuery[Attributes.Value1][MyQuery.CurrentRow] is Attributes.Default1>CHECKED</CFIF> onClick="#FunctionName#(#Counter#)">MyQuery[Attributes.Display1][MyQuery.CurrentRow]#Attributes.HTMLAfterEachRadio1#<CFSET Counter = Counter + 1></CFOUTPUT>

</CFIF>



<!--- INSERT ANY REQUESTED HTML BETWEEN 1ST AND SECOND ITEMS --->
<CFOUTPUT>#Attributes.HTMLAfter1#</CFOUTPUT>



<!--- OUTPUT SECOND SELECT BOX --->
<CFOUTPUT><SELECT NAME="#Attributes.Name2#" SIZE="#Attributes.Size2#" onChange="#FunctionName2#(this.selectedIndex)" <CFIF Attributes.Width2 is not "">STYLE="width:#Attributes.Width2#"</CFIF>></CFOUTPUT>
      <!--- SPECIAL FIRST ITEM, IF REQUESTED --->
      <CFIF Attributes.EmptyText2 is not ""><CFOUTPUT><OPTION VALUE="">#Attributes.EmptyText2#</CFOUTPUT></CFIF>
      
      <!--- GENERATE REMAINING ITEMS FROM QUERY --->
      <!--- WE ONLY NEED TO OUTPUT THE CHOICES FOR THE FIRST GROUP --->
      <!--- <CFSET FirstGroup = Evaluate("MyQuery.#Attributes.Value1#[1]")> --->
      <CFSET FirstGroup = Attributes.Default1>
      <CFSET CurrentGroup2 = "">
      <CFIF Attributes.EmptyText1 is "">
            <CFLOOP QUERY="MyQuery">
              <CFIF MyQuery[Attributes.Value1][MyQuery.CurrentRow] is Variables.FirstGroup>
                    <CFSET ThisValue = MyQuery[Attributes.Value2][MyQuery.CurrentRow]>
                    <CFIF Variables.ThisValue is not Variables.CurrentGroup2>
                      <CFOUTPUT><OPTION VALUE="#Variables.ThisValue#" <CFIF Variables.ThisValue is Attributes.Default2>SELECTED</CFIF>>#MyQuery[Attributes.Display2][MyQuery.CurrentRow]#</CFOUTPUT>
                        <CFSET CurrentGroup2 = MyQuery[Attributes.Value2][MyQuery.CurrentRow]>
                        </CFIF>
                  <!--- <CFELSE>
                    <CFBREAK> --->
                  </CFIF>
            </CFLOOP>
      </CFIF>      

  <!--- "FORCE WIDTH" OPTION AT BOTTOM, IF REQUESTED --->
      <CFIF Attributes.ForceWidth2 is not ""><CFOUTPUT><OPTION VALUE="">#RepeatString(Attributes.ForceWidthChar, Attributes.ForceWidth2)#</CFOUTPUT></CFIF>
      <CFIF Attributes.ExtraOptions2 is not ""><CFLOOP INDEX="This" FROM="1" TO="#Attributes.ExtraOptions2#"><CFOUTPUT><OPTION VALUE=""></CFOUTPUT></CFLOOP></CFIF>
<CFOUTPUT></SELECT></CFOUTPUT>



<!--- INSERT ANY REQUESTED HTML BETWEEN 2ND AND 3RD ITEMS --->
<CFOUTPUT>#Attributes.HTMLAfter2#</CFOUTPUT>



<!--- OUTPUT THIRD SELECT BOX --->
<CFOUTPUT><SELECT NAME="#Attributes.Name3#" SIZE="#Attributes.Size3#" <CFIF Attributes.Multiple3 is "Yes">MULTIPLE </CFIF><CFIF Attributes.Width3 is not "">STYLE="width:#Attributes.Width3#"</CFIF><CFIF Attributes.OnChange is not ""> OnChange="#Attributes.OnChange#"</CFIF>></CFOUTPUT>
      <CFSET FirstGroup = Attributes.Default1>
      <CFIF Attributes.EmptyText3 is "">
            <CFLOOP QUERY="MyQuery">
              <CFIF MyQuery[Attributes.Value1][MyQuery.CurrentRow] is Variables.FirstGroup>
                    <CFSET Hack = MyQuery[Attributes.Value2][MyQuery.CurrentRow]>
                    <CFSET ThisValue = MyQuery[Attributes.Value3][MyQuery.CurrentRow]>
                    <CFPARAM NAME="Variables.FirstGroup2" DEFAULT="#Variables.Hack#">
                    <CFIF MyQuery[Attributes.Value2][MyQuery.CurrentRow] is Variables.FirstGroup2>
                          <CFOUTPUT><OPTION VALUE="#Variables.ThisValue#" <CFIF Variables.ThisValue is Attributes.Default3>SELECTED</CFIF>>#MyQuery[Attributes.Display3][MyQuery.CurrentRow]#</CFOUTPUT>
                        </CFIF>      
                  </CFIF>
            </CFLOOP>
      </CFIF>      

      <CFIF Attributes.ForceWidth3 is not ""><CFOUTPUT><OPTION VALUE="">#RepeatString(Attributes.ForceWidthChar, Attributes.ForceWidth3)#</CFOUTPUT></CFIF>
      <CFIF Attributes.ExtraOptions3 is not ""><CFLOOP INDEX="This" FROM="1" TO="#Attributes.ExtraOptions3#"><CFOUTPUT><OPTION VALUE=""></CFOUTPUT></CFLOOP></CFIF>
<CFOUTPUT></SELECT><SCRIPT LANGUAGE="JavaScript1.1">#FunctionName#()</SCRIPT></CFOUTPUT>

<!--- REMOVE THIS AND FIRST LINE IF NOT USING CF3.1 OR LATER --->
<CFSETTING ENABLECFOUTPUTONLY="NO">



***QUERY AND CALL TAG CODE:***

<CFQUERY DATASOURCE="#mm_datasource_dsn#" NAME="getLocation">
  SELECT a.CountryID, a.Country, b.RegionID, b.Region, b.CountryID, c.CityID, c.City, c.RegionID, c.CountryID
  FROM tbl_Countries a, tbl_Regions b, tbl_Cities c
  WHERE b.RegionID = c.RegionID
  AND a.CountryID IN
    (SELECT CountryID
       FROM tbl_Countries
     WHERE a.CountryID = b.CountryID)
  ORDER BY c.City, b.Region, a.Country
</CFQUERY>

<FORM>

<CF_ThreeSelectsRelated
  Query="getLocation"
  Display1="Country"
  Display2="Region"
  Display3="City"
  Value1="CountryID"
  Value2="RegionID"
  Value3="CityID"
  EmptyText1="Select Country"
  EmptyText2="Select Region"
  Emptytext3="Select City">

</FORM>

0
jollymon6672
Asked:
jollymon6672
  • 2
  • 2
2 Solutions
 
rob_lorentzCommented:


I suspect your query.

try this...

 SELECT a.CountryID, a.Country, b.RegionID, b.Region, b.CountryID, c.CityID, c.City, c.RegionID, c.CountryID
  FROM tbl_Countries a, tbl_Regions b, tbl_Cities c
  WHERE b.RegionID = c.RegionID
  AND a.CountryID = b.CountryID
  ORDER BY c.City, b.Region, a.Country
0
 
mrichmonCommented:
Although I custom write them instead of using the tag, from what I understand:

Your query should be ordered as follows:

First select, second select, third select

I think you have them in the resverse...
0
 
jollymon6672Author Commented:
Rob's query failed. It does not return distinct countries, only a few regions and one city.

Basically, Upon selecting the coutry it should populate the secon select with all regions for that country and upon selecting a region, the third select should be populated with all cities in that region.
0
 
jollymon6672Author Commented:
I reversed the ORDER BY and it worked amazingly enough.
0
 
mrichmonCommented:
Yes because it is done via javascript and the javascript looks at everything that has the same country and stops when the country changes.

That is why it is important ot have them sorted int he correct oreder.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now