Solved

ColdFusion How can I bind second drop down field with a sticky drop drop down?

Posted on 2014-03-15
15
1,608 Views
Last Modified: 2014-03-20
I have two drop downs - when one is selected the other automatically displays the options.

I made the first drop down sticky (when the page displays the results the drop down retains the last input).

Problem: If nothing was selected in the second drop down it resets to all. I would like this second drop down to stay binded to the first drop down.

Code for the binded drop down:
<cfselect name="discipline4" style=width:154px; title="Press and hold Ctrl key to select multiple projects" multiple>
      <option value="ALL">ALL</option>
      <cfoutput query="DropDown4">
         <option value="#DropDown4.ProjNum#"
          <cfif listFindNoCase(FORM.discipline4, DropDown4.ProjNum)>selected</cfif>
           >  #DropDown4.ProjNum#</option>
        </cfoutput>
        
        <cfselect name="discipline4" selected="#FORM.discipline4#"
                bind="cfc:MyComponent3.getProjects( {discipline1} )"
                value="ProjNum"
                display="ProjNum"
                multiple="Yes"/>
                  </cfselect></td>

Open in new window

0
Comment
Question by:DJPr0
  • 7
  • 5
  • 3
15 Comments
 
LVL 14

Assisted Solution

by:RickEpnet
RickEpnet earned 125 total points
ID: 39931442
Here is a Sample I have

First Select
<cfselect enabled="No" name="fi_id" required="yes" message="Please Select an FI" multiple="no" query="listfi" value="Auto" display="FI_Name" queryPosition="below" selected="#fi_id#">
                <cfif fi_id is "">
                  <option value="" selected="selected"></option>
                </cfif>
                </cfselect>

Second Select
<cfselect bind="cfc:lookups.getatmbyfi({fi_id})" enabled="No" bindonload="yes" name="atm_id" required="yes" message="Please Select an ATM_ID" multiple="no" value="auto" display="atm_fullname" selected="#session.atm_select#" />

***** getatmbyfi function in lookups.cfc *****
    <cffunction name="getatmbyfi" access="remote">
    <cfargument name="FI_ID" required="yes">
   
            <cfquery name="atmQuery" datasource="#client.dsn#">
                  select CONCAT(atm_id,', ',city,', ',location_site) AS atm_fullname, auto
                  from v_atms
            where fi_id = '#fi_id#' and location_site <> ''
            order by atm_id
            </cfquery>        


      <cfreturn atmQuery>        
   
       
    </cffunction>
0
 

Author Comment

by:DJPr0
ID: 39931474
Adding the attribute: bindonload="yes" works fine.

Problem:
On page refresh, I receive a "null" value listed along with all the other project data.
0
 
LVL 14

Expert Comment

by:RickEpnet
ID: 39931920
The Null value is in the second select statement?
0
 
LVL 52

Expert Comment

by:_agx_
ID: 39931924
(no points...)

If you enabled the CF ajax debugger, is the NULL part of the the query results - or is it something that happens afterward? Not sure what your query looks like, but ... a query could actually return NULL if you were concatenating like in Rick's example. With concatenation, if even one of the column values is NULL, the end result will be NULL. (Edit) For example:

SELECT   CONCAT(atm_id, ', ', city, ', ', NULL)   <=== this would produce NULL
0
 

Author Comment

by:DJPr0
ID: 39931952
The Null value is in the second select statement?
Yes

If you enabled the CF ajax debugger, is the NULL part of the the query results - or is it something that happens afterward?


It happens on a fresh page due to enabling "Bind on load" on drop down 2.
On a fresh page nothing is selected on drop down 1 and by binding on load the CFC component is activated producing a "null" added to the list of data:

Drop down 1       Drop down 2 (binded via CFC) (multi-select box)
ALL                      ALL      
Ship 1                  null
Ship 2                  Project 1
Ship 3                  Project 2

Can we alter the CFC component? if "ALL" in drop down 1 don't bind anything

