Solved

setting up a SELECT menu -- how to indicated selected value -- Part 3

Posted on 2014-12-26
9
81 Views
Last Modified: 2014-12-27
ColdFusion 11

This is similar to previous questions, but is slightly different. I have been wrestling with this for a couple of hours. I think I am misusing the list function. Again, I am trying to convert CFSELECT to SELECT.

OLD code:
     <!--- this query selects State Titles from USCA_States --->
<cfquery datasource="#APPLICATION.dataSource#" name="GetStates">
        SELECT  StateName
        FROM    USCA_States
        ORDER BY StateName ASC
</cfquery>
        
         <cfselect size="5" class="adminSelect" name="state1" value="StateName" display="StateName" multiple="no" query="GetStates" queryPosition="below" selected="#ListCareplanners.state1#" tabindex="14">
     <option value=""> Select State: </option>
     </cfselect>
        

Open in new window



New code:

<!--- this query selects StateName from USCA_States --->

<cfquery datasource="#APPLICATION.dataSource#" name="GetStates">
        SELECT  StateID, StateName
        FROM    USCA_States
        ORDER BY StateName ASC
</cfquery>

<!--- query getSelected requests CareplannersID value already assigned to the current record --->
  <cfquery datasource="#application.datasource#" name="getSelected">
     SELECT CareplannersID
     FROM #REQUEST.membersTable# 
     WHERE CareplannersID = <cfqueryparam value="#val(form.CareplannersID)#" cfsqltype="cf_sql_integer">
</cfquery>  
  
<!--- convert assigned CareplannersID values into a comma separated value list --->
     <cfset selectedState = valueList(getSelected.CareplannersID)>  

  <!--- assuming #selectedState# is always defined --->
 <select size="10" name="state1" tabindex="14">
       <option value=""> Select State: </option>
      <cfoutput query="GetStates">
          <option value="#GetStates.StateName#" <cfif listFindNoCase(selectedState, CareplannersID)>selected</cfif>>
           #GetStates.StateName#
        </option>
     </cfoutput>
</select> 

Open in new window



But when I use this code, the selected state is always the last state in the full list of states -- in this case, Yukon (since I am including Canada).

I think I am setting up value "selectedState" improperly, or mishandling "listFindNoCase".

What am I missing?

Thank you as always. =)

Eric
0
Comment
Question by:Eric Bourland
  • 6
  • 3
9 Comments
 
LVL 39

Expert Comment

by:gdemaria
ID: 40519241
Hi Eric,

It looks like you are using the CareplannersID as the state id.   I suspect that is is not the state ID, is it?   If it is, then I need to change my answer, but I am guess the CarePlannerID is not the ID for the state, I think it would be "stateID"

     <cfset selectedState = valueList(getSelected.CareplannersID)>
                                                                                         ^^^^^^  is this the state id?

I think it should be...
     <cfset selectedState = valueList(getSelected.stateID)>

and be sure to select it in your getStates query..

  <cfquery datasource="#application.datasource#" name="getSelected">
     SELECT CareplannersID ----- select the stateID here
     FROM #REQUEST.membersTable#


In your CFIF statement, you want to compare the state ID to see if it's in the list of all states..

Here you have the carePlannerID, that is not the state ID
          <option value="#GetStates.StateName#" <cfif listFindNoCase(selectedState, CareplannersID)>selected</cfif>>
                                                                                                                                                    ^^^^^^^^^  you want state ID
It should be...
          <option value="#GetStates.StateName#" <cfif listFindNoCase(selectedState, GetStates.stateID)>selected</cfif>>
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 40519252
Makes sense. I see where I was going wrong.

>>>>  <cfquery datasource="#application.datasource#" name="getSelected">
     SELECT CareplannersID ----- select the stateID here
     FROM #REQUEST.membersTable#

Hmmm. Column StateID is in table USCA_States, not in table #REQUEST.membersTable#.

Column StateName is in both tables: USCA_States and #REQUEST.membersTable#

Can I dispense with StateID and use StateName?

I am going to try that. =)
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 40519256
My last comment contained at least one and possibly more errors.

Column StateName is in table USCA_States.

Column state1 is in table #REQUEST.membersTable#.

state1 is populated with values from StateName.

Reconsidering this. I think I see where this is going. BRB
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 40519262
Your States table using a primary key StateID and StateName ...

  SELECT  StateID, StateName
     FROM    USCA_States
  ORDER BY StateName ASC

When you create a foreign key to this table, you are supposed to use the primary key which is StateID in the other tables.   It sounds like you have used StateName instead.   That is typically not good, in this case, you may get away with it as state names should never change.

