Filter CFGRID based on drop down boxes

Question

I am new to ColdFusion and I am struggling to figure out how to do some simple functions that I could do in MS Access easily.

Here is what I am trying to do: I want to have a cfgrid filter its data based on a dropdown box. Then filter that data even further based on another dropdown box (if needed).

Any help would be greatly appreciated.

Example:

    <html>

<!---Grid Source---> 
<cfquery name="getArtists" datasource="cfartgallery">
    SELECT A.*
    FROM ARTISTS A
    WHERE
        1=1
    <cfif structKeyExists(form,'dropdownbox1')>
      AND STATE like <cfqueryparam value="%#dropdownbox1#%" cfsqltype="CF_SQL_VARCHAR" maxlength="255">
      <cfif structKeyExists(form,'dropdownbox2')>
      AND LASTNAME like <cfqueryparam value="%#dropdownbox2#%" cfsqltype="CF_SQL_VARCHAR" maxlength="255">
      </cfif> <!--- DropdownBox 2 --->
    </cfif> <!--- DropdownBox 1 --->
    ORDER BY A.LASTNAME, A.FIRSTNAME
</cfquery>


<head>
</head>
<body style="padding:20px;">

<cfform>


<cfformgroup type="horizontal" style="backgroundAlpha:0;font-size:12;color:##000;">
    <cfformgroup type="vertical" width="100">

<!---Query for STATE--->
<cfquery name="getState" datasource="cfartgallery">
    SELECT A.STATE   
    FROM ARTISTS A
</cfquery>

<!---STATE Dropdown---> 
<cfselect name="dropdownbox1" query="getState" queryposition="below" 
    label="State:"  value="STATE" display="STATE"  
    required="no" 
    style="backgroundAlpha:0;" width="125">
    <option></option>
</cfselect>

    </cfformgroup>
    <cfformgroup type="vertical" width="100">

<!---Query for LastName--->
<cfquery name="getLast" datasource="cfartgallery">
    SELECT A.LASTNAME   
    FROM ARTISTS A
</cfquery>

<!---LastName Dropdown---> 
<cfselect name="dropdownbox2" query="getLast" queryposition="below" 
    label="Last Name:"  value="LASTNAME" display="LASTNAME"  
    required="no" 
    style="backgroundAlpha:0;" width="125">
    <option></option>
</cfselect>
    </cfformgroup>
    </cfformgroup>

     <cfformitem type="spacer" height="20"/>


<!---Grid---> 
        <cfgrid name="myGrid" query="getArtists" format="html">
            <cfgridcolumn name="ARTISTID" header="ARTISTID" width="75" headeralign="center" dataalign="center"/>
            <cfgridcolumn name="FIRSTNAME" header="FIRSTNAME" width="100" headeralign="center" dataalign="center"/>
            <cfgridcolumn name="LASTNAME" header="LASTNAME" width="75" headeralign="center" dataalign="center"/>
            <cfgridcolumn name="ADDRESS" header="ADDRESS" width="100" headeralign="center" dataalign="center"/>
            <cfgridcolumn name="CITY" header="CITY" width="125" headeralign="center" dataalign="center"/>
            <cfgridcolumn name="STATE" header="STATE" width="125" headeralign="center" dataalign="center"/>
            <cfgridcolumn name="POSTALCODE" header="POSTALCODE" headeralign="center" dataalign="center"/>
            <cfgridcolumn name="EMAIL" header="EMAIL" width="125" headeralign="center" dataalign="center"/>
            <cfgridcolumn name="PHONE" header="PHONE" headeralign="center" dataalign="center"/>
            <cfgridcolumn name="FAX" header="FAX" headeralign="center" dataalign="center"/>
            <cfgridcolumn name="THEPASSWORD" header="THEPASSWORD" headeralign="center" dataalign="center"/>
         </cfgrid>

    </cfform>

</body>
</html>

UPDATE

The code Ben Koshy gave me worked like a charm one problem though when I change the form to format="flash" it gives me an error "There is no method with the name 'submit'". What do I need to do this resolve this?

Thanks Johnny

There was a recent question on a similar topic. Read this thread, http://stackoverflow.com/questions/14026510/how-would-you-go-about-executing-a-database-query-based-on-the-value-from-a-form. I'm not sure if the concepts will apply to a cfgrid, but it's worth a shot. — Dan Bracuk, Dec 27 '12 at 17:39
You are missing a submit of the form back to the original page when the filter is selected (state / name). — BKK, Dec 28 '12 at 07:43
One more question so could I also use the 2 dropdowns in conjunction with a filter as you type box? @Ben Koshy — Johnny McGahee, Dec 28 '12 at 14:16

BKK · Answer 1 · 2012-12-28T07:49:39.027

Most of the work required for this to work with CFGRID is on the CFQUERY side that is powering the grid. Here we simply check for existence of the dropdown box form field field names and then write some extra SQL that filters based on that criteria.