CFC component:
<cfcomponent>
   <cfset variables.dsn = "SupportTESTDB">

    <cffunction name="getProjects" access="remote" returnType="query">
     <cfargument name="disipline1" type="string" default="ALL">





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

        <!--- get data to use for select "value" and "display" columns --->
        <cfquery name="data" datasource="#variables.dsn#">
         SELECT 0 AS SortCol, 0 AS ship, 'ALL' as ProjNum FROM orderedmaterials
             UNION
              SELECT 1 AS SortCol, ship, ProjNum  
               FROM   orderedmaterials
           <!--- change cfsqltype as needed --->
           <cfif arguments.disipline1 neq "ALL">
                  where ship = <cfqueryparam value="#arguments.disipline1#" cfsqltype="cf_sql_varchar">
           </cfif>
             ORDER BY SortCol, ProjNum
        </cfquery>

        <cfreturn data>
    </cffunction>
                                           
</cfcomponent>

Open in new window

0
 
LVL 14

Expert Comment

by:RickEpnet
ID: 39931975
Have you tried taking out the Bind on load? I cannot remember what happens if you do that but it is worth a try. I did this a while back.
0
 

Author Comment

by:DJPr0
ID: 39932011
If I take out Bind on Load, I do not get the null value, but when the page displays the results of the search and retains the drop down 1 value the bind doesn't trigger.

Not sure if I could Bind on Load - only if other than "All" is detected in drop down 1.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 375 total points
ID: 39932047
> <cfselect name="discipline4" ...>
> <cfselect name="discipline4"

BindOnLoad works fine in CF10 - provided :

1) You're not actually using the same name for both select lists. The names must be different

2) Your getProjects() query actually treats the argument as *multiple* values, not just as single value.

      <cffunction name="getProjects" access="remote" returntype="query">
            <cfargument name="valuesFromFirstList" type="string" />
            <cfset var qResult = "">
            
            <cfquery name="qResult" ....>
                        SELECT 0 AS SortNum, 'ALL' AS ProjNum
                        UNION ALL
                  SELECT 1 AS SortNum, ProjNum
                  FROM   someTable
                  WHERE  someColumn IN
                        (

                        <cfqueryparam value="#arguments.valuesFromFirstList#"
                                           list="true"
                                           cfsqltype="(your col type here)" >
                        )
                  ORDER BY SortNum, ProjNum
            </cfquery>
            <cfreturn qResult />
      </cffunction>

Also, since you're populating the 1st list from a query, may as well use the "query" attribute instead. (Field names changed to emphasize what goes where ... )

<cfparam name="FORM.firstList" default="">
<cfparam name="FORM.secondList" default="">

<cfselect name="firstList" query="queryForFirstList" 
		display="ColumnNameToDisplayHere"
		value="ColumnNameForValueHere"
		queryPosition="below"
		multiple="true"
		selected="#FORM.firstList#">
      <option value="ALL">ALL</option>
</cfselect>        

<cfselect name="secondList" selected="#FORM.secondList#"
                bind="cfc:MyComponent3.getProjects( {firstList} )"
                value="ProjNum"
                display="ProjNum"
                bindOnLoad="true"
                multiple="Yes" />

Open in new window

0
 

Author Comment

by:DJPr0
ID: 39932612
Works good _agx_ (null is gone) except:

The second drop down Projects display's "ALL" only - how can I display all the projects in the second drop down on a fresh page?

How can we bind on load - if "ALL" in the first drop down show all projects.

Also changed first drop down to non-multiple - only want multiple on the second drop down.

Component:
<cfcomponent>
   <cfset variables.dsn = "SupportTESTDB">

    <cffunction name="getProjects" access="remote" returnType="query">
     <cfargument name="disipline1" type="string" >

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

 

        <!--- get data to use for select "value" and "display" columns --->
        <cfquery name="data" datasource="#variables.dsn#">
         SELECT 0 AS Sortnum,  'ALL' as ProjNum FROM orderedmaterials
             UNION
              SELECT 1 AS Sortnum,  ProjNum  
               FROM   orderedmaterials
           <!--- change cfsqltype as needed --->
         
                 
                  where ship in
                  (
                   <cfqueryparam value="#arguments.disipline1#" list="yes" cfsqltype="cf_sql_varchar">
                 
                  )
                               
         
             ORDER BY Sortnum, ProjNum
        </cfquery>
       
         <cfreturn data>
    </cffunction>
                                           
</cfcomponent>
0
 
LVL 52

