Can you search a CFGRID?

This post is more than 2 years old.

Here is an interesting question brought to me by Daniel F. Given a CFGRID on a page, is there a way to search through the data? He isn't talking about searching against the database and showing the results in the grid, but rather, simply searching through the client side data within the grid itself. I did some digging, and this is what I came up with. Please remember that ColdFusion 9 shipped with a much newer version of Ext than ColdFusion 8. So if you are still on the previous version of ColdFusion, the following code may not work.

Let's begin by looking at the basic GRID code. I won't explain everything here as I assume most folks already know how the grid works. I've added a simple text field as well that will be used for our client side searching.

<cfform name="test">

<b>Search:</b> <input type="text" id="search"><br/> <cfgrid autowidth="true" name="entries" format="html" width="600" bind="url:getentries.cfm?page={cfgridpage}&pagesize={cfgridpagesize}&sort={cfgridsortcolumn}&dir={cfgridsortdirection}"> <cfgridcolumn name="id" display="false"> <cfgridcolumn name="body" display="false">

<cfgridcolumn name="title" header="Title"> <cfgridcolumn name="posted" header="Posted"> </cfgrid> </cfform>

<cfset ajaxOnLoad("init")>

As a quick aside, getentries.cfm is a simple CFM that performs queries against a BlogCFC database. Note that I'm asking ColdFusion to run a JavaScript function called init on load. Let's take a look at that now.

<script> var grid; function init() { grid = ColdFusion.Grid.getGridObject('entries')

$("#search").change(function() {
	var val = $(this).val().toLowerCase()
	var sel = grid.getSelectionModel()
	var data = grid.getStore()
	var count = -1
	for(var i=0; i &lt; data.getCount(); i++) {
		var r = data.getAt(i)
		if(r.get("TITLE").toLowerCase().indexOf(val) &gt;= 0) {
			sel.selectRow(i)
			return
		}
	}
})

} </script>

Ok, so lets take this line by line. I grab an pointer to the CFGRID object by using ColdFusion.Grid.getGridObject. This is done one time. I'm using jQuery (included earlier in the script but not shown here) so I can write a quick change handler on the form field. (And again, I probably should have used keyup instead. Doesn't really matter for this demo however.)

I begin by lowercasing the value from the search field. Next, I ask the grid for it's selection model. This is an abstract object that will let me work with selections and the grid. As another aside, I didn't just make all this stuff up - but rather spent my time digging at the Ext Docs. Anyway, next I ask for the data store. Like the selection model, this result is tool that lets me interact with the data on the grid.

Once I have that, it then becomes pretty trivial. I loop over the data and use getAt to return record objects. Once there, I use get("TITLE") to get the value in the title column. Obviously that would be data dependent. Also note I convert it to lower case and check to see if my match term matches anywhere in there. You can use a "complete" match for searching as well. Finally if a match is found, I use the selection model object to select it.

Here is a quick example of what the grid looks like on start:

And here it is after I entered a search term:

Exciting stuff, right? I should point out that the Ext API is very powerful. There were other related things I could have done as well. For example, the data API contains an each function. It would let me run an anonymous function for each row. I didn't use that though as I wanted to be able to leave right away on a match. There is also a cool filter that, well, filters. That wouldn't select of course, but would do the search very nicely as well. Anyway, I've posted the complete source below, minus the source for getentries.cfm. If someone needs that, speak up.

<html>

<head> <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></script> <script> var grid; function init() { grid = ColdFusion.Grid.getGridObject('entries')

$("#search").change(function() {
	var val = $(this).val().toLowerCase()
	var sel = grid.getSelectionModel()
	var data = grid.getStore()
	var count = -1
	for(var i=0; i &lt; data.getCount(); i++) {
		var r = data.getAt(i)
		if(r.get("TITLE").toLowerCase().indexOf(val) &gt;= 0) {
			sel.selectRow(i)
			return
		}
	}
})

}

</script> </head> <body>

<cfform name="test">

<b>Search:</b> <input type="text" id="search"><br/> <cfgrid autowidth="true" name="entries" format="html" width="600" bind="url:getentries.cfm?page={cfgridpage}&pagesize={cfgridpagesize}&sort={cfgridsortcolumn}&dir={cfgridsortdirection}"> <cfgridcolumn name="id" display="false"> <cfgridcolumn name="body" display="false">