What I do is to use the state abbreviation as the primary key of the state table.  The two letter abbreviation takes the place of the numeric ID and I can use it in all tables as the foreign key.  We know that the state abbreviation never changes and it's nice not to have to go back to the states table unless you want the full name, which is only sometimes.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 3

Author Comment

by:Eric Bourland
ID: 40519273
Table USCA_States also has column StateAbbr with all of the abbreviations for US states and Canada provinces.

>>>What I do is to use the state abbreviation as the primary key of the state table.  The two letter abbreviation takes the place of the numeric ID and I can use it in all tables as the foreign key.

I see what you mean. I am going to work on this now.

Making progress. This is interesting and useful.
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 40519287
dear gdemaria,

Your method makes sense. Use StateAbbr as PK in states lookup table, and as foreign key in the membership table.

Yet here is my problem. I inherited this Careplanners database from someone else. The original version of this database was a MS Access '97 database from the 1990s.

I will try to explain my situation further.

* The original developer set up the Careplanners Membership table with full state names.

* Also, there are two business addresses for each member.

* .... Which means that each record in the table has two columns for States: state1 and state 2 .... which apparently the original developer had been updating manually, right in the database.

* So I have a table with about 900 records give or take with two columns: state1 and state2, both of which are populated with full state names.

* I have set up an edit form that requests state names from a state lookup table. So at least there is no variation in the state1 and state2 values assigned to any record.

If possible I would like to be able to set up a SELECT menu that displays the SELECTED state1 and state2 values for each record.

But I have been stuck with a complicated situation.

So, lookup table USCA_States has columns StateID, StateName, StateAbbr.

The membership table -- which I am updating with this form -- has columns state1 and state2.

(Ugh! This is all quite jerry-rigged.)

So, to update the first business address (column state1), I think I can use something like:

<!--- this query selects stateID, StateAbbr, StateName from USCA_States --->

<cfquery datasource="#APPLICATION.dataSource#" name="GetStates">
        SELECT  stateID, StateAbbr, StateName
        FROM    USCA_States
        ORDER BY StateName ASC
</cfquery>

<!--- query getSelected requests CareplannersID value already assigned to the current record --->
  <cfquery datasource="#application.datasource#" name="getSelected">
     SELECT CareplannersID, state1
     FROM #REQUEST.membersTable# 
     WHERE CareplannersID = <cfqueryparam value="#val(form.CareplannersID)#" cfsqltype="cf_sql_integer">
</cfquery>  
  
<!--- convert assigned CareplannersID value into a comma separated value list --->
      <cfset selectedState = valueList(getSelected.CareplannersID)>  

 <select size="10" name="state1" tabindex="14">
       <option value=""> Select State: </option>
      <cfoutput query="GetStates">
            <option value="#GetStates.StateName#" <cfif listFindNoCase(selectedState, CareplannersID)>selected</cfif>> 
           #GetStates.StateName#
        </option>
     </cfoutput>
</select> 

Open in new window


That does not throw an error ... but selects Yukon again at the end of the list.

You said you think I might get away with using StateName from the state lookup table, and state1 from the Membership table.... how would that look?

Thanks as always for helping me in my predicament. =)

Eric
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
ID: 40519304
I guess you have to just play the hand you were dealt, as they say...
You need to use State1 for your selected state column and match it up with the state name.  That's very unfortunate, especially since you don't use the Full State name in an Address!  You're supposed to use the abbreviation which means you need to use the full name to look up the abbreviation... messy.


 <cfset selectedState = valueList(getSelected.state1)>  

 <select size="10" name="state1" tabindex="14">
       <option value=""> Select State: </option>
      <cfoutput query="GetStates">
            <option value="#GetStates.StateName#" <cfif listFindNoCase(selectedState, GetStates.StateName)>selected</cfif>> 
           #GetStates.StateName#
        </option>
     </cfoutput>
</select> 
                                          

Open in new window

0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 40519312
Got it.... looks like that is working. I was trying to match those things but could not quite figure out how.

This also helps me match some other things I need to match.

I'll come back to this question in the morning ... I really appreciate your help. Thank you as always.

Hope you are well. Have a great evening. =)

Eric
0
 
LVL 3

Author Closing Comment

by:Eric Bourland
ID: 40520015
gdemaria, it's working really well. Thanks for getting me out of an awkward situation with the full state names. Abbreviations would be a lot better. For now I will keep this application as it is. If someone wants to change the name of the province of Yukon, I'll say sorry, you can't -- my database is already set up the other way. ;-) Thank you as always. Hope you are great.

Eric
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
This video discusses moving either the default database or any database to a new volume.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now