Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2006-03-28
10
Medium Priority
?
861 Views
Last Modified: 2013-12-24
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.
0
Comment
Question by:yellowsecond
  • 5
  • 3
  • 2
10 Comments
 
LVL 16

Expert Comment

by:RCorfman
ID: 16318206
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
 

Author Comment

by:yellowsecond
ID: 16318362
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
 

Author Comment

by:yellowsecond
ID: 16318391
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
LVL 16

Expert Comment

by:RCorfman
ID: 16318460
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
 
LVL 10

Expert Comment

by:Mr_Nil
ID: 16319225
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
 

Author Comment

by:yellowsecond
ID: 16326458
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
 
LVL 10

Expert Comment

by:Mr_Nil
ID: 16332827
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
 

Author Comment

by:yellowsecond
ID: 16333935
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
 
LVL 10

Accepted Solution

by:
Mr_Nil earned 540 total points
ID: 16334803
~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
 

Author Comment

by:yellowsecond
ID: 16335234
Absolutely perfect!

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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses
Course of the Month14 days, 3 hours left to enroll

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question