Solved

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

Posted on 2014-12-26
9
86 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

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

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

Suggested Solutions

The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Suggested Courses

738 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