• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Triple related select

Participant ,
Aug 04, 2011 Aug 04, 2011

Copy link to clipboard

Copied

I read Raymond Camden's blog post about triple related selects and I'm trying to implement a solution of my own.  I can only get the first dropdown to populate and am getting three bind errors "Bind Failed, element not found: SelState ..." for the first two and then "Bind Failed, element not found: SelDestination ... " for the last.

My code looks accurate (to me anyway) but clearly its not working.

Can someone please help me debug this?

<cfform name="Localiza">
<table>
<tr>
<td width="100">State:</td>
<td width="150">
<cfselect name="SelState" bind="cfc:_fc_cfc_triple.GetState()"
        display="misc_state" value="misc_state" BindOnLoad="true"/></td></tr>
<tr>
<td width="100">County:</td>
<td width="150">
<cfselect name="SelCounty" bind="cfc:_fc_cfc_triple.GetCounty({SelState})"
        display="destination_county" value="destination_county" BindOnLoad="false"/></td></tr>
<tr>
<td width="100">Destination:</td>
<td width="150">
<cfselect name="SelDestination" bind="cfc:_fc_cfc_triple.GetDestination({SelState},{SelDestination})"
        display="destination_name" value="destination_id" BindOnLoad="false"/></td></tr>
</table>
</cfform>

The form is above, the cfc (_fc_cfc_triple.cfc) is below

<cfcomponent>
   
    <cffunction name="GetState" access="remote" returnType="query">
        <cfquery name="LstState" datasource="mydsn">
        SELECT misc_state
        FROM states_misc
        ORDER BY misc_state ASC
        </cfquery>
        <cfreturn LstState>
    </cffunction>
   
    <cffunction name="GetCounty" access="remote" returnType="query">
        <cfargument name="misc_state" type="any" required="true">
        <cfif #ARGUMENTS.misc_state# EQ "">
            <cfset LstCounty="">
        <cfelse>
            <cfquery name="LstCounty" datasource="mydsn">
            SELECT DISTINCT destination_county
            FROM destinations
            WHERE destination_state = #ARGUMENTS.misc_state#
            ORDER BY destination_county ASC
            </cfquery>
        </cfif>
        <cfreturn LstCounty>
    </cffunction>
   
    <cffunction name="GetDestination" access="remote" returnType="query">
        <cfargument name="misc_state" type="any" required="true">
        <cfargument name="destination_county" type="any" required="true">
        <cfif #ARGUMENTS.misc_state# EQ "" OR #ARGUMENTS.destination_county# EQ "">
            <cfset LstDestination="">
        <cfelse>
            <cfquery name="LstDestination" datasource="mydsn">
            SELECT destination_id,destination_name
            FROM destinations
            WHERE destination_state = #ARGUMENTS.misc_state#
            AND destination_county = #ARGUMENTS.destination_county#
            ORDER BY destination_name ASC
            </cfquery>
        </cfif>
        <cfreturn LstDestination>
    </cffunction>

</cfcomponent>

Views

7.3K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Aug 04, 2011 Aug 04, 2011

Copy link to clipboard

Copied

All that I initially see incorrect is the last cfselect bind, it should be:

<cfselect name="SelDestination" bind="cfc:_fc_cfc_triple.GetDestination({SelState},{SelCounty})"
        display="destination_name" value="destination_id" BindOnLoad="false"/>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Aug 04, 2011 Aug 04, 2011

Copy link to clipboard

Copied

Changed the SelDestination to SelCounty in the last cfselect... Still getting the same three bind failed errors.

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Aug 04, 2011 Aug 04, 2011

Copy link to clipboard

Copied

For the last two selects, if the arguments are empty it will probably fail as the return type is expecting a query object and not a string. You can either change the functions as follows to make them return query objects when the arguments are empty, or make sure that there is no empty value in your State drop-down upon first load.

The code works for me otherwise.

