![[RSS]](/blog/template/rss.png)
Filtering a CFGrid
So you want to filter a CFGrid huh? ColdFusion makes this easy!
First you need a CFC method that will return the query used to populate the grid. This method should take the cfgrid parameters and the value you want to filter on.
data.cfc:
<cfcomponent>
<cffunction name="getdata" access="remote" output="false"
returntype="Any">
<cfargument name="page"
required="false" default="1" type="Numeric" />
<cfargument name="pagesize" required="false" default="10" type="Numeric"
/>
<cfargument name="sortcolumn" required="false"
default="" type="string" />
<cfargument name="sortdir"
required="false" default="ASC" type="string" />
<cfargument name="filter" required="false" default="" type="String"
/>>
<cfset q =
queryNew('id,name','integer,varchar') />
<cfset queryaddrow(q,1) />
<cfset querysetcell(q,'id',1)
/>
<cfset querysetcell(q,'name','sean') />
<cfset queryaddrow(q,1) />
<cfset
querysetcell(q,'id',2) />
<cfset
querysetcell(q,'name','phillip') />
<cfset queryaddrow(q,1) />
<cfset querysetcell(q,'id',3)
/>
<cfset querysetcell(q,'name','steve') />
<cfquery name="q" dbtype="query">
select * from q
<cfif
len(trim(arguments.filter))>
where name like <cfqueryparam cfsqltype="cf_sql_varchar"
value="%#arguments.filter#%" />
</cfif>
<cfif
len(trim(arguments.sortcolumn)) or len(trim(arguments.sortdir))>
order by #arguments.sortcolumn# #arguments.sortdir#
</cfif>
</cfquery>
<cfreturn
queryConvertForGrid(q,arguments.page,arguments.pageSize) />
</cffunction>
</cfcomponent>
<cffunction name="getdata" access="remote" output="false"
returntype="Any">
<cfargument name="page"
required="false" default="1" type="Numeric" />
<cfargument name="pagesize" required="false" default="10" type="Numeric"
/>
<cfargument name="sortcolumn" required="false"
default="" type="string" />
<cfargument name="sortdir"
required="false" default="ASC" type="string" />
<cfargument name="filter" required="false" default="" type="String"
/>>
<cfset q =
queryNew('id,name','integer,varchar') />
<cfset queryaddrow(q,1) />
<cfset querysetcell(q,'id',1)
/>
<cfset querysetcell(q,'name','sean') />
<cfset queryaddrow(q,1) />
<cfset
querysetcell(q,'id',2) />
<cfset
querysetcell(q,'name','phillip') />
<cfset queryaddrow(q,1) />
<cfset querysetcell(q,'id',3)
/>
<cfset querysetcell(q,'name','steve') />
<cfquery name="q" dbtype="query">
select * from q
<cfif
len(trim(arguments.filter))>
where name like <cfqueryparam cfsqltype="cf_sql_varchar"
value="%#arguments.filter#%" />
</cfif>
<cfif
len(trim(arguments.sortcolumn)) or len(trim(arguments.sortdir))>
order by #arguments.sortcolumn# #arguments.sortdir#
</cfif>
</cfquery>
<cfreturn
queryConvertForGrid(q,arguments.page,arguments.pageSize) />
</cffunction>
</cfcomponent>
You can see here that I am just hard coding the query in, you would replace this with your cfquery call to your database or a call to your service layer, etc. Once you have your query results, you want to use the queryConvertForGrid method to format the result to a value that CFGrid can work with.
You then need a cfgrid! The trick here is how to get the grid to refresh as the user types a value in the filter box. ColdFusion Ajax UI tools have bind expressions that let us bind one control to another, and to react to events.
index.cfm:
<cfform>
<p><label for="filter"><cfinput type="text" name="filter"
id="filter" /></p>
<cfgrid format="html"
bind="cfc:data.getData({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection},{filter@keyup})"
name="grd" bindonload="true">
<cfgridcolumn name="id" />
<cfgridcolumn name="name" />
</cfgrid>
</cfform>
<p><label for="filter"><cfinput type="text" name="filter"
id="filter" /></p>
<cfgrid format="html"
bind="cfc:data.getData({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection},{filter@keyup})"
name="grd" bindonload="true">
<cfgridcolumn name="id" />
<cfgridcolumn name="name" />
</cfgrid>
</cfform>
You can see my bind expression in the cf grid. It will call the getData method of my data.cfc and pass in the value from the "filter" input box whenever the key is released. ColdFusion provides other events you can react to as well, check the docs for details.
So now if you run index.cfm you will see a simple form with a text input and a grid. the grid will load when the page is run (bindonload="true") and as you type in the text box, the results are filtered.
EDIT: removed the example link as I have moved this blog to Railo
I've used your example
for
http://www.cfmzengarden.com/CFMZenGarden/051
hidden attribute like id, appear in the textbox once the name is clicked
on in the grid?
you would want that to change, but you can bind the values in the grid
to any other form fields. You can see an example here
http://nm1m.blogspot.com/2008/01/binding-to-html-c...
Sean -
Thanks for the response. Please allow me to clarify. I have
a form that includes a textbox called "clientID." ClientID
includes an onClick event that loads a cfwindow called
"userWin." UserWin loads a cfgrid populated with a list of
clients. Above the grid is a textbox called "filter" to
filter the clients.
Once the clients are filtered and the specific
client is found, I would like to click on the client and have the hidden
clientID attribute to populate the textbox "clientID" on the
parent page.
<cfinput name="clientID"
type="text"
maxlength="8"
onClick="ColdFusion.Window.show('userWin')"
bind="{clientLookup.client}"
tabindex="1"
value="#form.clientID#"/>
<cfwindow
name="userWin"
title="Client
Lookup"
initshow="false"
modal="true"
center="true"
height="330"
width="330">
<cfoutput>
<cfform name="tableform" >
<cfinput type="text" name="filter" >
<cfgrid format="html"
name="clientLookup"
pagesize="10"
selectmode="single"
autowidth="true"
bind="cfc:client.getClients({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection},
{filter@keyup})">
<cfgridcolumn name="client"
display="No"/>
<cfgridcolumn
name="firstname" header="FIRST NAME">
<cfgridcolumn name="lastname" header="LAST
NAME">
<cfgridcolumn name="organization"
header="ORGANIZATION">
</cfgrid>
</cfform>
</cfoutput>
</cfwindow>
I thought the code would help my explanation
:-)
Thanks.
the clientid cfinput within a cfform.
try something like
this:
<cfform>
<cfinput name="clientID"
type="text"
maxlength="8"
onClick="ColdFusion.Window.show('userWin')"
bind="{clientLookup.id}"
tabindex="1"
/>
</cfform>
<cfwindow
name="userWin"
title="Client
Lookup"
initshow="false"
modal="true"
center="true"
height="330"
width="330">
<cfoutput>
<cfform name="tableform" >
<cfinput type="text" name="filter" >
<cfgrid
format="html"
name="clientLookup"
pagesize="10"
selectmode="single"
autowidth="true"
bind="cfc:data.getdata({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection},
{filter@keyup})">
<cfgridcolumn
name="id" />
<cfgridcolumn name="name"
>
</cfgrid>
</cfform>
</cfoutput>
</cfwindow>
while reading your blog, it disappears
after about a minute.
I turned them off.
I've followed this blog and have a question of my own. Instead of filtering a cfgrid contents on a cfinput entry, I need to filter on the ID value from another cfgrid; like a Master/Detail relationship. I've had partial success using the following within the second cfgrid's definition:
<cfgrid name="Grid2"
width="450"
align="left"
format="html"
pagesize="10"
sort="true"
bind="cfc:myCFC.getDetailData({cfgridpage},
{cfgridpagesize},
{cfgridsortcolumn},
{cfgridsortdirection},
{Grid1.DetailID})"
It works fine for the first item in my Grid1 because the ID matches data from the cfc function populating Grid2. But when I move off of the matching Grid1 record and select a row without a matching ID value, Grid2 remains populated with the previous data and does not refresh.
Can you offer any insight in successfully filtering one cdgrid's data from another cfgrid?
Thanks in advance!
I just find this thread about CFgrid filtering. We are working on CF9.
I copy/paste the code, i haven't make any change. An error occurs when i launch the page : Error invoking CFC /DBUNIK/data.cfc : Error Executing Database Query