asked on

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

SOLUTION

RickEpnet

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

D J

ASKER

Adding the attribute: bindonload="yes" works fine.

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

RickEpnet

The Null value is in the second select statement?

_agx_

(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

D J

ASKER

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

RickEpnet

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.

D J

ASKER

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.

SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

D J

ASKER

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


<cfset var data="">


<cfquery name="data" datasource="#variables.dsn#">
SELECT 0 AS Sortnum, 'ALL' as ProjNum FROM orderedmaterials
UNION
SELECT 1 AS Sortnum, ProjNum
FROM orderedmaterials


where ship in
(
<cfqueryparam value="#arguments.disipline1#" list="yes" cfsqltype="cf_sql_varchar">

)

ORDER BY Sortnum, ProjNum
</cfquery>

<cfreturn data>
</cffunction>

</cfcomponent>

_agx_

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


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

D J

ASKER

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?

SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

D J

ASKER

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

)

ASKER CERTIFIED SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

D J

ASKER

Thanks!