[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2014-03-15
15
Medium Priority
?
1,883 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 500 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

Assisted Solution

by:_agx_
_agx_ earned 1500 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 1500 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 1500 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article demonstrates how to create a simple responsive confirmation dialog with Ok and Cancel buttons using HTML, CSS, jQuery and Promises
Originally, this post was published on Monitis Blog, you can check it here . Websites are getting bigger and more complicated by the day. Video, images and custom fonts are all great for showcasing your product or service. But the price to pay in…
In this tutorial viewers will learn how to style a corner ribbon overlay for an image using CSS Create a new class by typing ".Ribbon":  Define the class' "display:" as "inline-block": Define its "position:" as "relative": Define its "overflow:" as …
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

834 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