Posted in ColdFusion | Posted on 04-29-2010 | 5,074 views
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.
2
3<b>Search:</b> <input type="text" id="search"><br/>
4<cfgrid autowidth="true" name="entries" format="html" width="600" bind="url:getentries.cfm?page={cfgridpage}&pagesize={cfgridpagesize}&sort={cfgridsortcolumn}&dir={cfgridsortdirection}">
5 <cfgridcolumn name="id" display="false">
6 <cfgridcolumn name="body" display="false">
7
8 <cfgridcolumn name="title" header="Title">
9 <cfgridcolumn name="posted" header="Posted">
10</cfgrid>
11</cfform>
12
13<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.
2var grid;
3function init() {
4 grid = ColdFusion.Grid.getGridObject('entries')
5
6 $("#search").change(function() {
7 var val = $(this).val().toLowerCase()
8 var sel = grid.getSelectionModel()
9 var data = grid.getStore()
10 var count = -1
11 for(var i=0; i < data.getCount(); i++) {
12 var r = data.getAt(i)
13 if(r.get("TITLE").toLowerCase().indexOf(val) >= 0) {
14 sel.selectRow(i)
15 return
16 }
17 }
18 })
19
20}
21</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.
2
3<head>
4<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></script>
5<script>
6var grid;
7function init() {
8 grid = ColdFusion.Grid.getGridObject('entries')
9
10 $("#search").change(function() {
11 var val = $(this).val().toLowerCase()
12 var sel = grid.getSelectionModel()
13 var data = grid.getStore()
14 var count = -1
15 for(var i=0; i < data.getCount(); i++) {
16 var r = data.getAt(i)
17 if(r.get("TITLE").toLowerCase().indexOf(val) >= 0) {
18 sel.selectRow(i)
19 return
20 }
21 }
22 })
23
24}
25
26</script>
27</head>
28<body>
29
30<cfform name="test">
31
32<b>Search:</b> <input type="text" id="search"><br/>
33<cfgrid autowidth="true" name="entries" format="html" width="600" bind="url:getentries.cfm?page={cfgridpage}&pagesize={cfgridpagesize}&sort={cfgridsortcolumn}&dir={cfgridsortdirection}">
34 <cfgridcolumn name="id" display="false">
35 <cfgridcolumn name="body" display="false">
36
37 <cfgridcolumn name="title" header="Title">
38 <cfgridcolumn name="posted" header="Posted">
39</cfgrid>
40</cfform>
41
42<cfset ajaxOnLoad("init")>
43
44</body>
45</html>
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?)
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
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" >
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
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
}
}
})
allowSearch.get("d046d")
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...
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
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!
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.
Ugh - I was going to point to the site where I got the JSON cfc, but it appears to be hacked.
http://www.epiphantastic.com/cfjson/
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.
<html>
<head>
<!---Java script to perform a search on grid data--->
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1/jque...;
<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" > 
</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>
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
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.
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.
Please point me to an example.
[Add Comment] [Subscribe to Comments]