<cfquery name="getPeople" datasource="myDSN">
  SELECT
    column1, column2
  FROM
    People
  WHERE
    1=1
    <cfif structKeyExists(form,'dropdownbox1')>
      AND column1 like <cfqueryparam value="%#dropdownbox1#%" cfsqltype="CF_SQL_VARCHAR" maxlength="255">
      <cfif structKeyExists(form,'dropdownbox2')>
      AND column1 like <cfqueryparam value="%#dropdownbox2#%" cfsqltype="CF_SQL_VARCHAR" maxlength="255">
      </cfif> <!--- DropdownBox 2 --->
    </cfif> <!--- DropdownBox 1 --->
</cfquery>

<cfgrid ... >

UPDATE: Here's the example using your sample code.

<html>
<cfparam name="form.state" default="">
<cfparam name="form.lastname" default="">

<!---Grid Source---> 
<cfquery name="getArtists" datasource="cfartgallery">
    SELECT A.*
    FROM ARTISTS A
    WHERE
        1=1
    AND STATE like <cfqueryparam value="%#form.state#%" cfsqltype="CF_SQL_VARCHAR" maxlength="255">
    AND LASTNAME like <cfqueryparam value="%#form.lastname#%" cfsqltype="CF_SQL_VARCHAR" maxlength="255">
    ORDER BY A.LASTNAME, A.FIRSTNAME
</cfquery>


<head>
</head>
<body style="padding:20px;">

<cfform action="index.cfm" method="post">


<cfformgroup type="horizontal" style="backgroundAlpha:0;font-size:12;color:##000;">
    <cfformgroup type="vertical" width="100">

<!---Query for STATE--->
<cfquery name="getState" datasource="cfartgallery">
    SELECT A.STATE   
    FROM ARTISTS A
    WHERE
        1=1
        AND LASTNAME like <cfqueryparam value="%#form.lastname#%" cfsqltype="CF_SQL_VARCHAR" maxlength="255">
</cfquery>

<!---STATE Dropdown---> 
<cfselect name="state" query="getState" queryposition="below" 
    label="State:"
    value="STATE"
    display="STATE"  
    required="no"
    selected="#form.state#"
    onchange="submit();"
    style="backgroundAlpha:0;" width="125">
    <option></option>
</cfselect>


    </cfformgroup>
    <cfformgroup type="vertical" width="100">

<!---Query for LastName--->
<cfquery name="getLast" datasource="cfartgallery">
    SELECT A.LASTNAME   
    FROM ARTISTS A
    WHERE
        1=1
        AND STATE like <cfqueryparam value="%#form.state#%" cfsqltype="CF_SQL_VARCHAR" maxlength="255">
</cfquery>

<!---LastName Dropdown---> 
<cfselect name="lastname" query="getLast" queryposition="below" 
    label="Last Name:"  value="LASTNAME" display="LASTNAME"  
    selected="#form.lastname#"
    required="no" 
    onchange="submit();"
    style="backgroundAlpha:0;" width="125">
    <option></option>
</cfselect>

    <a href="index.cfm"><input type="button" value="Reset"></a>
    </cfformgroup>
    </cfformgroup>

     <cfformitem type="spacer" height="20"/>


<!---Grid---> 
        <cfgrid name="myGrid" query="getArtists" format="html">
            <cfgridcolumn name="ARTISTID" header="ARTISTID" width="75" headeralign="center" dataalign="center"/>
            <cfgridcolumn name="FIRSTNAME" header="FIRSTNAME" width="100" headeralign="center" dataalign="center"/>
            <cfgridcolumn name="LASTNAME" header="LASTNAME" width="75" headeralign="center" dataalign="center"/>
            <cfgridcolumn name="ADDRESS" header="ADDRESS" width="100" headeralign="center" dataalign="center"/>
            <cfgridcolumn name="CITY" header="CITY" width="125" headeralign="center" dataalign="center"/>
            <cfgridcolumn name="STATE" header="STATE" width="125" headeralign="center" dataalign="center"/>
            <cfgridcolumn name="POSTALCODE" header="POSTALCODE" headeralign="center" dataalign="center"/>
            <cfgridcolumn name="EMAIL" header="EMAIL" width="125" headeralign="center" dataalign="center"/>
            <cfgridcolumn name="PHONE" header="PHONE" headeralign="center" dataalign="center"/>
            <cfgridcolumn name="FAX" header="FAX" headeralign="center" dataalign="center"/>
            <cfgridcolumn name="THEPASSWORD" header="THEPASSWORD" headeralign="center" dataalign="center"/>
         </cfgrid>

    </cfform>

</body>
</html>

Thanks for your quick response but I still don't get it. @Ben Koshy — Johnny McGahee, Dec 27 '12 at 23:43

Filter CFGRID based on drop down boxes

1 Answers1