<cffunction name="GetCounty" access="remote" returnType="query">
        <cfargument name="misc_state" type="any" required="true">
        <cfif #ARGUMENTS.misc_state# EQ "">
             <cfset LstCounty = queryNew("destination_county","varchar")>
        <cfelse>
            <cfquery name="LstCounty" datasource="mydsn">
            SELECT DISTINCT destination_county
            FROM destinations
            WHERE destination_state = #ARGUMENTS.misc_state#
            ORDER BY destination_county ASC
            </cfquery>
        </cfif>
        <cfreturn LstCounty>
    </cffunction>
   
    <cffunction name="GetDestination" access="remote" returnType="query">
        <cfargument name="misc_state" type="any" required="true">
        <cfargument name="destination_county" type="any" required="true">
        <cfif #ARGUMENTS.misc_state# EQ "" OR #ARGUMENTS.destination_county# EQ "">
            <cfset LstDestination = queryNew("destination_id,destination_name","integer,varchar")>
        <cfelse>
            <cfquery name="LstDestination" datasource="mydsn">
            SELECT destination_id,destination_name
            FROM destinations
            WHERE destination_state = #ARGUMENTS.misc_state#
            AND destination_county = #ARGUMENTS.destination_county#
            ORDER BY destination_name ASC
            </cfquery>
        </cfif>
        <cfreturn LstDestination>
    </cffunction>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Aug 04, 2011 Aug 04, 2011

Copy link to clipboard

Copied

Lots of cleanup to do... there is blood everywhere from me banging my head on the desk.

 

Kidding of course.  This new change did nothing... still the same three bind errors as my original post.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Aug 04, 2011 Aug 04, 2011

Copy link to clipboard

Copied

I continue to tinker with this but I cannot understand why all three binds fail.  I changed my initial query so that a result has to be displayed and that didn't work.

I'm beginning to wonder if I should abandon my approach and just use the downloaded code from the Jedi's site and fill that .cfm include with real data??

I'd rather not do that.  Can anyone see what is wrong with my approach?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Aug 05, 2011 Aug 05, 2011

Copy link to clipboard

Copied

I'd rather not do that.  Can anyone see what is wrong with my approach?

Probably trying to keep all the balls in the air before you've learned how to juggle could be the issue here.

Do this:

* Abandon the code you currently have;

* start again.  Write some code that gets a single bind working to spec.  Do not worry about your ultimate requirement, just hard-code mark-up & sample data and everything you need to make the code as simple as possible, and as self-contained as possible;

* copy that code, putting the original aside so you can return to it if you need to;

* on the copy of the code, augment it to get the second bind working.  Do not think about the third bind until you have the second one working;

* copy that code and save a back-up of it;

* augment the copy to get the third bind working;

* put that code aside;

* apply the techniques that you should now be comfortable with to your actual requirement, implementing one enhancement at a time until it works 100% before moving onto the next bit.

At no point move to the next step before the code in the current step is working 100% and you understand it 100%.  At any step you cannot progress, post the code here, post the results of running the code here, post any CF or JS error messages here.  The code you post here should be able to be copy and pasted from your post, saved to file(s) and run.  So no DB requirements, no calls to methods you don't provide the code for, etc.

--

Adam

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Aug 05, 2011 Aug 05, 2011

Copy link to clipboard

Copied

That's actually not the issue in this case.  I already have several related selects on my site.

I wanted to understand why the code I pasted in this thread is not accurate.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Aug 05, 2011 Aug 05, 2011

Copy link to clipboard

Copied

I have been tinkering with the data that's in the included .cfm file that contains the <cfscript>.

Things are working well for me... However, my last dropdown is the problem child.  The reason I wanted a triple select in the first place is that my destinations table (the third dropdown) has over 300k rows.  So I can't load it up front in the <cfscript> because it takes way too long.

What can I do?  Is there some way I can wait to query the table until the first two dropdowns are populated?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Aug 05, 2011 Aug 05, 2011

Copy link to clipboard

Copied

Answered my own q.  Man... that was fun.  Ok first of all, thank you Raymond for posting the triple select blog post on your Jedi blog.  What I ended up doing was altering the script so that I had query data returned for the first two selects.  Since my third select was the one that queried that large table, I didn't use the script but instead queried the database.  That's it!  Simple and working well.

Please let me know if you'd like me to post the code and I will.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Aug 05, 2011 Aug 05, 2011

Copy link to clipboard

Copied

Good work.

It'd probably be helpful for other people encountering the same problem if you posted your code.

--

Adam

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Aug 05, 2011 Aug 05, 2011

Copy link to clipboard

Copied

Adam Cameron. wrote:

Good work.

It'd probably be helpful for other people encountering the same problem if you posted your code.

--