<cfgridcolumn name="title" header="Title"> <cfgridcolumn name="posted" header="Posted"> </cfgrid> </cfform>

<cfset ajaxOnLoad("init")>

</body> </html>

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Archived Comments

Comment 1 by Sean Coyne posted on 4/29/2010 at 3:42 PM

You can do it w/o jQuery using the built in bits as well. I wrote up a post a while ago here http://www.n42designs.com/b...

Comment 2 by Raymond Camden posted on 4/29/2010 at 3:51 PM

Except this is exactly _not_ what he wanted though - I kinda pointed that out on top. :)

Comment 3 by Mike Kirby posted on 4/29/2010 at 5:52 PM

This is exactly what I've been looking for. Can I get a copy of your code for getEntries.cfm? Also, can you point me to the documentation / example of the filter?

Comment 4 by Raymond Camden posted on 4/29/2010 at 5:55 PM

Here is getentries: http://pastebin.com/kEHwFQy2

As for filter, if you go to the Ext Docs I linked to above, you can see it. It is a method of the Store object. (Sorry to say RTFM, but I don't think the Ext docs support deep linking?)

Comment 5 by Mike Kirby posted on 4/29/2010 at 5:57 PM

Great! Thanks.

Comment 6 by Jeremy Moore posted on 4/29/2010 at 6:06 PM

Deep link to Ext Store object Doc: http://www.extjs.com/deploy...

Comment 7 by Raymond Camden posted on 4/29/2010 at 6:10 PM

Thanks Jeremy. (I was being lazy. ;)

Comment 8 by Don Blaire posted on 4/29/2010 at 9:30 PM

I love these posts. This is stuff I can really put to use. Thx.

Comment 9 by Dan Fredericks posted on 6/1/2010 at 5:54 PM

Hey,
I use this code exactly in an example page, and it works great. The instant I try to use a bind to a cfc, it does not work. I also have a select box that filters to grid to the proper data. I then use a text field to type in my item to search. When I do this, i get an error:

r.get("fieldName")..toLowerCase().indexOf(val) >= 0
is not a function.

Any ideas from anyone as to why this could be? if it is an issue, anybody know a solution around this? I have been playing with this code for about 2 hours, so I thought it was time to ask people...

thanks
dan

Comment 10 by Raymond Camden posted on 6/1/2010 at 6:27 PM

It sounds like you have a few issues here. Let's focus on one at a time. So when you switch to a CFC, and you say 'it doesnt work', how does it not work? Just the search, or loading data in general?

Comment 11 by Dan Fredericks posted on 6/1/2010 at 8:49 PM

Ok, so here is the situation:
The page has a select box where the user selects the item they want, then the bind with the grid displays data in the grid about that item. eventually I'll have to figure out how to use a select box in the grid for the user to change a data from a C to a G and save that in the db.
So that is the page. Now the user has the option to search the grid to find a record. I got your code working on a separate page, however that page just displayed data so I used the query attribute, not the bind. Now I am using the bind.
CF code: (search on the d046d or NIIN column)

<cfgrid name="ARSELgrid2" width="900px" height="150px" format="html" pagesize="20" bind="cfc:NewTest.testComponent.ARSELallValues({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection},{AEL})" sort="true" gridlines="true" bindonload="false" onchange="cfc:NewTest.testComponent.editData({cfgridaction},{cfgridrow},{cfgridchanged})" selectmode="edit">
<cfgridcolumn name="pc" header="PC" select="true" values="P,C" valuesdisplay="P,C">
<cfgridcolumn name="d046d" header="NIIN" select="false" >
<cfgridcolumn name="c004c" header="ItemName" width="200" select="false" >
<cfgridcolumn name="sd016" header="PN" select="false">
<cfgridcolumn name="c035" header="CAGE" select="false">
<cfgridcolumn name="e007_9" header="KitQty" select="false">
<cfgridcolumn name="e007_1" header="ColNo1" select="false">
<cfgridcolumn name="e007_2" header="ColNo2" select="false">
<cfgridcolumn name="e007_3" header="ColNo3" select="false">
<cfgridcolumn name="e007_4" header="ColNo4" select="false">
<cfgridcolumn name="e007_5" header="ColNo5" select="false">
<cfgridcolumn name="e007_6" header="ColNo6" select="false">
<cfgridcolumn name="e007_7" header="ColNO7" select="false">
<cfgridcolumn name="e007_8" header="ColNo8" select="false">
</cfgrid>
<br>
<cfinput type="text" name="keywordSearchGrid" id="keywordSearchGrid" size="100" >

Comment 12 by Raymond Camden posted on 6/2/2010 at 11:56 PM

You still didn't answer the question. Is the issue that the data never loads, or that the ability to search it is failing?

Comment 13 by Dan Fredericks posted on 6/3/2010 at 3:25 PM

Hey sorry if I did not answer the question. Well, the basic answer is it does not search...
I am not sure what happens. The data loads in the grid just fine, however when I try to search the grid, I get this error:
allowSearch.get("D046D").toLowerCase is not a function.
If I just run your example code, it works just fine...when I add in a select box to filter the grid data, and a bind in the cfgrid, things don't work.

Does this answer better?

thanks
Dan

Comment 14 by Raymond Camden posted on 6/3/2010 at 3:28 PM

Check your case - I think you create it in lowercase in your code.

Comment 15 by Dan Fredericks posted on 6/3/2010 at 3:37 PM

Hey, sorry, but which code should be Capital?
You see my grid code above, now here is my js code. Should I make all my gridColumn names Capital, or lowercase, and is that the issue? Sorry I am not clear...
var gridAllow;
function init() {

gridAllow = ColdFusion.Grid.getGridObject('ARSELgrid')

$("#keywordSearchGrid").change(function() {
var val = $(this).val().toLowerCase()
var sel = gridAllow.getSelectionModel()
var data = gridAllow.getStore()
var count = -1
for(var i=0; i < data.getCount(); i++) {
var allowSearch = data.getAt(i)
if(allowSearch.get("D046D").toLowerCase().indexOf(val) >= 0) {
sel.selectRow(i)
return
}
}
})

Comment 16 by Raymond Camden posted on 6/3/2010 at 3:40 PM

The string inside get.

allowSearch.get("d046d")

Comment 17 by Dan Fredericks posted on 6/3/2010 at 4:08 PM

ok, so maybe here is the issue, i am tying in a number into the search string. When I do that, the error occurs. If I switch this line
if(allowSearch.get("D046D").toLowerCase().indexOf(val) >= 0) {
to another column that is a string, the search works fine. So, I guess I need to figure out how to change the code above to work for numbers.
Sorry I did not see this before...

Comment 18 by Raymond Camden posted on 6/3/2010 at 4:56 PM

If the value is a number, do you want to match the number, or treat it like a string. Ie, do you want "1 matches 100" or "1 matches only 1"

Comment 19 by Dan Fredericks posted on 6/3/2010 at 5:09 PM

Hey, found out I don't need to search on the number field, so I got it working thanks to you.
So, side question, have you or anyone else out there used drop down boxes in grids? if so, is there a way to populate them from a bind(query)? I have a drop down in column 1. The user selects P or C in the drop down. This gets saved in the DB. when the page is reloaded, is there a way to populate the drop down with the value saved in the db?

thanks
Dan

Comment 20 by Raymond Camden posted on 6/3/2010 at 5:11 PM

I haven't. I can say though that the Ext grid upon which cfgrid is based, is -very- powerful. If you have trouble doing it via CF's interface, you can always use the grid natively.

Comment 21 by Ron posted on 6/30/2010 at 6:04 PM

@Ray

Great stuff! Your site regularly saves my bacon! Thanks to Jedi Master Ray.

Also, could you repost the getentries code again? The last link doesn't work anymore.

Thanks!

Comment 22 by Raymond Camden posted on 6/30/2010 at 6:09 PM

This should be it:

http://pastebin.com/ySwt7xWm

NOTICE! This CFM should be locked down a bit more. For example, validation MUST be added to url.sort/url.dir. But for a demo, it works.

Comment 23 by AndyG posted on 7/8/2010 at 11:30 PM

I am interested in embedding a jsgrid into a cf page using ColdFusion MX7. I tried to use SigmaGrid which is very similar to ExtGrid and a .cfc to search the database, then returning the result in json format. I have the grid loading, but the data gets lost somewhere. Any help will be appreciated. Thank you!

Comment 24 by Raymond Camden posted on 7/8/2010 at 11:39 PM

CF didn't add "native" JSON parsing for CFCs till 8. If you want to use JSON in CF7, you must call a CFM. That CFM runs your CFC and then translates it to JSON. To do that, use JSON.cfc:

Ugh - I was going to point to the site where I got the JSON cfc, but it appears to be hacked.

http://www.epiphantastic.co...

Click that only if you have a good firewall up. If you download Lighthouse Pro, I've got a copy of that CFC in the helpers folder.

Comment 25 by Brian posted on 8/20/2010 at 8:49 PM

So I am having sort of the same problem as the above individual that is the search function doesn't work when binding the grid to a cfc

Comment 26 by Raymond Camden posted on 8/23/2010 at 6:10 AM

In what way does it not work? Is it online where we can see?

Comment 27 by Brian posted on 8/24/2010 at 1:05 AM

Well I used your code shown above the only difference I can see between the two is that I am referencing a cfc as opposed to a url. I cannot post the actual code online (proprietary issues) but I will post the general structure as soon as I can.

Comment 28 by Brian posted on 8/24/2010 at 1:07 AM

sorry what I mean by not working is that I type a search term into the field and hit enter and nothing happens

Comment 29 by Brian posted on 8/24/2010 at 1:57 AM

So here is my code...

<html>
<head>
<!---Java script to perform a search on grid data--->
<script type="text/javascript" src="http://ajax.googleapis.com/..."></script>
<script>
var grid;
function init()
{
grid = ColdFusion.Grid.getGridObject('my_grid')
$("#search").change(function() {
var val = $(this).val().toLowerCase()
var sel = grid.getSelectionModel()
var data = grid.getStore()
var count = -1
for(var i=0; i < data.getCount(); i++) {
var r = data.getAt(i)
if(r.get("my_column").toLowerCase().indexOf(val) >= 0) {
sel.selectRow(i)
return
}
}
})
}
</script>
</head>
<body>
<cfform action="for_post.cfm" method="post">
<cflayout type="tab" tabHeight="750">
<cflayoutarea title="star">
<table align="left" bgcolor=C0C0C0 border="1">
<tr>
<td align="left">
Search:<input type="text" name="search" >&nbsp
</td>
</tr>
<tr>
<td align="center"> Ships of Interest<br>
<cfgrid name="my_grid"
format="html"
pagesize="32"
striperows="yes"
selectmode="edit"
delete="yes"
bind="cfc:edit.getAddress({cfgridpage}, {cfgridpagesize}, {cfgridsortcolumn}, {cfgridsortdirection})"
onchange="cfc:edit.editInfo({cfgridaction},{cfgridrow},
{cfgridchanged})">

<cfgridcolumn name="my_column" header="Number">
</cfgrid>
</td>
</cflayoutarea>
</cflayout>
</cfform>
<cfset ajaxOnLoad("init")>
</table> </body>
</html>

And here is the code for my components I am referencing a local database for development I am not sure wheter this matters

<cffunction name="getAddress" access="remote" returnType="struct">
<cfargument name="page" type="numeric" required="yes" />
<cfargument name="pageSize" type="numeric" required="yes" />
<cfargument name="gridsortcolumn" type="string" required="no" default="" />
<cfargument name="gridsortdir" type="string" required="no" default="" />

<!---Set Local Variables--->
<cfset var ship="" />

<!---Get ship data--->
<cfquery name="information" datasource="#THIS.dsn#">
SELECT *
FROM information
<cfif ARGUMENTS.gridsortcolumn NEQ ""
and ARGUMENTS.gridsortdir NEQ "">
ORDER BY #ARGUMENTS.gridsortcolumn# #ARGUMENTS.gridsortdir#
</cfif>
</cfquery>

<!---Return as grid Structure--->
<cfreturn QueryConvertForGrid(information,
ARGUMENTS.page,ARGUMENTS.pagesize) />
</cffunction>

<cffunction name="editInfo" access="remote">
<cfargument name="gridaction" type="string" required="yes">
<cfargument name="gridrow" type="struct" required="yes">
<cfargument name="gridchanged" type="struct" required="yes">

<!--- Local variables --->
<cfset var colname="">
<cfset var value="">

<!--- Process gridaction --->
<cfswitch expression="#ARGUMENTS.gridaction#">
<!--- Process updates --->
<cfcase value="U">
<!--- Get column name and value --->
<cfset colname=StructKeyList(ARGUMENTS.gridchanged)>
<cfset value=ARGUMENTS.gridchanged[colname]>
<!--- Perform actual update --->
<cfquery datasource="#THIS.dsn#">
UPDATE information
SET #colname# = '#value#'
WHERE Number = '#ARGUMENTS.gridrow.Number#'
</cfquery>
</cfcase>
<!--- Process deletes --->
<cfcase value="D">
<!--- Perform actual delete --->
<cfquery datasource="#THIS.dsn#">
DELETE FROM information
where Number = '#ARGUMENTS.gridrow.Number#'
</cfquery>
</cfcase>
</cfswitch>
</cffunction>

Comment 30 by Raymond Camden posted on 8/24/2010 at 9:30 PM

I'd start with simple debugging statements using console.log. For example, inside your change function, add a console.log for the val variable. Ensure it is what you tink it is. Before the loop, add a log for data.getCount(), ensure it makes sense. Etc, etc etc.

Comment 31 by Dan Fredericks posted on 8/25/2010 at 3:48 PM

Brian,
Ray helped me a lot with my code and one thing I had was a lot of data in the grid...actually in Firefox it gives me a slow/unresponsive error each time i load the grid...maybe 2000 records. Anyway, My users needed to be able to search through any of those records, and using a bind and pagesize did not work for me because this code will not select an item that is not part of your first 32 items for pagesize.
So, if you have more than 32 records, using a bind may not be the way to go.

Ray, I think I am right about all this...

Dan

Comment 32 by Raymond Camden posted on 8/25/2010 at 3:53 PM

I definitely would not try to load 2000 records into the grid at once. I'd use a bind or another method.

Comment 33 by Dan Fredericks posted on 8/25/2010 at 5:48 PM

Ray,
I know it is not great, but the users need to search through the entire list, they can't have pagesize...i am not sure how to load the data differently so the search will work.

Comment 34 by Raymond Camden posted on 8/25/2010 at 6:56 PM

You can still have them search the entire db - just don't use cfgrid. Use another solution. Here is one example: http://www.coldfusionjedi.c...

Comment 35 by Brian posted on 8/26/2010 at 1:29 AM

thanks I got it working but using a different approach I think it was the amount of records I was dealing with, real time searching was just not possible but when I bound the search to a button it worked. I am throughly new to coldfusion (bout a month) so I am still learning the ins and outs of the language but it is nice

Comment 36 by Billie JO posted on 8/27/2010 at 8:17 PM

Could this also be used to remember the row that was selected in a grid at the time the page is submitted? My grid is bound to a cfc, (bind="cfc:kpi_maintenance_functions.fill_grid({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection},{area},{category},{subcategory},{yearmonth})"), when the user submits the page the first row is highlighted instead of the the row the user selected.

Comment 37 by Raymond Camden posted on 8/27/2010 at 11:18 PM

The selected row should be in the form scope.

Comment 38 by Billie JO posted on 8/27/2010 at 11:33 PM

Would you be able to tell me how to reference it and where? I haven't seen any posts that have mentioned this. I have all the other parts working thanks to your site.

Comment 39 by Raymond Camden posted on 8/28/2010 at 12:19 AM

Try dumping the form scope. It should be in there. (Although I discovered a bug with this in 901 with multiselect grids -which isn't what we are talking here.

Comment 40 by mona posted on 2/9/2011 at 12:39 AM

The grid populated initally but the search does not work.

Comment 41 by Raymond Camden posted on 2/11/2011 at 2:15 AM

Are you on CF9?

Comment 42 by mona posted on 2/11/2011 at 3:19 AM

Yes i am on CF9

Comment 43 by mona posted on 2/11/2011 at 3:21 AM

Ray,
I was able to load and do the search. I do want to be able search the entire result from the database not just the initial grid that loads.

Comment 44 by Raymond Camden posted on 2/11/2011 at 5:23 PM

To do that you would need entirely different code. You can make your cfgrid use bind and in the bind include a text field. Basically a modified form of the grid you see here.

Comment 45 by mona posted on 2/11/2011 at 9:28 PM

Ray,
Please point me to an example.

Comment 46 by Raymond Camden posted on 2/12/2011 at 12:12 AM

I believe I have one - but the entry title isn't coming to mind. Best I can suggest now is to do a search here for cfgrid.

Comment 47 by Michael posted on 12/29/2011 at 2:49 PM

Please, does anyone know whether it is possible to extent the context menu of the coloums to do the filtering like in this example: http://ccinct.com/lab/filte... while using CFGRID?