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 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:
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).
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.
Thanks.
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.
http://www.coldfusionjedi.com/index.cfm/2007/8/9/R...
:) Abul
Should be "lunch"
Abul
--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. :)
--Dave
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.
<cfinput name="searchString" value="#url.foo#" />
That should initally filter based on the url far foo - is that what you meant?
Page 1
Page 2
Like 10 per page...
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.
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.
This attribute is ignored if you specify a query attribute.
<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>
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.
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!
For those who are interested the cfc is now an attachment to this blog post. Download above.
HTH.
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!!
I am a bit of a noob to this. In your example iof passing more than one search param:
getSearchString = function(){
var s = {};
s.string = ColdFusion.getElementValue('searchString');
s.col = ColdFusion.getElementValue('searchCol');
return s;
}
Can you PLEASE tell me how I would reference this in my CFC and query??? And in the bind statement of the grid do you still use getSearchString() to pass the structure.
THANK YOU.
Im using some exact similar code to load 300 or so records from my DB to the GRID across 4 or 5 pages is pagesize.
This takes about 2-3 minutes for the initial Load & 100% of my CPU !!!
The same data when loaded into a CFGRID- format=APPLET runs in 20 seconds or less.
The Sql table that i extract the data into a CFC is only about 2000 rows in size & the query runs in less than 20 millisecs.
Can someone explain what am i missing ?
Thanks
As an added bonus question I have seen search text fields that filter the data as you type. How can that be done?