How can I bind data from a select box from a different column than the value and display of the select box?

I have a table of area schools. The columns are:

School_ID
School_Name
School_Address
School_City
School_State
School_Zip
School_Principal

I want to create a select box that only displays the school name, but when a school name is selected I want the other information (Address, City, State, Zip, and Principal) to populate text fields below it. Here's my humble beginning:

<cfquery name="getSchoolname" datasource="#APPLICATION.datasource#">
SELECT *
FROM tblSchool
</cfquery>

<cfselect name="PI_School"
                         query="getSchoolname"
                         value="School_Name"
                         selected="#getPI.PI_School#"  (this refers to another query that retrieves the currently selected school)
                         display="School_Name">
</cfselect>

I know that I could change the value of the select box to something like "School_Address" and then bind it to the text field like so:

<cfinput type="text" name="whatever" bind="{PI_School.selectedItem.data}">

However, I need the value to remain "School_Name". Plus, that doesn't allow me to get the other column data anyway.

Thanks in advance for your help.
yellowsecondAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RCorfmanCommented:
Are you talking about dynamically as the user selects different schools with the form not being refreshed? If so, this is something that you need to use javascript for, not cold fusion.

You would have to output all the data required into javascript tables, then tie them to an index and use javascript procedures to change the other fields.

If you want to update it one time when the form is output, this is much easier.
0
yellowsecondAuthor Commented:
Yes, I want it to populate the text fields dynamically. The information in the text fields should change each time a different option is chosen in the select box without submitting or refreshing the form. Is there really not a way to accomplish this with CF? Unfortunately I don't know Javascript at all.
0
yellowsecondAuthor Commented:
P.S. I know it's possible to bind data from a selected item in a <cfgrid> to a text field in this same manner, I'm just hoping there's a way of doing it with a select box instead of a grid.

Thanks for your help...
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

RCorfmanCommented:
Not to my knowledge. I've heard lot's of good things regarding AJAX but have no experience there.  there are other people who monitor this forum who have more experience with the javascript/Ajax methods than I do. I'm sure they'll chime in.
0
Mr_NilCommented:
You can achieve this with Flash CFForm, which is what you appear to be describing with data bindings anyway.

The query you use to populate the cfselect is available to actionscript in the dataProvider object attached to your select.
Your select is called PI_School, so the dataProvider object is : PI_School.dataProvider  
This is, however, is the complete query and not just the currently selected item. To get this you use the selectedIndex data from the select to specify the row you want to view.  PI_School.dataProvider[PI_School.selectedIndex]

You can then access the columns of the query :  
   PI_School.dataProvider[PI_School.selectedIndex]['School_Address']
   PI_School.dataProvider[PI_School.selectedIndex]['City']
and so on

Here's a function, select and textarea that I wrote to do something pretty similar to what you're after.  The name of my form is "OrderForm".  You would need to change this to whatever the name of your form is and change DeliveryAddress1 to DeliveryAddress4 to the appropriate columns from your query.

<cfformitem type="script">
function setAddressDisplay():Void {
      OrderForm.AddressDisplay = '';
      if (DeliveryAddress.selectedIndex >= 1) {
            var thisAddress = DeliveryAddressList.dataProvider[DeliveryAddress.selectedIndex-1];
            var AddressLine1 = thisAddress['DeliveryAddress1'];
            var AddressLine2 = thisAddress['DeliveryAddress2'];
            var AddressLine3 = thisAddress['DeliveryAddress3'];
            var AddressLine4 = thisAddress['DeliveryAddress4'];

            OrderForm.AddressDisplay = DeliveryAddressList.dataProvider[DeliveryAddress.selectedIndex-1]['DeliveryName'];
            if (AddressLine1.length > 0 && AddressLine1 != 'null') OrderForm.AddressDisplay = OrderForm.AddressDisplay+'<br>'+AddressLine1;
            if (AddressLine2.length > 0 && AddressLine2 != 'null') OrderForm.AddressDisplay = OrderForm.AddressDisplay+'<br>'+AddressLine2;
            if (AddressLine3.length > 0 && AddressLine3 != 'null') OrderForm.AddressDisplay = OrderForm.AddressDisplay+'<br>'+AddressLine3;
            if (AddressLine4.length > 0 && AddressLine4 != 'null') OrderForm.AddressDisplay = OrderForm.AddressDisplay+'<br>'+AddressLine4;
            OrderForm.AddressDisplay = OrderForm.AddressDisplay+'<br>'+thisAddress['DeliveryPostCode'];
      }
}
</cfformitem>

