One of the coolest things about the ColdFusion 8 implementation of the CFGrid tag is that you can do a lot of customization, if you know your way around the Ext objects. I have found several blog entries about using custom renderers with the CFGrid tag. However, could not find a working example of one for date fields, so I decided to build one.
This example uses the coupons table in the cfbookclub database that comes with ColdFusion. There are two files in this example, coupons.cfc contains the functions needed for the editable CFGrid, and CouponForm.cfm contains the CFGrid Code and the JavaScript needed to do the date rendering. You will notice I had to specifically import the /CFIDE/scripts/ajax/ext/package/date.js file because it is not automatically imported with the cfgrid functionality. If you don't include that script tag you will get the JavaScript error "v.dateFormat is not a function".
CouponForm.cfm
<html>
<head>
<title>Custom Date Renderer</title>
<!--- import the Ext date package --->
<script src="/CFIDE/scripts/ajax/ext/package/date.js" type="text/javascript"></script>
<!--- create javascript function for rendering dates --->
<script language="JavaScript" type="text/javascript">
setDateRenderer = function(){
mygrid = ColdFusion.Grid.getGridObject('CouponsGrid');
cm = mygrid.getColumnModel();
cm.setRenderer(3, Ext.util.Format.dateRenderer('m/d/Y'));
mygrid.reconfigure(mygrid.getDataSource(),cm);
}
</script>
</head>
<body>
<!--- Set up the Grid --->
<cfform id="CouponForm" name="CouponForm">
<cfgrid name="CouponsGrid"
format="html"
pagesize="10"
striperows="yes"
selectmode="edit"
bind="cfc:coupons.getCoupons({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection})"
onchange="cfc:coupons.editCoupon({cfgridaction},{cfgridrow},{cfgridchanged})">
<cfgridcolumn name="Couponid" display="false" />
<cfgridcolumn name="SPONSORID" header="Sponsor" width="100"/>
<cfgridcolumn name="COUPON" header="Coupon" width="100"/>
<cfgridcolumn name="EXPIRATIONDATE" header="Exp Date" width="200"/>
</cfgrid>
</cfform>
<!--- use AjaxOnLoad to set the date renderer --->
<cfset ajaxOnLoad("setDateRenderer")>
</body>
</html>
</html>
coupons.cfc
<cfset THIS.dsn="cfbookclub">
<!--- Get art by media type --->
<cffunction name="getCoupons" 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="">
<!--- Local variables --->
<cfset var data="">
<!--- Get data --->
<cfquery name="data" datasource="#THIS.dsn#">
SELECT COUPONID, SPONSORID, COUPON, EXPIRATIONDATE
FROM COUPONS
</cfquery>
<cfset qryFinalQuery = QueryNew("COUPONID, SPONSORID, COUPON, EXPIRATIONDATE","varchar,varchar,varchar,varchar") />
<cfloop query="data">
<cfset QueryAddRow(qryFinalQuery) />
<cfset QuerySetCell(qryFinalQuery, "COUPONID", "#data.COUPONID#") />
<cfset QuerySetCell(qryFinalQuery, "SPONSORID", "#data.SPONSORID#") />
<cfset QuerySetCell(qryFinalQuery, "COUPON", "#data.COUPON#") />
<cfset QuerySetCell(qryFinalQuery, "EXPIRATIONDATE", "#DateFormat(data.EXPIRATIONDATE, 'mm/dd/yyyy')#") />
</cfloop>
<!--- And return it as a grid structure --->
<cfreturn QueryConvertForGrid(qryFinalQuery,
ARGUMENTS.page,
ARGUMENTS.pageSize)>
</cffunction>
<!--- Edit an coupon --->
<cffunction name="editCoupon" 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 COUPONS
SET <cfswitch expression="#colname#">
<cfcase value="EXPIRATIONDATE">#colname# = #createodbcdatetime(value)#</cfcase>
<cfdefaultcase>#colname# = '#value#'</cfdefaultcase>
</cfswitch>
WHERE COUPONID = #ARGUMENTS.gridrow.COUPONID#
</cfquery>
</cfcase>
<!--- Process deletes --->
<!--- <cfcase value="D">
<!--- Perform actual delete --->
<cfquery datasource="#THIS.dsn#">
DELETE FROM COUPONS
WHERE contactid = #ARGUMENTS.gridrow.COUPONID#
</cfquery>
</cfcase> --->
</cfswitch>
</cffunction>
</cfcomponent>
Click Here to See a Working Example
You will also notice in the getCoupons function in coupons.cfc, that there is a bit of a work around going on there. The issue is that the QueryConvertForGrid function does not mesh well with date fields, it automatically puts them into an uncommon date format that most people wouldn't want to use. I found a post on Gary Gilbert's blog that shows how to resolve this problem by rebuilding the query and forcing the date field to be a varchar field with the formatting you want.
http://extjs.com/deploy/ext/docs/output/Ext.util.F...
Indeed, I tried that first but I couldn't figure out how to get it to work. I had my setDateRenderer function set up like:
setDateRenderer = function(){
mygrid = ColdFusion.Grid.getGridObject('CouponsGrid');
cm = mygrid.getColumnModel();
cm.setRenderer(3, Ext.util.Format.dateRenderer('m/d/Y'));
mygrid.reconfigure(mygrid.getDataSource(),cm);
}
But, I kept getting the error "v.dateFormat is not a function".
That's when I decided to build my own function for formatting the date. At first I built it using functions in another dateFormat.js file I have used in the past, but then I realized that the CFIDE folder probably had the EXT date.js file somewhere, so I found it and used that instead.
After your comment I looked at it further, and It turns out that all I really needed to do was add that script tag importing the date.js file, that is why I was geting the "dateFormat is not a function" error.
<script language="JavaScript" type="text/javascript">
formatDates = function(data,cellmd,record,row,col,store){
if(!data){
return "";
}
if(!(data instanceof Date)){
data = new Date(Date.parse(data));
}
return data.dateFormat("m/d/Y");
}
setDateRenderer = function(){
mygrid = ColdFusion.Grid.getGridObject('CouponsGrid');
cm = mygrid.getColumnModel();
cm.setRenderer(3, formatDates);
mygrid.reconfigure(mygrid.getDataSource(),cm);
}
</script>
<cfajaximport tags="cfinput-datefield">
to import the date.js file instead of:
<script src="/CFIDE/scripts/ajax/ext/package/date.js" type="text/javascript"></script>
but it works either way.
One thing I noticed using an editable grid is that the renderer doesn't seem to affect the format once I click on a cell in the grid to edit it - so that when editing it shows a data as July, 10 2007 00:00:00 but when not editing it shows it as 2007-07-10. Any idea how to change the format for that as well so that I can send the data back as it is shown when not editing? Thanks.
If you read the last paragraph of myblog entry above you will see that I touched on the subject you are referring to and I referenced that same entry from Gary Gilbert's blog.
<cfset QuerySetCell(qryFinalQuery, "EXPIRATIONDATE", "#DateFormat(data.EXPIRATIONDATE, 'mm/dd/yyyy')#") />
would be like:
<cfset QuerySetCell(qryFinalQuery, "EXPIRATIONDATE", "#DateFormat(data.EXPIRATIONDATE,'mm-dd-yyyy')# #TimeFormat(data.EXPIRATIONDATE)#") />
then in CouponForm.cfm you would change the date format strings in the setDateRenderer function and the dataValidator function to use the format 'm/d/Y h:i A' instead of 'm/d/Y'.
For a complete list of format characters read this page:
http://extjs.com/deploy/ext/docs/output/Date.html
<cfif trim(data.EXPIRATIONDATE) neq "">
<cfset QuerySetCell(qryFinalQuery, "EXPIRATIONDATE", "#DateFormat(data.EXPIRATIONDATE,'mm-dd-yyyy')# #TimeFormat(data.EXPIRATIONDATE)#") />
<cfelse>
<cfset QuerySetCell(qryFinalQuery, "EXPIRATIONDATE", "") />
</cfif>
Here's another post of yours that shows the same behavior..
http://www.coldfusionguy.com/ColdFusion/blog/index...
There is an attribute called "select" in the cfgridcolumn tag, the default is "Yes". If you set it to "No", the field cannot be selected or edited. So I updated the sponsorid and coupon columns to be like this:
<cfgridcolumn name="SPONSORID" header="Sponsor" width="100" select="No"/>
<cfgridcolumn name="COUPON" header="Coupon" width="100" select="No"/>
It doesn't sort the date column properly. It sorts by the first character of the date value like the following.
01/20/2008
02/20/2007
03//20/2007
I tried to cast from cfquery in my function and custom render
by using Ext.util.Format.dateRenderer but no luck. Is there something that I am totally missing here?
Thanks in advance for anyone who figured this out.
I found a way that can bypass the date formatting by queryConvertForGrid(), and also can use Ext.util.Format.dateRenderer()
in MS-SQL:
SELECT convert(varchar,NM.createDate, 1) + ' ' + convert(varchar,NM.createDate, 108) AS Date
FROM XXX
Since this is the format that Ext1.0 date object takes, (see: http://extjs.com/deploy/ext-1.0.1/docs/output/Date... ), we can use Ext.util.Format.dateRenderer() without any additional JS!!!
Below is a more complete example:
<cfsavecontent variable="head">
<script src="/CFIDE/scripts/ajax/ext/package/date.js" type="text/javascript"></script>
<script type="text/javascript">
applyCustomRenderer = function() {
grid = ColdFusion.Grid.getGridObject('newMeterGrid');
cm = grid.getColumnModel();
cm.setRenderer(0, Ext.util.Format.dateRenderer('Y-m-d'));
// 0 is the first cfgridcolumn
grid.reconfigure(grid.getDataSource(), cm);
};
</script>
</cfsavecontent>
<cfhtmlhead text="#head#">
<cfset ajaxOnLoad("applyCustomRenderer")>