Where is the Coldfusion forum?

[EwokStud]

I've not been posting to the Coldfusion forum in months.  I cannot find how to ask a question in the CFML department.  Please help.

[BKBK]

Your current post is in the Coldfusion forum. So, ask your next question as you did this one.

[EwokStud]

I am trying to achieve having a list of customers displayed in the bottom portion of my webpage, filtered according to the cityID, located at the top of my page.  I understand the group method, but can I link two separate query resultsets, according to the name of the two CFTABLES or the linkage of two associated number fields (the other method)?

ie. CFTABLE QUERY=qry_cities    I'd like to use a dropdown to change the city, and the have the second table refresh.

CFTABLE QUERY=qry_customers

[WolfShade]

You marked this question as answered, so it's less likely that anyone will respond.

But, since I'm here.. 

Don't use CFTABLE.  It has been deprecated.

Do a Google search for "ColdFusion related selects".  I found this blog post by Raymond Camden for precisely what you seek.  (Just a heads-up: it requires jQuery.)

V/r,

^_^

[BKBK]

As WolfShade says, cftable has been deprecated. Nevertheless, I can think of a very easy way to implement what you want. The demo is as follows.

1) Create the following database table, with columns cityID(varchar 10), custID(varchar 10) and custName(varchar 30). Call it customer:

2) Store the following 3 files within the same directory under the web root:

cities.cfm

<!--- Code at top of page --->

<cfif isdefined("form.city")>

    <cfset cityCustomerObject = createobject("component","CityCustomer")>

    <cfset customerQuery = cityCustomerObject.getCustomers(form.city)>

</cfif>

<cfoutput><form action="#cgi.script_name#" method="post"></cfoutput>

<select name="city" id="city" onchange="this.form.submit()">

    <option value="">Select city</option>

    <option value="ct1">city 1</option>

    <option value="ct2">city 2</option>

    <option value="ct3">city 3</option>

</select>

</form>

<!--- Code at bottom of page --->

<form action="actionPage.cfm" method="post">

<select name="customer" id="customer">

    <option value="">Select customer</option>

    <cfif isdefined("form.city")>

        <cfoutput query="customerQuery">

             <option value="#custID#">#custname#</option>

        </cfoutput>

    </cfif>  

</select><br>

<input type="submit" name="sbmt" value="Send">

</form>

CityCustomer.cfc

<cfcomponent>

    <cffunction name="getCustomers" returntype="query">

        <cfargument name="cityID" >

        <cfset var customers = queryNew("")>

         <!--- dsn = name of datasource in which customer table stored. dsn should be registered in the administrator--->

        <cfquery name="customers" datasource="dsn">

            select custID, custName

            from customer

            where cityID = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.cityID#">

        </cfquery>

        <cfreturn customers>

    </cffunction>

</cfcomponent>

actionPage.cfm

Selected customer ID: <cfif isdefined("form.customer")><cfoutput>#form.customer#</cfoutput></cfif>

Get the ball rolling by opening the page cities.cfm in the browser. The rest follows from there.

[Dave Ferguson]

Couple of points here..

Use structkeyexists instead of isDefined.  It is a lot more accurate and is less likely to throw false responses.

structKeyExists(form, 'city')

Form submits to an onchange on a select is not really a good idea.  If the user makes a mistake the form is submitted and they have to go back.  It is better to put a go button or icon next to the select to submit it.

I wouldn't do this below.  It creates excess overhead because cf has to create an empty query object that it will then just replace.

<cfset var customers = queryNew("")>

You can easily just do this instead and get the same result

<cfset var customers = ''>

Overall, for a better user experience, I would do the customer loading via ajax.  Just doesn't make sense to reload the whole page just because a select was changed.

[BKBK]

Dave Ferguson wrote:

Use structkeyexists instead of isDefined. It is a lot more accurate and is less likely to throw false responses.

structKeyExists(form, 'city')

I would disagree. In this case, isDefined("form.city") is accurate and unlikely to throw false responses. The arguments I have seen against its use have been anecdotal, with few hard facts.

I wouldn't do this below. It creates excess overhead because cf has to create an empty query object that it will then just replace.

<cfset var customers = queryNew("")>

You can easily just do this instead and get the same result

<cfset var customers = ''>

True, but the story is not that simple. There is a trade-off. The queryNew("") is better self-documentation. Customers is a query, not a string. In any case, the overhead is small.

Form submits to an onchange on a select is not really a good idea. If the user makes a mistake the form is submitted and they have to go back. It is better to put a go button or icon next to the select to submit it.

True. There are lots of other things you can add. This is just a demo.

Overall, for a better user experience, I would do the customer loading via ajax. Just doesn't make sense to reload the whole page just because a select was changed.

You are right, if what you want is to send the data to the server without reloading the page. However, here as elsewhere in software, there is no gospel. It remains a choice. For example, someone else may choose to reload the page to perform server-side validation.

In any case, my main consideration was simplicity. This.form.submit() - done. Just a demo to illustrate a concept! Says so on the tin.

[EwokStud]

Well, let's back up.  Let's say I am new to Coldfusion after version MX7.  I've heard of a cfc document, and understand it replaces the application.cfm, right? I understand that the tags in cfc are almost all different; it's like learning CF all over again for me.  My need - for now - is as simple as taking one CFQUERY, and using it to accomplish a very quick filtration on a webpage (template).  I mentioned the knowledge I have of joining two tables together, and using the group attribute to achieve two displays from the one query.  The goal I seek is to achieve a basic display, using two separate tables (and 2 queries) to achieve the same effect.  i.e. Form 1 and Form 2 are synchronized (linked) according to a common field value.  I appreciate the rocket science, but how do I synchronize two forms, each with their own query?

[EwokStud]

Replying to myself, I ask the monitors if I said something wrong?

[BKBK]

Piling up new questions on top of previous ones is false economy. It can, in the end, lead to confusion even after all the questions have been answered.

In any case, it can seem like you were using the original question as bait. Which can seem like a lack of courtesy.

In short, you posted three times previously. Your 3 posts contained 3 different questions. You failed to acknowledge the answers that responders gave you. That ain't cool, bro.

You should ask separate questions in separate threads. Acknowledge that a question has been answered. Then, if necessary, move on and start a new thread.

[EwokStud]

not bait...it was the real question.  Because my initial post wasn't phrased properly, the answer I sought was still lurking in the twilight.  Having asked the same question a second way, it sounded like another question.  The two tables, cities and customers have a third table, for normalization purposes.  As the simplest solution is often the best in many cases, let me ask if using the GROUP attribute in the main query should still be used, and then link the bottom query result (customers) to the first query result (single record at top of page), using the ID fields.

Tables: cities (cityID, cityNm, cityStt_), customers (cstmrID, cstmrNmLast, cstmrNmFirst), and finally cstmrDir (cstmrDirID, cstmrDirStreet, cstmrDirCity [LongInteger], cstmrDirState [LongInteger]).  Naturally, there are other tables, states, etc.).

Here are my current babies:

Here's my Main Query (I don't know if I need a second one):

SELECT cities.*, cstmrs.*, cstmrDir.*, [cstmrNmLast] & ', ' & [cstmrNmFirst] AS cstmrNm_z

FROM ((cstmrDir RIGHT JOIN cities ON cstmrDir.cstmrDirCity_ = cities.cityID) LEFT JOIN stt ON cities.cityStt_ = stt.sttID) LEFT JOIN cstmrs ON cstmrDir.cstmrDir_ID = cstmrs.cstmrID

WHERE (((cstmrDir.cstmrDir_ID) Is Not Null));

Here's the Goal (done in MS Word):