Adam

    <cfquery name="get_states" datasource="mydsn">
    SELECT state_id,state
    FROM states
    ORDER BY state
    </cfquery>
    <cfquery name="get_counties" datasource="mydsn">
    SELECT county_id,state_id,county
    FROM counties
    ORDER BY state_id,county_id
    </cfquery>
    <cfquery name="get_destinations" datasource="mydsn" maxrows="1">
    SELECT state_id,county_id,destination_id,destination_name
    FROM destinations
    WHERE destination_id = '0'
    ORDER BY state_id,county_id
    </cfquery>
   
    <cfscript>
    State = QueryNew("state_id,state","Integer,Varchar");
   
        for(i=1; i LTE get_states.RecordCount; i=i+1){
            newRow = QueryAddRow(State);
                QuerySetCell(State, "state_id", #get_states.state_id#);
                QuerySetCell(State, "state", "#get_states.state#");
        }
   
    County = QueryNew("state_id, county_id, county", "Integer, Integer, Varchar");
   
        for(i=1; i LTE get_counties.RecordCount; i=i+1){
            newRow = QueryAddRow(County);
                QuerySetCell(County, "state_id", #get_counties.state_id#);
                QuerySetCell(County, "county_id", #get_counties.county_id#);
                QuerySetCell(County, "county", "#get_counties.county#");
        }
   
    Destination = QueryNew("state_id, county_id, destination_id, destination_name", "Integer, Integer, Integer, Varchar");
   
        for(i=1; i LTE get_destinations.RecordCount; i=i+1){
            newRow = QueryAddRow(Destination);
                QuerySetCell(Destination, "state_id", #get_destinations.state_id#);
                QuerySetCell(Destination, "county_id", #get_destinations.county_id#);
                QuerySetCell(Destination, "destination_id", #get_destinations.destination_id#);
                QuerySetCell(Destination, "destination_name", "#get_destinations.destination_name#");
        }
    </cfscript>
   
    ===================================================================
   
    <cffunction name="GetDestination" access="remote" returnType="query">
    <cfargument name="State" type="any" required="true">
    <cfargument name="County" type="any" required="true">
    <cfif ARGUMENTS.State EQ "" OR ARGUMENTS.County EQ "">
        <cfset LstDestination = QueryNew("state_id, county_id, destination_id, destination_name", "Integer, Integer, Integer, Varchar")>
    <cfelse>
        <cfquery name="LstDestination" datasource="mydsn">
            SELECT state_id, county_id, destination_id, destination_name
            FROM destinations
            WHERE state_id = #ARGUMENTS.State# AND
            county_id = #ARGUMENTS.County#
            ORDER BY destination_name
        </cfquery>
    </cfif>
    <cfreturn LstDestination>
    </cffunction>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Nov 27, 2012 Nov 27, 2012

Copy link to clipboard

Copied

Hi Idesdema,

I'm working on a year/make/model/option/option2 related search and came across your post. I know its been a while but do you have the form that goes with the post so i can take a look at how to reference the cfc properly?

I have tried so many tutorisals and none have worked. At this point i built a database to match your code so i can test to make sute i have a good idea before i convert it to my automotive app. Thanks!

Cheers,

Aaron

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Nov 27, 2012 Nov 27, 2012

Copy link to clipboard

Copied

Nevermind, found it!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Nov 27, 2012 Nov 27, 2012

Copy link to clipboard

Copied

Ok cool.  I have it if you need it.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Nov 27, 2012 Nov 27, 2012

Copy link to clipboard

Copied

Hi,

Did you ever get a error on the  returnType="query"? It says "The value of the attribute QUERY is invalid. The 'Query' attribute must be defined if the 'Value', 'Display', or 'Group' attributes are defined."

    <cffunction name="GetDestination" access="remote" returnType="query">

    <cfargument name="State" type="any" required="true">

    <cfargument name="County" type="any" required="true">

    <cfif ARGUMENTS.State EQ "" OR ARGUMENTS.County EQ "">

        <cfset LstDestination = QueryNew("state_id, county_id, destination_id, destination_name", "Integer, Integer, Integer, Varchar")>

    <cfelse>

        <cfquery name="LstDestination" datasource="" username="" password="">

            SELECT state_id, county_id, destination_id, destination_name

            FROM destinations

            WHERE state_id = #ARGUMENTS.State# AND

            county_id = #ARGUMENTS.County#

            ORDER BY destination_name

        </cfquery>

    </cfif>

    <cfreturn LstDestination>

    </cffunction>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Nov 27, 2012 Nov 27, 2012

Copy link to clipboard

Copied

Are you including the .cfm file in the cfc that initially declares that LstDestination?  Something like...

 

(in the included file before your functions)

 

LstDestination = QueryNew("state_id, county_id, destination_id, destination_name", "Integer, Integer, Integer, Varchar");

    for(i=1; i LTE get_destinations.recordcount; i=i+1){

    newRow = QueryAddRow(LstDestination);

    QuerySetCell(LstDestination,"state_id", #get_destinations.state_id#);

     QuerySetCell

       and so on for the other columns...

 

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Nov 27, 2012 Nov 27, 2012

Copy link to clipboard

Copied

I have it in my cfc named "_fc_cfc_triple.cfc" saved in the same directory as the form. I haven't really used CFC's until this week. How do i include it properly? I wonder if its even loading it.

I thought maybe the form was calling it in the select tag:

<cfform>

<table>

<tr>

<td width="100">State:</td>

<td width="150">

<cfselect name="SelState" bind="cfc:_fc_cfc_triple.GetState()" display="misc_state" value="misc_state" BindOnLoad="true"/></td></tr>

<tr>

<td width="100">County:</td>

<td width="150">

<cfselect name="SelCounty" bind="cfc:_fc_cfc_triple.GetCounty({SelState})" display="destination_county" value="destination_county" BindOnLoad="false"/></td></tr>

<tr>

<td width="100">Destination:</td>

<td width="150">

<cfselect name="SelDestination" bind="cfc:_fc_cfc_triple.GetDestination({SelState},{SelDestination})" display="destination_name" value="destination_id" BindOnLoad="false"/></td></tr>

</table>

</cfform>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Nov 27, 2012 Nov 27, 2012

Copy link to clipboard

Copied

CFC:

    <cffunction name="GetDestination" access="remote" returnType="query">

    <cfargument name="State" type="any" required="true">

    <cfargument name="County" type="any" required="true">

    <cfif ARGUMENTS.State EQ "" OR ARGUMENTS.County EQ "">

        <cfset LstDestination = QueryNew("state_id, county_id, destination_id, destination_name", "Integer, Integer, Integer, Varchar")>

    <cfelse>

        <cfquery name="LstDestination" datasource="" username="" password="">

            SELECT state_id, county_id, destination_id, destination_name

            FROM destinations

            WHERE state_id = #ARGUMENTS.State# AND

            county_id = #ARGUMENTS.County#

            ORDER BY destination_name

        </cfquery>

    </cfif>

    <cfreturn LstDestination>

    </cffunction>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Nov 27, 2012 Nov 27, 2012

Copy link to clipboard

Copied

The code I roughed out in #16 is what should in a seperate .cfm file that is included within your .cfc.

So, my code is very similar to yours, but I have a <cfinclude template="..."> in the .cfc file that first establishes the three queries for when the select boxes have yet to be changed.  You should have something similar.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Nov 27, 2012 Nov 27, 2012

Copy link to clipboard

Copied

idesdema wrote:

The code I roughed out in #16 is what should in a seperate .cfm file that is included within your .cfc.

So, my code is very similar to yours, but I have a <cfinclude template="..."> in the .cfc file that first establishes the three queries for when the select boxes have yet to be changed.  You should have something similar.

Thank you. Do i wrap that with a javascript tag?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Nov 27, 2012 Nov 27, 2012

Copy link to clipboard

Copied

Run your cfqueries, then wrap the QueryNew statements in tags.

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Nov 27, 2012 Nov 27, 2012

Copy link to clipboard

Copied

I think you are better off with 3 functions, one for each select.  It would be a lot simpler.

Also, if you read the entire thread, you will notice some excellent advice from Adam Cameron.  It's the one that mentions juggling.  The approach he suggests is the one I usually take.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Nov 27, 2012 Nov 27, 2012

Copy link to clipboard

Copied

That is how mine is setup... one function for each dropdown.  I'm assuming Aaron's is that way as well.  And for the record I did take Adam's advice (long long ago when I was working on this) and that's how I arrived at my triple select.  It works well and is efficient.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Nov 28, 2012 Nov 28, 2012

Copy link to clipboard

Copied

idesdema wrote:

That is how mine is setup... one function for each dropdown.  I'm assuming Aaron's is that way as well.  And for the record I did take Adam's advice (long long ago when I was working on this) and that's how I arrived at my triple select.  It works well and is efficient.

Thanks, mine is set up that way as well. I'm going to start over and go peice by piece and see if that makes more sense.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation