Filtering Records In An Ajax Grid

The first thought that comes to my mind when working with grids is 'how can I filter/search within the results?' Grids are very nice - especially the new Ajax grid in ColdFusion 8. But they can be a bit difficult to quickly get at something that you may know exists. Here is one method to do filtering.

I've chosen to highlight this method on purpose - there is another method that may be a bit simpler but I wanted to highlight something cool with this method. It is also possibly a preferable method when dealing with grids that have many pages of data and are a little less responsive the others because it does not filter the results as you type. So on to the code:

The first chunk I'll show is the grid and the search box and search button. In the grid I am using a bind statement that calls my 'art' CFC. The CFC follows the necessary format for working with grids (for more on that see the developers guide).

<cfinput name="searchString" />
<cfinput type="button" name="searchBtn" value="Search" onclick="ColdFusion.Grid.refresh('artGrid', false);" />
<cfgrid
   format="html"
   name="artGrid"
   pagesize="5"
   sort="true"
   bind="cfc:art.getArt({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection}, getSearchString())">

   
   <cfgridcolumn name="artid" header="ID" />
   <cfgridcolumn name="artname" header="Name" />
   
</cfgrid>

The first four arguments that I'm passing are needed to do the dynamic paging. The thing that is cool about this bind statement is the fifth argument - getSearchString() - which is not a form field value but a JavaScript function that looks like this:

<script>
getSearchString = function(){
   var s = ColdFusion.getElementValue('searchString');
   return s;
}
</script>

Can't get much simpler then that really. This function gets the current value of the cfinput 'searchString' and returns it. So on load of the page this will normally be blank - which passes an empty string to the CFC (the query in my CFC will only limit the results if the search string exists).

So how does the search happen? Well it won't happen when I type in the cfinput because I'm not binding directly to that control. I handle the refresh in the onClick of the search button (you could also make it a submit button and handle the refresh onSubmit of the form so that a simple press of the enter key will do the search).

onclick="ColdFusion.Grid.refresh('artGrid', false);"

So here I refresh the artGrid. At this point the bind statement is reevaluated and the getSearchString function will now return the searchString and the CFC can limit the results.

Here is an online demo.

Comments
Nice example.. Is there a possible way to open up cfwindow while I click on data grid filed -- which will open up cfwindow related to clicked content?

Thanks.
# Posted By Abul | 8/9/07 2:11 PM
Absolutely. You could bind to the grid in the source attribute of your cfwindow. The window could then access that dynamic variable (like a primary key) to get details.
# Posted By todd sharp | 8/9/07 2:18 PM
I should mention - if you wanted the window to automatically fire when the grid row is selected you'll have to tap into the underlying Ext grid object and register a listener.
# Posted By todd sharp | 8/9/07 2:28 PM
Or use cfajaxproxy :)

But now that I think of it - it would probably be pretty annoying to open a window everytime you select a different row in the grid.
# Posted By todd sharp | 8/9/07 2:34 PM
I need to know how to make my own application and database page the results. Do you have an example of this?
# Posted By Keith Korry | 8/9/07 2:53 PM
Not sure I follow your question Keith - can you explain a little more what you're trying to do?
# Posted By todd sharp | 8/9/07 2:54 PM
Abul - check Ray's post for an example of reacting to a grid row selection change:

http://www.coldfusionjedi.com/index.cfm/2007/8/9/R...
# Posted By todd sharp | 8/9/07 3:08 PM
Excellent help. Whenever you come to Houston -- I will buy you a launch!

:) Abul
# Posted By Abul | 8/10/07 9:25 AM
Typo :{

Should be "lunch"

Abul
# Posted By Abul | 8/10/07 9:27 AM
Great example. I have a question though. What if you want to have not only a searchstring but the value from, lets say, a dropdown? It it possible to get both of those values inside if the single getSearchString() function? I tried playing around with this and came up with nothing but making multiple functions.

--Dave
# Posted By Dave Ferguson | 8/10/07 10:50 AM
How about this Dave:

getSearchString = function(){
   var s = {};
   s.string = ColdFusion.getElementValue('searchString');
   s.col = ColdFusion.getElementValue('searchCol');
   return s;
}

This will pass a struct in the 5th position - and can be handled accordingly. :)
# Posted By todd sharp | 8/10/07 11:42 AM
Ok now I feel stupid. I should have been able to figure that out. Thanks todd.

--Dave
# Posted By Dave Ferguson | 8/10/07 2:27 PM
Nah no worries - it isn't immediately obvious in my opinion. You happened to catch me at an alert moment - normally I would have fumbled with that for an hour before I figured out it was that easy ;)
# Posted By todd sharp | 8/10/07 2:42 PM
Todd, Is there a way to capture a URL variable and pass it as well?

For example if I have a cfmenu that points to a separate page of listings by category (want to keep a separate url for SEO reasons). I am very new to this ajax thing but have been inhaling it from all of the blogs recently.
# Posted By Cyrill | 8/10/07 4:29 PM
technically you could just set the value of the cfinput to the url var. something like this:

<cfinput name="searchString" value="#url.foo#" />

That should initally filter based on the url far foo - is that what you meant?
# Posted By todd sharp | 8/10/07 4:38 PM
How do you tell the flash form to page a certain amount of records.
Page 1
Page 2

Like 10 per page...
# Posted By Keith Korry | 8/10/07 9:30 PM
Flash forms are a whole different topic Keith. This demo is for the ColdFusion 8 Ajax grid. There is nothing built in for the flash grid for paging.
# Posted By todd sharp | 8/10/07 10:37 PM
Todd,

