passing 2 values from cfselect based on selection from dropdown?

Question

This is only my second question on SO, and I'm brand new to web coding of all kinds (anything I've done I've picked up within the last 3 weeks so I don't really know what I'm doing) but here's some background and my issue:

Background info: I'm updating some existing pages that previously had, no joke, 42 different dropdown menus to show data maps for different water bodies and sampling dates. So I'm condensing that down to a slightly more elegant (though still way dated) approach of all of that in 2 dropdown menus where users can choose whatever combo of tributaries and sampling dates they want to see data for.

I have 2 dynamic related dropdowns using a CFC and CFM (those are working great, thanks to help from SO). THE CFC has the functions, queries, arguments to get the data from the database. The CFM displays the first dropdown menu (a list of tributaries), and when a user selects from that, a second dropdown is populated with dates that the tributary/ies were sampled. The 'display' of this 2nd dropdown is tributary name appended to the beginning of the date so they displays as a 'tribdate', like "Back River: Apr 07, 2014". When a user selects what they want from the second dropdown (named 'link') and hits submit, the sent value(s) are path strings ('File_html') which, on the target page, result in maps of data being displayed for those tributaries on those dates. In the target page the 'form.link' is defined, loops, and shows all the data map images (ie. 'File_html') correctly.

My issue: Above each image that is displayed I want to have the "tributary" name and the "sampling date" but I don't know how to pass this information. I am passing the 'File_html' but I would like to pass 'tribdate' (the display value) or pass 'tributary' or 'cruisedate' separately. Or even use the alt text from the image, but I don't know how to do that either.

What I've tried: (1) I can't pull a usable tributary name out of the value that is passed (many are partial names), but I can pull a usable tributary date. (2) If I pull the 'tributary' name from the 1st dropdown where tributary is selected and try to use that to title the maps it only works for one map (which makes sense bc only 1 tributary is selected). So if a user selects 2 dates from 'Back River' only one map can be displayed with the title of 'Back River' and there's an error for the second. If there's a way to replicate this tributary value to be reused when needed, that would be great. (3) I tried making a 3rd dropdown which just shows the tributary name for the dates selected and then on the mapdisplay using the name of that dropdown menu for the title name, which works, but I can't really have people select those, I want them automatically passed.

Can I call tributary from the CFC? Can I send 'tribdate' to the mapdisplay page? Any help, hints, ideas would be great, thank you. I saw this question which is essentially what I'm asking but I don't really understand where to put the answer that was given: Using CFQUERY and CFSELECT to pull multiple values based on selection

The CFC:

<cfcomponent displayname="GetStuff" hint="Getting data on cruises from database">
<!---Get list of Tributaries--->
    <cffunction name="getData" 
                access="remote" 
                returntype="query"
                hint="Get data for tributary dropdown">

<!---define variables--->
<CFSET VAR data="">

<!---Run the query for tributaries--->
<cfquery name="data" datasource="mydatasource">
    SELECT DISTINCT Tributary
    FROM df_cruises
    ORDER BY Tributary
</cfquery>

<!---and return it--->
<cfreturn data>
</cffunction>

<!---Get Dates by Tributary--->
<cffunction name="getDates"
            access="remote"
            returntype="query"
            hint="Get cruise dates by tributary for select dropdown">
<cfargument name="Tributary" type="any" required="no" multiple="yes">

<!---define variables--->
<cfset var data="">

<!---Run query to get Date Data and add Trib name to front, and get File_html to send to MapDisplay--->
<cfquery name="data" datasource="mydatasource">
    SELECT File_html, Tributary, (Tributary + ':' + SPACE(2)) + CONVERT(varchar(12), CruiseDate, 107) AS tribdate
    FROM df_cruises
    WHERE Tributary IN (<cfqueryparam cfsqltype='cf_sql_varchar' list="yes" value='#ARGUMENTS.Tributary#'>)
    ORDER BY tribdate
</cfquery>

<!---And return it--->
<cfreturn data>
</cffunction>

</cfcomponent>

CFM which has the dropdowns/form:

<cfform name="CruiseChoose" action="mapdisplayv5.cfm" method="post">    
 <td align="center">
     <strong>Tributary/Water Body:</strong><br />
    <cfselect name="Tributary"
    size="7" class="dynamicdropdown"
    multiple="yes"
    value="Tributary"
    display="Tributary"
    bind="cfc:UsingBook.getData()"
    bindonload="yes"/>
    </td>

    <td align="center"><br />
      <strong>Sampling Dates:</strong><br />
    <cfselect name="link"
    size="7" class="dynamicdropdown"
    multiple="yes"
    value="File_html"
    display="tribdate"
    bind="cfc:UsingBook.getDates({Tributary})"
    bindonload="false"
    type="any"/>
    </td>