<cfselect name="DeliveryAddress" label="Delivery Address" required="yes" message="Please select a delivery address for this order." query="DeliveryAddressList" value="DeliveryID" display="DeliveryLine" queryPosition="below" width="300" onChange="setAddressDisplay()">
      <option value="0">Please select a delivery address.</option>
</cfselect>

<cfinput type="hidden" name="AddressDisplay">
<cfformitem type="html" bind="{OrderForm.AddressDisplay}" style="FONT-SIZE: 11px; COLOR: ##333333; FONT-FAMILY: 'Trebuchet MS', Arial; line-height: 15px; font-weight: normal" />

NOTE : I use [DeliveryAddress.selectedIndex-1] in the function because I have a "Please Select...." option at the top of the select that is not part of the dataProvider and so need to take this into account when a user selects an option.

Hope this helps
0
yellowsecondAuthor Commented:
Thanks for your comments.

I am indeed using a flash form. I guess I should have mentioned that in the beginning.

This is turning out to be far more complicated than I thought it'd be. I'm definitely getting in over my head, but I want to figure this out. I wish I had more points to award for this question, but I've only got the 125. Anyway...if you're willing to continue helping me through this I'd greatly appreciate it.

You mentioned that I could access the other columns in the query via actionscript, and the bind statement would be:

PI_School.dataProvider[PI_School.selectedIndex]['School_Address']
 
This syntax is somewhat familiar to me, based on some bindings I had to do with <cfgrid>. Before adding your <cfscript> I tried plugging it in as follows:

<!--- Get Current School --->
<cfquery name="getPI" datasource="#APPLICATION.datasource#">
SELECT PI_School
FROM tblPI
WHERE PI_PID='#PI_PID#'
</cfquery>
<!--- Get School Info --->
<cfquery name="getSchoolname" datasource="#APPLICATION.datasource#">
SELECT School_ID, School_Name, School_Address, School_City, School_State, School_Zip, School_Principal
FROM tblSchool
</cfquery>

<cfform name="PIform" format="flash" action="PIedit2.cfm" height="150" width="400" skin="halosilver" wmode="transparent">

<cfselect width="180"
              name="PI_School"
              query="getSchoolname"
              value="School_Name"  
              display="School_Name"
              selected="#getPI.PI_School#">
</cfselect>
<cfinput type="text" name="whatever" bind="{PI_School.dataProvider[PI_School.selectedIndex]['School_Address']}">

</cfform>

Of course, that'd be too easy if it just worked with that simple change, right? I hoped that the text input (named "whatever") would display the contents of the School_Address column, but it remained blank. The flash form loaded ok, but it just didn't populate that field.

So on to your Delivery Address example. I did try, as you suggested, to use your code and replace "OrderForm" with "PI_School" and change the column names as well, but then the form wouldn't even load. I'm sure I messed up something...at the very least the syntax. I'm not very well acqainted with ActionScript, so I'm not sure what all is going on in your example. I don't know for certain what items indicate a field in your table, which are variables, which refer to queries, form fields, etc.

In an attempt to troubleshoot the problem I'd like to use your example code verbatim and see if I can get it working. Would you mind showing me your table name, columns, and query info? I could then create a table identical to yours and it would give me a better understanding of the inner workings of your example.

In addition to this do you have any recommendations for a book or online resource that will help me get my head around this?

Many thanks...
0
Mr_NilCommented:
Try this :  <cfinput type="text" name="whatever" bind="{getSchoolname.dataProvider[PI_School.selectedIndex]['School_Address']}">

Something I forgot is that you actually access the query rather than the select for the dataProvider, so for you this would be getSchoolname in my example the select was populated using a query called "DeliveryAddressList".  

From my example :
var thisAddress = DeliveryAddressList.dataProvider[DeliveryAddress.selectedIndex-1];

