Stillnet Studios

A while back I had the need to do some formatting on a cfgrid. I was using a typical product/price table but the prices are stored with 4 decimals and we needed to display that much resolution. The html version of CFGRID does not directly support this.

Consider this “fake” query set:

<cfset materials=QueryNew("ProductCode,Description,Price","VarChar, VarChar, decimal")>  
<cfset Temp=QueryAddRow(materials,1)>
<cfset Temp=QuerySetCell(materials,"ProductCode","MAT1")>  
<cfset Temp=QuerySetCell(materials,"Description","Copper Bar")>  
<cfset Temp=QuerySetCell(materials,"Price",3.3400)>  
<cfset Temp=QueryAddRow(materials)>
<cfset Temp=QuerySetCell(materials,"ProductCode","MAT2")>  
<cfset Temp=QuerySetCell(materials,"Description","Feeler Gauge")>  
<cfset Temp=QuerySetCell(materials,"Price",2.2900)>  
<cfset Temp=QueryAddRow(materials)>
<cfset Temp=QuerySetCell(materials,"ProductCode","MAT3")>  
<cfset Temp=QuerySetCell(materials,"Description","Plastic Retainer")>  
<cfset Temp=QuerySetCell(materials,"Price",1.3201)>  

Note the prices have 4 decimal values, this is how they come to us from the database. If you try to display these with this cfgrid code:

<cfform>
<cfgrid name="testgrid" format="html" query="materials">
</cfgrid>
</cfform>

You will end up with this:


Notice the 3rd material is displayed correctly, but the others are not, we would like them to all have the same number of decimals for consistency. You might think you could use cfgridcolumn tags and the numberformat attribute like this:

<cfform>
<cfgrid name="testgrid" format="html" query="materials">
	<cfgridcolumn name="ProductCode">
	<cfgridcolumn name="Description">
	<cfgridcolumn name="Price" numberformat="0.0000">
</cfgrid>
</cfform>

But this will not work, the numberformat attribute only works on Java type cfgrids, not HTML ones. This attempt will silently fail with no error message, leaving you scratching your head for a while. Lucky for us, ColdFusion creates its grids using the populat EXT JavaScript framework. This means there is a lot of documentation and examples out there on how to work with this code in EXT. Its pretty easy to hook into the grid with JavaScript and format the data.

<script>var myFormatter = Ext.util.Format.numberRenderer('.0000');
mygrid = ColdFusion.Grid.getGridObject('testgrid');
cm = mygrid.getColumnModel();
cm.setRenderer(2, myFormatter);  // the 2 here indicates we want to format the 3rd column (JS is zero indexed)
mygrid.reconfigure(mygrid.getStore(),cm);
</script>

But you can’t run the JavaScript code when your page loads because the grid takes a few milliseconds to get setup, so if your code runs right away it won’t find the grid. You could use the JavaScript setTimeout() to delay execution of your code:

<script language="javaScript">
var myFormatter = Ext.util.Format.numberRenderer('.0000');
formatgrid = function() {
	mygrid = ColdFusion.Grid.getGridObject('testgrid');
	cm = mygrid.getColumnModel();
	cm.setRenderer(2, myFormatter);
	mygrid.reconfigure(mygrid.getStore(),cm);
	}
setTimeout("formatgrid()",500);
</script>

However you would be guessing at how long it will take and its going to vary on each client. I know ColdFusion has the built in ajaxOnLoad function that is supposed to run your code after things like grids have been created, but I have never gotten this to work. I know for sure it won’t work for content that is loaded secondarily after the base page has finished loading, like many of my pages are. But I can’t get it work even on a simple test file. So I wrote a little function to watch for the grid, and call the formatting code when the grid exists.

<script language="javaScript">
function lookForGrid() {
	// keeps checking the page to see if the grid exists yet. When it does, then call the formatter function
	if (ColdFusion.Grid.getGridObject('testgrid')) {
		formatgrid();
		}
	else {
		setTimeout(lookForGrid,10);
		}
	}

var myFormatter = Ext.util.Format.numberRenderer('.0000');
formatgrid = function() {
	mygrid = ColdFusion.Grid.getGridObject('testgrid');
	cm = mygrid.getColumnModel();
	cm.setRenderer(2, myFormatter);
	mygrid.reconfigure(mygrid.getStore(),cm);
	}
();
</script>

And this is the result:

The code could definitely be cleaned up a little, so the grid name isn’t duplicated at least, but its a good start. There are lots of other formatters available also, we’ve found the DateRenderer to be useful.