ColdFusion 8 Grid Filtering
Over the past couple of weeks I have been writing quick tutorials on extending the built in AJAX components. Today I would like to show you an example that I believe everyone is going to find useful. Once you have a data grid displaying your data there are usually 2 key operations users will perform. The 1st is pagination of records which is built into the grid and is pretty easy to use. The 2nd is filtering or how can I find exactly what I am looking for without browsing all of the records. Most filter examples I have seen where built outside of the grid but I want to run through an example that builds a filter into the grid.
Before we get started I think we should take a look at the outcome, this way the tutorial makes a little more sense. I do not have 8 running so I will have to explain this using pictures. First we have a list of artists and I provide the user a way to filter the artists by what state they are in. When the state is selected the grid is updated.
This demo like the others uses the cfartgallery data source that ships with CF so you should be able to run this example without any setup. The first thing we need to do is run our query and display our grid. This is pretty basic and you should understand what is going on here.
SELECT artistId, firstname, lastname, address, city, state, postalcode, email
FROM Artists
</cfquery>
<cfset args = structNew()>
<cfset args.name = "ArtistGrid">
<cfset args.format = "html">
<cfset args.query = "getArtists">
<cfset args.stripeRows = true>
<cfset args.selectColor = "##D9E8FB">
<cfset args.width = 600>
<cfform>
<cfgrid attributeCollection="#args#">
<cfgridcolumn name="artistid" display="false">
<cfgridcolumn name="firstname" header="First Name">
<cfgridcolumn name="lastname" header="Last Name">
<cfgridcolumn name="email" header="Email Address">
<cfgridcolumn name="address" header="Address">
<cfgridcolumn name="city" header="City">
<cfgridcolumn name="state" header="State">
<cfgridcolumn name="postalcode" header="Zip">
</cfgrid>
</cfform>
Now that our grid is setup we need to call a function post setup. We can do this using the ajaxOnLoad tag.
I am going to be using some toolbar and button code that is a part of the Ext framework. To make use of those classes we must import them.
<script type="text/javascript" src="/CFIDE/scripts/ajax/ext/package/button/button.js"></script>
Now that we setup our init method that will automatically get called after the page is setup. We need to get the grid component and we can use the built in method to do so. Next if you read my custom toolbar article you know the next couple of lines grabs the header panel and creates a toolbar on it.
function init(){
grid = ColdFusion.Grid.getGridObject("ArtistGrid");
var gridHead = grid.getView().getHeaderPanel(true);
var tbar = new Ext.Toolbar(gridHead);
}
</script>
Now we need to build our combo box that will be used as a filter. If you are not familiar with Ext you may want to look at the docs. A Combo box can loaded using json but for this example to keep things simple I am just going to load the box inline. The empty text attribute the text to display in the box while now selection is made.
id:"stateFilter",
emptyText:"Filter By State",
mode:"local",
triggerAction:"all",
displayField:"text",
valueField:"value",
store:new Ext.data.SimpleStore({
fields: ["value", "text"],
data: [
["CA","California"],
["CO","Colorado"],
["FL","Florida"],
["GA","Georgia"],
["NY","New York"],
["SD","South Dakota"]
]
})
});
Now that we have created our combo box we need to add an event listener. What we are saying is when a user selects something do the following. Using the grid reference we created at the top of the function we can grab the grids store (datasource) and use a built in method filter. The filter method takes two arguments, the column you want to filter on and the value you want to to filter.
var state = record.data.value;
//filter the records grid.getDataSource().filter("STATE",state);
});
The filter is now setup, now we just need to add it to our toolbar. The first line ads a spacer in so that our toolbar items are right aligned. Next I add a button that can be used to remove the filter. After a state is selected this will give us a way to return back to the grids original state. We will look at the event handler for this button in a second. Then next we create a separator between our button and combo box and finally add our combo box.
tbar.addButton({
text:"Remove Filter",
icon:"plugin.png",
cls:"x-btn-text-icon",
tooltip:"Remove Filter",
handler:removeFilter
});
tbar.add(new Ext.Toolbar.Separator());
tbar.add(cb);
Finally here is how the grids original state is restored. The store has a method for removing the filter and I simply reset the combo box the empty text is restored.
store = grid.getDataSource()
//remove the data filter store.clearFilter();
//clear the value of the combo box cb.clearValue();
//reset it so empty text shows up cb.reset();
}
Hopefully this tutorial will show you that there is a great deal of customizing that can go into the grids. Hopefully this will inspire you to create your own filters. If you have any comments or questions just let me know. Plus I am looking for more ideas on extending the grid. If you have something you are trying to accomplish but not sure how just let me know. Here is the full code for the tutorial.
<head>
<title>Grid Filter Exmaple</title>
<script type="text/javascript" src="/CFIDE/scripts/ajax/ext/package/toolbar/toolbar.js"></script>
<script type="text/javascript" src="/CFIDE/scripts/ajax/ext/package/button/button.js"></script>
<script type="text/javascript">
function init(){
grid = ColdFusion.Grid.getGridObject("ArtistGrid");
var gridHead = grid.getView().getHeaderPanel(true);
var tbar = new Ext.Toolbar(gridHead);
cb = new Ext.form.ComboBox({
id:"stateFilter",
emptyText:"Filter By State",
mode:"local",
triggerAction:"all",
displayField:"text",
valueField:"value",
store:new Ext.data.SimpleStore({
fields: ["value", "text"],
data: [
["CA","California"],
["CO","Colorado"],
["FL","Florida"],
["GA","Georgia"],
["NY","New York"],
["SD","South Dakota"]
]
})
});
cb.addListener("select",function(combo,record,index){
var state = record.data.value;
//filter the records grid.getDataSource().filter("STATE",state);
});
Ext.fly(tbar.addSpacer().getEl().parentNode).setStyle('width', '100%');
tbar.addButton({
text:"Remove Filter",
icon:"plugin.png",
cls:"x-btn-text-icon",
tooltip:"Remove Filter",
handler:removeFilter
});
tbar.add(new Ext.Toolbar.Separator());
tbar.add(cb);
console.log(Ext);
}
function removeFilter(){
store = grid.getDataSource()
//remove the data filter store.clearFilter();
//clear the value of the combo box cb.clearValue();
//reset it so empty text shows up cb.reset();
}
</script>
</head>
<body>
<link href="/CFIDE/scripts/ajax/ext/resources/css/ytheme-aero.css" rel="stylesheet" type="text/css">
<cfquery name="getArtists" datasource="cfartgallery">
SELECT artistId, firstname, lastname, address, city, state, postalcode, email
FROM Artists
</cfquery>
<cfset args = structNew()>
<cfset args.name = "ArtistGrid">
<cfset args.format = "html">
<cfset args.query = "getArtists">
<cfset args.stripeRows = true>
<cfset args.selectColor = "##D9E8FB">
<cfset args.width = 600>
<cfform>
<cfgrid attributeCollection="#args#">
<cfgridcolumn name="artistid" display="false">
<cfgridcolumn name="firstname" header="First Name">
<cfgridcolumn name="lastname" header="Last Name">
<cfgridcolumn name="email" header="Email Address">
<cfgridcolumn name="address" header="Address">
<cfgridcolumn name="city" header="City">
<cfgridcolumn name="state" header="State">
<cfgridcolumn name="postalcode" header="Zip">
</cfgrid>
</cfform>
<cfset ajaxOnLoad("init")>
</body>
</html>