Thank you so much. The hidden field with a URL parameter worked perfectly.

Now, is there a way to bind different columns to different functions in a CFC, for example: selecting (clicking) one column loads company data, clicking on a city name in the CITY column displays only entries from that city? I looked at Ray's blog but it looks like his use of the cfajaxproxy selects the whole row.
# Posted By Cyrill | 8/11/07 1:59 AM
I mean coldfusion (flash) grid.
If I make a grid from a sql db connection and I want only 10 per page How do I make it show only 10.
# Posted By Keith Korry | 8/11/07 4:40 AM
CFGRID- Pagesize The number of rows to display per page for a dynamic grid. If the number of available rows exceeds the page size, the grid displays only the specified number of entries on a single page, and the user navigates between pages to show all data. The grid retrieves data for each page only when it is required for display.

This attribute is ignored if you specify a query attribute.
# Posted By Keith Korry | 8/11/07 2:45 PM
Nope Still Doesnt Work, Heres the code...
<cfquery name="R1" datasource="canadadb" maxrows="200">
SELECT *
FROM dbo.cities
</cfquery>
<cfform>
<cfgrid query="R1"
name="R1"
format="html"
pagesize="10"
striperows="yes">
<cfgridcolumn name="id" header="First Name"/>
<cfgridcolumn name="weather" header="weather"/>
<cfgridcolumn name="cities" header="cities"/>
<cfgridcolumn name="province" header="province"/>
</cfgrid>
</cfform>
# Posted By Keith Korry | 8/11/07 4:00 PM
Hey Todd,

Great website!! It's always the first I go to in the morning. Keep up the genius (and very helpful) work.

I'm still getting my head around cf8's new bind stuff, and I was wondering if you could post your getArt.cfc contents for us to have a looksee? I'm getting a coldfusion error:

You cannot specify more arguments to a CFC function than it declares.
---- Error parsing bind cfc:customers.getData({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection}, getSearchString()) ----

Thanks again for all you do for us cfJunkies.
# Posted By Marty McGee | 8/12/07 5:21 PM
Todd,

I will second Marty's request for the art.cfc. I figured out that you need a getSearchString argument in the cfc or else you get the error Marty mentions but the filtering does not work. No error but the dataset does not get filtered.

Thanks for providing this example. I very much appreciate it!
# Posted By Ray Buechler | 8/16/07 3:36 PM
Marty: Sorry! Did not see your request earlier...

For those who are interested the cfc is now an attachment to this blog post. Download above.

HTH.
# Posted By todd sharp | 8/16/07 7:14 PM
How would you enhance this to filter the grid as you type and remove the submit button?
# Posted By Howie | 3/28/08 3:40 PM
Just add an onchange to the text input that does the same thing as the button. But I'd strongly suggest not doing that since that would mean every character entered would fire off an Ajax request and that could get messy/laggy real quick.
# Posted By todd sharp | 3/28/08 9:54 PM
Hi i need some help with this:
I have two grids (grid1 & grid2). I want grid2 results to be filtered based on grid1 selection (which i was able to do) but initially on page load i want all records to show up on grid2.
This is what i have:
function init(){
//window.filterParam = '';
grid = ColdFusion.Grid.getGridObject("grid1");
grid.addListener("rowclick",setValue);
}
function setValue()
{
//window.filterParam = '';
var grid = ColdFusion.Grid.getGridObject("grid1");
var record = grid.getSelections();
window.filterParam = record[0].data.FIRSTNAME;

var grid2 = ColdFusion.Grid.getGridObject("grid2");
grid2.refresh();
}
function getValue()
{
return window.filterParam;
}

And the bind attribute for grid2 has:
<cfgrid format="html" name="employeeGrid"
bind="cfc:employeeService.getData({cfgridpage},{cfgridpagesize},
   {cfgridsortcolumn},{cfgridsortdirection},getValue())"

Whats happening is, on page load all records display on grid2. But when a row from grid1 is clicked, still grid2 shows all records. Only when a second row from grid1 is clicked, results pertaining to previous grid1 selection (not current selection) get filtered in grid2.

I printed 'window.filterParam' when every grid1 row is clicked and it seems to be getting the right value, but for some reason grid2 results aren't getting refreshed.

please someone help!!
# Posted By sneha | 8/1/08 9:23 PM

Calendar

Sun Mon Tue Wed Thu Fri Sat
     12
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31       

Subscribe

Enter your email address to subscribe to this blog.

Tags

actionscript ajax blogging cfsnippets coldfusion flash forms flex funny stuff misc model-glue off topic personal project learn slidesix sql

Recent Comments

Adding Auto Generated Code Downloads to BlogCFC
ada5fsa said: http://www.mountwashingto... http://extjs.com/forum/me... h... [More]

BlogCFC Survey #2
ada5fsa said: http://www.mountwashingto... http://extjs.com/forum/me... h... [More]

Most Difficult Captcha Of All Time
kokkooo said: [url=http://synchrophase.info/...]消費者金融 ブラック[/url] [url=http://geji-geji.com/]消費者金融ブラック[/url] [url=http... [More]

Most Difficult Captcha Of All Time
kokkooo said: <a href="http://synchrophase.info/...">消費者金融 ブラック</a> <a href="http://gej... [More]

Using A PlayStation 2 HDD In Your PC
Vodin said: Well dang. Thanks man, my uncle who owns a Video Game shop had a PS2 harddrive in the junk box, I sn... [More]

RSS


coldfusionbloggers

FullAsAGoog MXNA

Consumed By Feed-Squirrel.com