7 Replies Latest reply on Apr 20, 2009 6:54 PM by cfwild

    CFSELECT - Binding to two fields?

    Preserved

      I've got a simple form with three selects. Make, Model and Year.

       

      Model is bound to Make using:

      bind="cfc:getdata.getmodels({make@click})"

      bindonload="true"

       

      This works great.

       

      Now, I need to have two fields for makes. I'm trying to do something like this (in my own fake coding words):

       

      <select name="make1" class="input">
                 <option value="" selected>Please Select... </option>
                 <option value="red">Red</option>
                 <option value="Blue">Blue</option>

        </select>

       

      <select name="make2" class="input">
                  <option value="" selected>Please Select... </option>
                  <option value="white">White</option>
                  <option value="black">Black</option>

        </select>

       

          <cfselect name="model" id="model"
                    value="model"
                   display="model"
                   bind="cfc:getdata.getmodels({make@click}) or ({make2@click})"
                      class="input"
                      multiple="no"
                      bindonload="true"    />

      Essentially which ever Make field is chosen is what Model would bind to.I've tried a number of things, even usinig @none with no luck. Is this even possible?  I also might have a problem with my CFC:

       

       

         <!--- Get model by make name --->
         <cffunction name="getmodels" access="remote" returnType="query">
            <cfargument name="make1" type="string" required="true">
         <cfargument name="make2" type="string" required="true">
            <!--- Define variables --->
            <cfset var data="">
         
           <cfif ARGUMENTS.make1 is not "">
             <!--- Get data --->
            <cfquery name="data" datasource="dsn"   >
            SELECT distinct model
           FROM dataone09
            WHERE make = <cfqueryPARAM value = "#ARGUMENTS.make1#"
         CFSQLType = "CF_SQL_VARCHAR">
            ORDER BY model
            </cfquery>
              </cfif>

       

            <cfif ARGUMENTS.make2 is not "">
              <!--- Get data --->
             <cfquery name="data" datasource="dsn"   >
             SELECT distinct model
            FROM datatwo09
             WHERE make = <cfqueryPARAM value = "#ARGUMENTS.make2#"
          CFSQLType = "CF_SQL_VARCHAR">
             ORDER BY model
             </cfquery>
               </cfif>

       

            <!--- And return it --->
            <cfreturn data>
         </cffunction>

       

      Any words of wisdom or guidance would be most helpful,

       

      Thanks!

        • 1. Re: CFSELECT - Binding to two fields?
          ilssac Level 5

          I can not imagine any way that you could do this in the <cfselect...> itself.  I would guess you would need to do this logic in the CFC that is bound to the select.  This CFC would take the two lists and join them into one list to feed the select.  Then it would take the result from the select and figure out which original list it came from.

           

          This would take so serious logic in the bound CFC function, but that is the only place I can see doing this kind of logic.

          • 2. Re: CFSELECT - Binding to two fields?
            cfwild Level 1

            Hi,

             

            I would review how your db is set up.  Conceptually, you should have all your makes in 1 table.  When you query the db, your first select should present all of the makes you have available.  Then when the user selects the make, a where clause can go into the next query and bring back all of the models that correspond to that make.  e.g. WHERE make = Mercedes.

             

            If you have no other options, I'd still go with the single select, but either

             

            a). use a JOIN to bring the makes together if they're in different tables

             

            b).  use something like queryNew and create your own query that you can use to feed the cfselect.  If you're using this approach, you'll want to know what table attaches to what make, so you know where to query for year.

             

            If the two makes aren't in the same table, getting to makes won't be to hard, but then getting to year will present additional painful coding challenges.

             

            I'd go back to my first statement...

             

            Take care,

             

            cfwild

            • 3. Re: CFSELECT - Binding to two fields?
              Preserved Level 1

              Thanks guys, I'm making progress, but still struggling.

               

              Part of the issue I'm having is that the client does not want all makes to appear in one dropdown, it would be too large and the two different types of makes is not the same market as the other.

               

              I've got this so far:

               

              1. A radio button to select which type of make. Choose Car or Motorcycle using this radio button.

              2. A drop down list is then bound to the above radio button, and is then populated from a query based on the answer above. Choose car, and lots of car makes show up (Honda, Toyota ect). Choose Motorcycle and lots of Motorcycle makes show up (Honda, Harley, Yamaha ect).


              3. User then chooses a make from step two, and this is where I'm stuck. I with the bind, I can only pass the make name to the CFC, so how do I differentiate which models to get? If I have them all in the same table, and select based on the make, I get lots of mixes since some car companies make motorcycles like Honda. I need to keep them seperate. If I break up my tables, then given only the argument of Make, how do I get my cfc to query the proper table (cars or Motorcyles)?

               

              Does this make sence??

               

              Preserved

              • 4. Re: CFSELECT - Binding to two fields?
                ilssac Level 5

                Pass back a combo value that give you the data you need.  Then the processing function should easily be able to seperate the combo into its parts.

                 

                <option = "mortorcycle|honda">Honda</option>

                OR

                <option="car|honda">Honda</option>

                 

                Simple string and|or list processing can combine and seperate these values as needed.

                • 5. Re: CFSELECT - Binding to two fields?
                  Preserved Level 1

                  Thanks Ian,

                   

                  I think I could make that work, but think I'm close to a different approach.

                   

                  Since I have the type from my first form field named which, and I'll have the make from the second field, can't I just bind the third using two bind parameters?

                   

                  Field 1 name is "which"

                  Field 2 name is "make"

                   

                  Field 3:  bind="cfc:model.getdata09.getmodels(which={which@none},make={make@click})"

                   

                  However, I get an error that motorcycle is undefined.

                   

                  Here's my function:

                     <!--- Get model by make name --->
                     <cffunction name="getmodels" access="remote" returnType="query">
                        <cfargument name="make" type="string" required="true">
                    <cfargument name="which" type="string" required="true">
                       
                        <cfset var data="">
                        <cfset which ="#arguments.which#">  
                        <cfif which is "car">
                           <cfset THIS.table="cardata">
                                  </cfif>
                      
                        <cfif which is motorcycle>
                           <cfset THIS.table="motorcycledata">
                                  </cfif>

                   

                        <!--- Get data --->
                        <cfquery name="data" datasource="#THIS.dsn#"   >
                        SELECT distinct model
                       FROM #THIS.table#
                        WHERE make = <cfqueryPARAM value = "#ARGUMENTS.make#"
                     CFSQLType = "CF_SQL_VARCHAR">
                        ORDER BY model
                        </cfquery>

                   

                      <!--- And return it --->
                        <cfreturn data>
                     </cffunction>

                  • 6. Re: CFSELECT - Binding to two fields?
                    cfwild Level 1

                    Hi,

                     

                    I'm thinking that you should have one table.  In that table you a columns {which, make, model, year}.  You keep a seperate record for each combination.  So in the db, you have a row for Auto, Honda, Civic, 2010.  You have another row for Motorcycle, Honda, CRX1000, 1988.  Now when you run your queries, you first ask for distinct which(s).  This will pull back Auto & Motorcycle.  Then when you run the next query, you again use distict, but have a where clause = which.  Then to get your makes, you again use distinct, but have where = which AND = make.

                     

                    cfwild

                    • 7. Re: CFSELECT - Binding to two fields?
                      cfwild Level 1

                      Hi,

                       

                      A couple of other thoughts:  you don't have to use the exact code, just the concept.

                       

                      on your first select/radio button:

                       

                      use

                      bind="cfc:detail.getWhich()" and use the bindonLoad="true"

                       

                      on the second select

                       

                      use

                      bind="cfc:detail.getMake({Which})   no bindonLoad

                       

                      on the third select

                       

                      use

                      bind="cfc:detail.getModel({Which},{Make})"  no bindonLoad

                       

                      Your current code looks ok, to debug, you might try a cfdump tag and see what is being returned.  You could also write your code like this:

                       

                      <cfset which ="#arguments.which#">  
                            <cfif which is "car">
                               <cfset THIS.table="cardata">
                                      </cfif>
                          
                            <cfif which is motorcycle>
                               <cfset THIS.table="motorcycledata">
                                      </cfif>

                       

                      goes to:

                       

                      <cfif ARGUMENTS.which EQ "car">

                           <cfset THIS.table = "cardata" />

                      <cfelse>

                           <cfset THIS.table = "motorcycledata" />

                      </cfif>

                       

                      good luck

                       

                      cfwild