Triple related select

Report · Aug 04, 2011

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>

Report · Nov 28, 2012

Let me know if u want my code if u get stuck. I'll zip and send.

Report · Nov 28, 2012

idesdema wrote:
Let me know if u want my code if u get stuck. I'll zip and send.

Yes Please...

I have broken it down to the following, but i'm still confused. I don't get errors anymore, but i don't get any select options either.

index.cfm:

<table>

<tr>

<td width="100">Select Year:</td>

</td>

</tr>

</table>

</cfform>

include QryYears.cfm:

SELECT DISTINCT YearRange

FROM ExactFit2012

ORDER BY YearRange

</cfquery>

YearRange = QueryNew("YearRange","Varchar");

for(i=1; i LTE getYearRange.RecordCount; i=i+1){

newRow = QueryAddRow(YearRange);

QuerySetCell(YearRange, "YearRange", "#GetYearRange.YearRange#");

}

</cfscript>

CFC:

SELECT DISTINCT YearRange

FROM ExactFit2012

ORDER BY YearRange

</cfquery>

</cfif>

</cffunction>

I don't know if i'm supposed to have two queries that are the same, or if the second query was supposed to be for my next set of select options.

Report · Nov 28, 2012

Nevermind on the last sentance, i see its just a if statment on the argument. Going codeblind!

Report · Nov 28, 2012

The first part, the form:

<cfform name="destination_jump" method="post" action="redirect.cfm?redirect_indicator=destination_jump">
<table cellpadding="4" cellspacing="0" border="0">
<tr>
    <tr> 
        <td align="right" valign="top">
        State:
        </td>
        <td align="left">
        <cfselect class="dropdown" name="jump_state" bind="cfc:_cfc_triple.get_states()" display="state" value="state_id" bindonload="true"/>
        </td>
    </tr>
    <tr> 
        <td align="right" valign="top">
        County: 
        </td>
        <td align="left">
        <cfselect class="dropdown" name="jump_county" bind="cfc:_cfc_triple.get_counties({jump_state})" display="county" value="county_id"/>
        </td>
    </tr>
    <tr> 
        <td align="right" valign="top">
        Destination:
        </td>
        <td align="left">
        <cfselect class="dropdown" name="jump_desty" bind="cfc:_cfc_triple.get_destinations({jump_state},{jump_county},{activity})" display="destination_name" value="destination_id" required="yes" message="Please select a destination."/>
        </td>
    </tr>
    <tr> 
        <td valign="top" align="center" colspan="2">
        <cfinput type="submit" validate="submitonce" name="save" value="GO">
        </td>
    </tr>
</table>
</cfform>

The second part, the cfc file:

<cfcomponent>
    
    <cfinclude template="cfc-includes/_cfc_triple.cfm">
    
    <cffunction name="get_states" access="remote" returnType="query">
        <cfquery name="LstState" dbtype="query">
        SELECT state_id, state
        FROM State
        ORDER BY state
        </cfquery>
        <cfreturn LstState>
    </cffunction>
    
    <cffunction name="get_counties" access="remote" returnType="query">
        <cfargument name="State" type="any" required="true">
        <cfif ARGUMENTS.State EQ "">
            <cfset LstCounty = QueryNew("state_id, county_id, county", "Integer, Integer, Varchar")>
        <cfelse>
            <cfquery name="LstCounty" dbtype="query">
            SELECT state_id, county_id, county
            FROM County
            WHERE state_id = #ARGUMENTS.State#
            ORDER BY county
            </cfquery>
        </cfif>
        <cfreturn LstCounty>
    </cffunction>
    
    <cffunction name="get_destinations" access="remote" returnType="query">
        <cfargument name="State" type="any" required="true">
        <cfargument name="County" type="any" required="true">
        <cfargument name="activity" 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 DISTINCT state_id, county_id, destination_id, CONCAT_WS(' - ',destination_name,destination_county) destination_name
            FROM #ARGUMENTS.activity#_destinations
            WHERE state_id = #ARGUMENTS.State# AND
            county_id = #ARGUMENTS.County#
            ORDER BY destination_name
            </cfquery>
        </cfif>
        <cfreturn LstDestination>
    </cffunction>
    
</cfcomponent>

And lastly, the cfm include for the cfc:

<cfquery name="get_states" datasource="mydsn">
SELECT state_id,state
FROM tbl_states
ORDER BY state
</cfquery>
<cfquery name="get_counties" datasource="mydsn">
SELECT county_id,state_id,county
FROM tbl_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 tbl_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>

Report · Nov 28, 2012

Thank you very much!

Can I ask if you ever got the following error on the query using the " display="state" value="state_id" " within the select tags?

On my last couple tries I got the "The value of the attribute QUERY is invalid. The 'Query' attribute must be defined if the 'Value', 'Display', or 'Group' attributes are defined." error so I went to Ray's blog and another developer had taken them out completely to fix the error, even though Ray never got that error on his local servers. I'm curious as to why we got them but ray and others didn't.

We are using CF8 but we are shared hosting so I’m thinking it might be some preference in the CF settings.

Thanks!

Report · Nov 28, 2012

Nevermind, i think i see why now, they are database fields within the display and value attributes

Report · Nov 28, 2012

Hi Again,

All of my data is in one table with one ID so i needed to modify the included page and the CFC queries and the script below to refelct that. I don't get errors but still no data being inserted into the dropdowns.

