asked on

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.

RCorfman

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.

yellowsecond

ASKER

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.

yellowsecond

ASKER

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...

RCorfman

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.

Mr_Nil

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+' '+AddressLine1;
 if (AddressLine2.length > 0 && AddressLine2 != 'null') OrderForm.AddressDisplay = OrderForm.AddressDisplay+' '+AddressLine2;
 if (AddressLine3.length > 0 && AddressLine3 != 'null') OrderForm.AddressDisplay = OrderForm.AddressDisplay+' '+AddressLine3;
 if (AddressLine4.length > 0 && AddressLine4 != 'null') OrderForm.AddressDisplay = OrderForm.AddressDisplay+' '+AddressLine4;
 OrderForm.AddressDisplay = OrderForm.AddressDisplay+' '+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

yellowsecond

ASKER

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:


<cfquery name="getPI" datasource="#APPLICATION.datasource#">
SELECT PI_School
FROM tblPI
WHERE PI_PID='#PI_PID#'
</cfquery>

<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...

Mr_Nil

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.

yellowsecond

ASKER

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?

ASKER CERTIFIED SOLUTION

Mr_Nil

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

yellowsecond

ASKER

Absolutely perfect!

This is exactly what I was looking for. Thanks so much!