The target CFM page which displays the data maps:

<!---mapdisplayv5.cfm--->
<tr>
<td>
<p><p><cfoutput>
<cfif IsDefined("form.link")>
<br>
<cfset cnt= 1>
<cfloop index="listing" list="#form.link#" delimiters=",">
<cfset Tributary=#listGetAt(form.link, cnt)#> 
<div align="center">
<font size="4"><b>#Tributary#<cfset cnt=cnt> - <cfset tribdate=#listGetAt(form.link, cnt)#> #right(tribdate, 6)# </b></font>
<cfset wqmimage=#listGetAt(form.link, cnt)#>
<table width="85%" border="1" bordercolor="##000000">
  <tr>
    <td><a href="images/#Ltrim(wqmimage)#lg.html" class="link"><cfinclude template="images/#Ltrim(wqmimage)#.html"></a></td>
  </tr>
</table>

<a href="images/#Ltrim(wqmimage)#lg.html" class="link">Click here for Enlarged Map (WARNING-Large File Size)</a><Br>

<cfif listlen(form.link)Less Than or Equal to #cnt#>
<cfbreak>
</cfif>
<cfset cnt=cnt+1>

<br><br><hr>

</cfloop>
<cfelse>
<b>
<p align="center"><br><br><br><br>No Selection Was Made.<br>Please Return to the Previous Page and Make Your
    Selection</p></b><Br>
    <br><br><br>
</cfif>
</p>

</cfoutput></p>

UPDATE: On the CFM page that has the form I am now passing the PK "ID" to the mapdisplayv5.cfm, below is the new code for that. It now relates the correct title and shows the image that I want and will show multiple, but each multiple is a copy of whatever the first selection is from the second dropdown. So if you choose 2 different dates, it shows 2 copies of the first date you chose.

Snapshot of the change to the 2nd dropdown on the CFM with the form, note that I am now passing ID:

<td align="center"><br />
  <strong>Sampling Dates:</strong><br />
<cfselect name="link"
size="7" class="dynamicdropdown"
multiple="yes"
value="ID"
display="tribdate"
bind="cfc:UsingBook.getDates({Tributary})"
bindonload="false"
type="any"/>
</td>

Change to the mapdisplay.cfm page:

<!---mapdisplayv5.cfm--->
<tr>
<td>
<p><cfoutput>
<cfif IsDefined("form.link")>
<cfquery name="retrieve" datasource="mydatasource">
SELECT File_html, Tributary, CruiseDate, (Tributary + ':' + SPACE(2)) + CONVERT(varchar(12), CruiseDate, 107) AS tribdate
FROM df_cruises
WHERE ID IN (<CFQUERYPARAM VALUE='#FORM.link#' list='yes' CFSQLTYPE='CF_SQL_INTEGER'>)
</cfquery>
<cfset cnt= 1>
<cfloop index="listing" list="#form.link#" delimiters=",">
<br>
<cfset wqmimage=#retrieve.File_html#>
<cfset tributary=#retrieve.Tributary#>
<cfset tribdate=#retrieve.tribdate#>
<div align="center">
<font size="4"><b>#tribdate#</b></font>
<a href="images/wqmimage.jpg" class="link"><cfinclude template="images/#Ltrim(wqmimage)#.html"></a>
<a href="images/#Ltrim(wqmimage)#lg.html" class="link">Click here for Enlarged Map (WARNING-Large File Size)</a><Br><br>
<cfif listlen(form.link)Less Than or Equal to #cnt#>
<cfbreak>
</cfif>
<cfset cnt=cnt+1>
</cfloop>
<cfelse>
<br>
<p align="center"><br><br><br><br>No Selection Was Made.<br>Please Return to the Previous Page and Make Your
    Selection</p></b><Br>
    <br><br><br>
</cfif>
</cfoutput>

UPDATE 2:The corrected, working version of the mapdisplayv5.cfm (THANK YOU luke, Dan, and Leigh!)