<cfquery name="get_years" datasource="DSN">
SELECT YearRange
FROM ExactFit2012
ORDER BY YearRange
</cfquery>
<cfquery name="get_makes" datasource="DSN">
SELECT make
FROM ExactFit2012
ORDER BY Make
</cfquery>
<cfquery name="get_models" datasource="DSN">
SELECT model
FROM ExactFit2012
ORDER BY model
</cfquery>
Origional Code:
<cfquery name="get_states" datasource="mydsn">
SELECT state_id,state
FROM tbl_states
ORDER BY state
</cfquery>
<cfquery name="get_counties" datasource="mydsn">
SELECT county_id,state_id,county
FROM tbl_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 tbl_destinations
WHERE destination_id = '0'
ORDER BY state_id,county_id
</cfquery>

<cfscript>
Year = QueryNew("YearRange","Varchar");
    for(i=1; i LTE get_years.RecordCount; i=i+1){
        newRow = QueryAddRow(Year);
        QuerySetCell(Year, "YearRange", "#get_years.YearRange#");
    }
make = QueryNew("YearRange, make", "Varchar, Varchar");
    for(i=1; i LTE get_makes.RecordCount; i=i+1){
        newRow = QueryAddRow(make);
        QuerySetCell(make, "YearRange", #get_makes.YearRange#);
        QuerySetCell(make, "Make", #get_makes.make#);
    }
Model = QueryNew("YearRange, make, model", "Varchar, Varchar, Varchar");
    for(i=1; i LTE get_models.RecordCount; i=i+1){
        newRow = QueryAddRow(model);
        QuerySetCell(model, "YearRange", #get_models.YearRange#);
        QuerySetCell(model, "make", #get_models.make#);
        QuerySetCell(model, "model", #get_models.model#);
    }
</cfscript>
Origional Code:
<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>

I deleted all the references to the _id fields from the code so that it might work with my data but i think i may have needed that.

Report · Nov 28, 2012

Dan Bracuk wrote:
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.

Thank you, i will start with a single select this morning and try and get it working properly.

Report · Nov 28, 2012

Hi Idesdema & Dan,

Back Again, still not working for me.

I tried breaking it down to a single select for learning purposes. I keep getting the error "The value of the attribute QUERY is invalid. The 'Query' attribute must be defined if the 'Value', 'Display', or 'Group' attributes are defined." unless i remove the "display" and "value" attributes inside of the select tag. When i do it stops the error but i get no results. For some reason i think those need to be there as a reference to the data and could be the reason i'm not seeing results, but i'm not certain. What do you think?

Form:

<cfform name="_jump" method="post" action="">
<table cellpadding="4" cellspacing="0" border="0">
<tr>
    <tr>
        <td align="right" valign="top">
        Year Range:
        </td>
        <td align="left">
        <cfselect class="dropdown" name="jump_state" bind="cfc:_cfc_triple.get_states()" display="state" value="state_id" bindonload="true"/>
        </td>
    </tr>
</table>
</cfform>

CFC:

<cfcomponent>
    <cfinclude template="_cfc_triple.cfm">
    <cffunction name="get_states" access="remote" returnType="query">
        <cfquery name="LstState" dbtype="query" datasource="GriffinDSN">
        SELECT ID, YearRange
        FROM ExactFit2012
        ORDER BY YearRange
        </cfquery>
        <cfreturn LstState>
    </cffunction>
</cfcomponent>

Include:

<cfquery name="get_states" datasource="GriffinDSN">
SELECT ID, YearRange
FROM ExactFit2012
ORDER BY YearRange
</cfquery>
<cfscript>
State = QueryNew("ID,YearRange","Integer,Varchar");
    for(i=1; i LTE get_states.RecordCount; i=i+1){
        newRow = QueryAddRow(State);
        QuerySetCell(State, "id", #get_states.id#);
        QuerySetCell(State, "YearRange", "#get_states.YearRange#");
    }
</cfscript>

Report · Nov 28, 2012

Your error message is being generated because you are running a query of queries against a database table.

It appears that you are running way too much code in order to get what you want. For example, you are running a database query called get_states and then looping though it to produce a CF query called state. I'm not sure what that's all about.

I also don't know why there is an included file for the query. If you need a query object that's available to more than one function, just run the code in the cfc file, outside of any function.

Going back to the juggling analogy, if it were me, I'd test my cfc functions with either cfinvoke or createobject syntax before attempting to use them with bind syntax.

Report · Nov 28, 2012

Dan Bracuk wrote:
Your error message is being generated because you are running a query of queries against a database table.
It appears that you are running way too much code in order to get what you want. For example, you are running a database query called get_states and then looping though it to produce a CF query called state. I'm not sure what that's all about.
I also don't know why there is an included file for the query. If you need a query object that's available to more than one function, just run the code in the cfc file, outside of any function.
Going back to the juggling analogy, if it were me, I'd test my cfc functions with either cfinvoke or createobject syntax before attempting to use them with bind syntax.

Thanks! I will keep trying

Report · Nov 28, 2012

Hi Dan i tried using the CFInvoke Below. I haven't tried using these types of tags before and am googling examples to modify and use.

Here is mine:

CFC: Named Components

<cfcomponent displayname="Years" hint="ColdFusion Component for Getting Vehicle Years">
<cffunction name="retrieveYears" hint="Gets all years from the database" returntype="query">
   <cfquery name="getYears" datasource="GriffinDSN">
        SELECT ID, YearRange
        FROM ExactFit2012
        ORDER BY YearRange
   </cfquery>
   <cfreturn getYears>
</cffunction>
</cfcomponent>

Invoke:

<cfinvoke component="components.years" method="retrieveYears" returnvariable="allYears"></cfinvoke>
<cfoutput query="allYears">
#YearRange#
</cfoutput>

Adobe Community

Triple related select