DeliveryAddressList is the query name and you need to access the dataProvider object in the query.
The bit in the brackets, [ ], specifies the row from your query you want.  To get this you use the index of the selected item in your cfselect, because behind the scenes the cfselect is using the exact same dataProvider object.

In terms of what to read....  there's not a great deal - I bought O'Reily's "Essential Actionscript 2.0" and aquired a copy of the Macromedia "Developing rich clients with Flex" (I attended the Scottish ColdFusion User Group conference last year - Scotch on the Rocks ) They are both useful references, but I haven't used them that much.  Other resource I've used are the articles by the guys at asfusion (http://www.asfusion.com/) and the Flex language and API references on Macromedia's livedocs (http://livedocs.macromedia.com/flex/15/).  In case you were wondering why I use the various flex docs, this is because CFMX 7 has a built-in, cut-down version of Flex 1.5 Server.

Most of what I've learnt has been from the ASFusion articles, a lot of hours of pain and head bashing with the occassional pestering of friends that develop Flex applications.
0
yellowsecondAuthor Commented:
I tried making those changes and the form didn't load. I added some debugging features so I'm now able to see a little more clearly what's going on when my flash form doesn't appear. I got a couple of errors as follows:

2 Errors found.
 
Error /Jeffco/PIedi.mxml:1802
There is no property with the name 'getSchoolname'.


Error /Jeffco/PIedi.mxml:2364
There is no property with the name 'getSchoolname'.

After trying this I went back to your Delivery Address example and got the same error, but with 'DeliveryAddressList' rather than 'getSchoolname'. So it would seem that for some reason the query results aren't being accessed by the actionscript. Is there something else I need to do to the query so that ActionScript 'sees' it? I know there are other ways of performing the query...from a cfc, for instance...but I've never done it that way. Might that be the direction I need to go?
0
Mr_NilCommented:
~sigh~ really sorry about this....  I've just spend the last 60 mins trying to work out why your code wasn't working and why I couldn't get it working either - I switch computers and look at slightly newer version of the code I used this technique in and immediately spot the difference. (Guess I tidied up )

I've been slightly sneaky to get this working. Tucked away at the top of the page is a hidden cfgrid that is populated with the same query that is used to populate the cfselect.  It is this that I am pull the data out of and not the query.  The CFGRID creates a row and a column for each column and row in the query, so it possible to access it like it was a query.

<cfgrid name="DeliveryAddressList" query="DeliveryAddressList" appendkey="yes" griddataalign="left" gridlines="yes" rowheaderalign="left" colheaderalign="left" selectmode="browse" enabled="no" autowidth="true" visible="no" height="-1" width="-1"></cfgrid>

Stupidly I called it the same name as the query (made sense at the time - really stupid idea when I come back to look at the code again).  So lets take your code and write it a bit more sensibly!

<!--- Get Current School --->
<cfquery name="getPI" datasource="#APPLICATION.datasource#">
SELECT PI_School
FROM tblPI
WHERE PI_PID='#PI_PID#'
</cfquery>
<!--- Get School Info --->
<cfquery name="getSchoolname" datasource="#APPLICATION.datasource#">
SELECT School_ID, School_Name, School_Address, School_City, School_State, School_Zip, School_Principal
FROM tblSchool
</cfquery>

<cfform name="PIform" format="flash" action="PIedit2.cfm" height="150" width="400" skin="halosilver" wmode="transparent">
<cfgrid name="gridSchoolNames" query="getSchoolName" appendkey="yes" griddataalign="left" gridlines="yes" rowheaderalign="left" colheaderalign="left" selectmode="browse" enabled="no" autowidth="true" visible="no" height="-1" width="-1"></cfgrid>

<cfselect width="180"
              name="PI_School"
              query="getSchoolname"
              value="School_Name"  
              display="School_Name"
              selected="#getPI.PI_School#">
</cfselect>
<cfinput type="text" name="whatever" bind="{gridSchoolNames.dataProvider[PI_School.selectedIndex]['School_Address']}">

</cfform>

And that should do the trick.

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
yellowsecondAuthor Commented:
Absolutely perfect!

This is exactly what I was looking for. Thanks so much!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.