<!---mapdisplayv5.cfm--->
<tr>
<td>
<p>
<cfif IsDefined("form.link")>
<cfquery name="retrieve" datasource="tideRO">
SELECT File_html, Tributary, CruiseDate, (Tributary + ':' + SPACE(2)) + CONVERT(varchar(12), CruiseDate, 107) AS tribdate
FROM df_cruises
WHERE ID IN (<CFQUERYPARAM VALUE='#FORM.link#' list='yes' CFSQLTYPE='CF_SQL_INTEGER'>)
</cfquery>
<br><cfoutput query="retrieve">
<cfset wqmimage=#retrieve.File_html#>
<cfset tributary=#retrieve.Tributary#>
<cfset tribdate=#retrieve.tribdate#>
<div align="center">
<font size="4"><b>#tribdate#</b></font>
<a href="images/#wqmimage#.jpg" class="link"><cfinclude template="images/#Ltrim(wqmimage)#.html"></a>
<a href="images/#Ltrim(wqmimage)#lg.html" class="link">Click here for Enlarged Map (WARNING-Large File Size)</a><Br><br></cfoutput>

i guess the database you're querying has some sort of unique ID field. if you pass that over instead of any other data, then you can pull out any data you want with the ID passed over — luke, May 04 '16 at 14:42
ah, yes, that sounds totally logical! can you point me to any hints or links to help on how to do that on my target page? I can pass the ID field but I don't know how to then use that to get what I want. — Becalecca, May 04 '16 at 14:56
i've not used cfselect before, but i'd guess you want to set the value="Tributary" to your database ID like value="ID". then on your next page, you can SELECT whatyouneed FROM df_cruises WHERE ID = . then you can cfoutput your query like #YourQuery.Tributary# — luke, May 04 '16 at 15:06
Side note, you might also want to review the table structure. From the other thread, my impression was it could benefit from [some normalization](https://technet.microsoft.com/en-us/library/aa216117%28v=sql.80%29.aspx). For example, typically unique Tributaries would probably be stored a separate table with a unique ID. That ID would then be used as a FK in other related tables (rather than the name). Along the same lines, forms would still *display* the Tributary name, but use the "ID" as the field value, which is then passed to the CFC's for querying, similar to what luke is suggesting. — Leigh, May 04 '16 at 16:13
Thanks luke, this is a big help, I have the titles and link to the datamap working for a single selection, I just have to work out how to get the multiple selections working. I'll post a code update when I get there. And thank you too, Leigh, I definitely understand what you mean, and I'm actually surprised it isn't set up that way, however I've come into a long established data program and I don't think I'm allowed to change any table structures (yet?) — Becalecca, May 04 '16 at 17:26
I have vague recollections of suggesting that you pass ID fields in your last question. — Dan Bracuk, May 04 '16 at 17:31
Yes you absolutely did Dan, and I went with a different approach that was easier (at the time) for me, but now I have definitely switched to that approach! — Becalecca, May 04 '16 at 17:41
I have it partially working (I posted an update below original question), it's not working correctly - if you select more than one date it's showing the same information multiple times instead of showing the correct selections. — Becalecca, May 04 '16 at 18:30
If more than one date is selected, what are you passing and what is your query doing with it? — Dan Bracuk, May 04 '16 at 18:35
If more than one display=tribdate is selected the value passed is ID. I had hoped that my query was pulling all other info that I'd like to used based on the IDs that are sent from the dropdown selections. And if anything is chosen individually it is correctly pulling/displaying. But anything more than 1 selection results in duplicates. — Becalecca, May 04 '16 at 18:39
(EDIT) *each multiple is a copy of whatever the first selection is* Disclaimer, I have only skimmed the update, but it looks like the logic is wrong. You should be using the query results to generate the map results, not looping through the form fields. ie Query loop `.....` instead of `...`. Also, the reason you are seeing duplicates is that the code is not inside a *query* loop, so using `#queryName.columnName#` will *always* returns the value in the *first* row of the query. — Leigh, May 04 '16 at 18:49
*I've come into a long established data program and I don't think I'm allowed to change any table structures* I was afraid of that ;-) It would required some work to refactor the tables after the fact, but it is well worth it. A properly normalized structure has less data integrity issues, requires less complex queries and tends to yield better performance. — Leigh, May 04 '16 at 18:58
should I post an answer to this with all the corrected code? — Becalecca, May 04 '16 at 19:04
For my 2¢, I think @Luke should promote his suggestion to an answer, since it led to the solution. You can still post a separate answer with the corrected code to help the next guy reading this thread :) — Leigh, May 04 '16 at 19:14

passing 2 values from cfselect based on selection from dropdown?

0 Answers0