Expert Comment

by:_agx_
ID: 39932671
> How can we bind on load - if "ALL" in the first drop down show all projects.

Not sure I follow.  Do you mean you when "ALL" is selected in the first list, you want the second list to show contain all projects, not just the option "ALL"? If yes, you need to change the query so it returns everything when the argument EQ "ALL".

      <!--- only filter by value when criteria is something OTHER than "ALL" --->
      <cfif arguments.discipline1 NEQ "ALL">
      where ship in
                  (
                   <cfqueryparam value="#arguments.disipline1#" list="yes" cfsqltype="cf_sql_varchar">
                 
                  )
       </cfif>

Though if the 1st select is no longer multiple, then you don't need the IN (...) clause. A simple equals = will do:

      <cfif arguments.discipline1 NEQ "ALL">
            WHERE ship = <cfqueryparam ...>
     </cfif>
0
 

Author Comment

by:DJPr0
ID: 39932702
Do you mean you when "ALL" is selected in the first list, you want the second list to show contain all projects, not just the option "ALL"?
Yes
After adding: <cfif arguments.discipline1 NEQ "ALL">

Everything works fine except the "null" is back in the list : (

Projects
All
null
Project 1
Project 2

Is the component returning all the projects with a null value or are we disabling the component with the NEQ statement spitting out just a null?
0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 375 total points
ID: 39932719
If you enabled the CF ajax debugger, is the NULL part of the the query results - or is it something that happens afterward?

I think we're back to my earlier question.  Are there actual NULL's in the query results - or is the NULL being added by javascript?  Run it in your browser with different values (ALL, etc...). What query results do you see?

http://yourserver/path/to/yourComponent.cfc?method=getProjects&discipline1=ALL


If there are NULL's in your db table, you need to exclude them in the query, something like:

       <!--- return all NON-NULL project num's --->
       <cfif arguments.discipline1 EQ "ALL">
               WHERE  ship IS NOT NULL
       <cfelse>
               WHERE  ship = <cfqueryparam ...>
       </cfif>
0
 

Author Comment

by:DJPr0
ID: 39932743
There are null values only in the second drop down (projects - field)

No nulls in the first drop down (ship - field)

This seems to work:
<cfif arguments.disipline1 EQ "ALL">
                  WHERE  projnum IS NOT NULL
                  <cfelse>
                  where ship =
                  (
                   <cfqueryparam value="#arguments.disipline1#" list="yes" cfsqltype="cf_sql_varchar">
                 
                  )
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 375 total points
ID: 39932756
EDIT
> There are null values only in the second drop down (projects - field)

Right, but my question was where is the NULL coming from: the database or javascript  :) It could be either, but .. your last post answered the question. If adding the fliter WHERE Col IS NOT NULL solves the problem - that means the nulls are coming from the database table.
----------------------------

> This seems to work:

Oh yeah .. the first filter should have been on ProjNum. FYI, you only need parenthesis when using an IN (...) clause with a list of values. If you're only looking for a single value, use:  WHERE Col = <cfqueryparam value="..."  ....>

Also, if it's possible for the cfelse to return NULL ProjNum's too, you should add the NULL filter to the cfelse statement as well.

<cfif arguments.disipline1 EQ "ALL">
    WHERE  projnum IS NOT NULL
<cfelse>
    WHERE ship =   <cfqueryparam value="#arguments.disipline1#"
                                        cfsqltype="cf_sql_varchar">
    AND     projnum IS NOT NULL
</cfif>
0
 

Author Closing Comment

by:DJPr0
ID: 39942440
Thanks!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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 …
Have you tried to learn about Unicode, UTF-8, and multibyte text encoding and all the articles are just too "academic" or too technical? This article aims to make the whole topic easy for just about anyone to understand.
In this tutorial viewers will learn how to code links for mobile sites that, once clicked, send a call or text to a specified number. For a telephone link (once clicked, calls a number), begin with a normal "<a href=" link tag. For the href, specify…
The viewer will learn the benefit of using external CSS files and the relationship between class and ID selectors. Create your external css file by saving it as style.css then set up your style tags: (CODE) Reference the nav tag and set your prop…

747 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

9 Experts available now in Live!

Get 1:1 Help Now