5 Replies Latest reply on Mar 12, 2007 5:48 AM by Dan Bracuk

    Related Selects

    Manuel_Lino
      I'm trying to get a couple of related selects working based on Dan Bracuk's code. Below I'll write my interpretation.

      1 - On top of cfm page:

      <cfquery name="Cliente" datasource="intraartis">
      SELECT ClienteID, ClienteNome
      FROM dbo.cliente_tbl
      ORDER BY ClienteNome
      </cfquery>

      <cfquery name="Contacto" datasource="intraartis">
      SELECT ClienteID, ContactoID, ContactoNome
      FROM dbo.contactos_tbl
      ORDER BY ContactoNome
      </cfquery>

      2 - Within the Head tags:

      <script language="javascript">

      <cfset idArray=ListToArray(ValueList(Contacto.ContactoID))>;
      <cfset compareArray=ListToArray(ValueList(Contacto.ClienteID))>;
      <cfset descriptionArray=ListToArray(ValueList(Contacto.ContactoNome))>;

      function selectChange(selectedValue)
      {
      <cfoutput>
      var #toScript(idArray, "theIdArray")#;
      var #toScript(compareArray, "theCompareArray")#;
      var #toScript(descriptionArray, "theDescriptionArray")#;
      </cfoutput>

      var i=0;
      var j=0;
      var len=theIdArray.length;

      document.forms['ProjectoFrm'].Contacto.options.length=0;
      for(i=0;i<len;i++)
      {
      if(theCompareArray==selectedValue)
      {
      document.forms['ProjectoFrm'].Contacto.options[j]=new Option(theDescriptionArray,theIdArray);
      j++;
      }
      }
      return true;
      }
      </script>

      3 - Inside of the form tags:

      <cfselect query="Cliente" name="ClienteID" onChange="selectChange(this.Value);" value="ClienteID" display="ClienteNome" selected="#selectedCliente#"/>

      <cfif existingrecord>
      <cfselect query="Contacto" name="ContactoID" value="ContactoID" display="ContactoNome" selected="#selectedContacto#"/>
      <cfelse>
      <select name="ContactoID">
      <option value="1">Not Updated</option>
      </select>
      </cfif>

      4. It comes up with the COLDFUSION error: Variable SELECTEDCLIENTE is undefined.
      Viewing the source of the page, the arrays are built.

      Could Dan Bracuk or someone else give me some help?
      Thanks.

      Manuel
        • 1. Re: Related Selects
          Dan Bracuk Level 5
          I don't see you setting a variable named selectedCliente anywhere.

          In the page where my code resides, there is a third query that looks for existing values. If there are none, which means I will be inserting rather than updating records, I have defaults. Right now that code is at work and I am not.

          • 2. Re: Related Selects
            Manuel_Lino Level 1
            Thanks Dan for your reply.
            I´m pasting your code as it is on the post of 02/26/2007:

            I only had to do this once, and it works. Here it is

            <cfquery name="Etiology" datasource="burns">
            select EtiologyID, Etiology
            from burns_Etiology
            where show = <cfqueryparam cfsqltype="cf_sql_char" value="Y">
            </cfquery>

            <cfquery name="EtiologyDetail" datasource="burns">
            select EtiologyID, EtiologyDetailID, detail
            from burns_EtiologyDetail
            where show = <cfqueryparam cfsqltype="cf_sql_char" value="Y">
            </cfquery>

            // arrays to make selectcontrol script work
            idArray = ListToArray(ValueList(EtiologyDetail.EtiologyDetailID));
            compareArray = ListToArray(ValueList(EtiologyDetail.EtiologyID));
            descriptionArray = ListToArray(ValueList(EtiologyDetail.detail));

            another query for the current value, if applicable

            <script language="JavaScript">
            function selectChange(selectedValue)
            {
            // changes the options for etilogy detail based on selection of etiology
            <cfoutput>
            var #toScript(idArray, "theIdArray")#;
            var #toScript(compareArray, "theCompareArray")#;
            var #toScript(descriptionArray, "theDescriptionArray")#;
            </cfoutput>
            var i = 0;
            var j = 0;
            var len = theIdArray.length;

            // clear the detail select control
            document.forms['dataForm'].EtiologyDetail.options.length = 0;

            for ( i = 0; i < len; i++)
            {
            if (theCompareArray == selectedValue)
            {
            document.forms['dataForm'].EtiologyDetail.options[j] = new Option(theDescriptionArray,theIdArray);
            j++;
            } // end if
            } // end loop

            return true;
            } // end function
            </script>

            Function gets called here
            <th>Etiology</th>
            <td><cfselect query="Etiology" name="Etiology" onChange="selectChange(this.value);"
            value="Etiologyid" display="Etiology" selected="#selectedEtiology#" />
            </td>

            <th>Detail</th>
            <td colspan="3">
            <cfif existingrecord>
            <cfselect query="etdetail" value="EtiologyDetailID" display="detail"
            name="EtiologyDetail" selected="#selectedEtiologyDetail#" />
            <cfelse>
            <select name="EtiologyDetail" >
            <option value="1">Not Updated</option>
            </select>
            </cfif>

            I don´t see where you set the variable "selectedEthiology".
            Hope this helps.
            • 3. Re: Related Selects
              Dan Bracuk Level 5
              It was in the part of the code that I didn't copy.
              • 4. Re: Related Selects
                Manuel_Lino Level 1
                Thanks again.
                Could you either paste the missing part of the code or help me with the code that I have?
                Also, I tried to remove the selected attribute. It also comes up with the error "variable existingrecord is undefined".
                Apreciate your help.

                Manuel
                • 5. Re: Related Selects
                  Dan Bracuk Level 5
                  Start with the fundamentals. You can't use a variable until you define it somewhere. Common ways to define variables are cfset and cfscript.

                  The page that I wrote is used to either add records to a db, or update them. Hence the name of the variable, existingrecord. Is that what you are trying to achieve? I already hinted as to how I came up with that variable. You have to read more than the code snippets.