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

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

DJPr0Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
_agx_Connect With a Mentor Commented:
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
 
RickEpnetConnect With a Mentor Commented:
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
 
DJPr0Author Commented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
RickEpnetCommented:
The Null value is in the second select statement?
0
 
_agx_Commented:
(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
 
DJPr0Author Commented:
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
 
RickEpnetCommented:
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
 
DJPr0Author Commented:
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
 
_agx_Connect With a Mentor Commented:
> <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
 
DJPr0Author Commented:
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
 
_agx_Commented:
> 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
 
DJPr0Author Commented:
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
 
_agx_Connect With a Mentor Commented:
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
 
DJPr0Author Commented:
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
 
DJPr0Author Commented:
Thanks!
0
All Courses

From novice to tech pro — start learning today.