CFGrid Problem Solved!

In my previous post My Adventure with CFGrid, I wrote about troubles I was having meeting some minor last minute client requirements.  In short, they want a cool table with Ajax features like paging, but they also want links in the table in an Actions column that are not Ajax and take the user to a detail page.  Sounds like an odd request for Ajax functionality but what the client wants the client gets.

My initial problem was how to add the links to the Actions column.  This was solved with the help of Ray Camden who suggested using the querySetCell() method to add my link data to the query recordset after the query is executed.  This worked well enough and my manager was impressed.  Something I didn’t pay attention to initially was that the pagesize attribute of cfgrid is ignored when the query attribute is specified.  The client definitely wants paging for larger recordsets, so the solution turned out to be using the bind parameter rather than the query attribute.

Having never used databinding before, I jumped right in and quickly found all sorts of little things that normally work, suddenly didn’t.  None of my APPLICATION or SESSION variables seemed to be available.  Variable not defined was what the error read.  By the way, I highly recommend Firebug for debugging Ajax requests.  The solution was to either hardcode my variables or add hidden form elements and then pass them in my bind parameter to the CFC.  I ended up using both to finally get my awesome looking cfgrid that pages through 10 records at a time and has an Actions column which takes the user to a detail page.

While at CFUnited this I decided to ask around about why my SESSION and APPLICATION variables were coming up undefined when called via the bind parameter.  It was explained to me that bind is accessing the CFC directly rather than as a normal ColdFusion request and that is why they were not available.  This sounds reasonable to me, but I would like to get some confirmation.  So I pose the question to all my fellow CFers out there.  Is this really the case with databinding?

My Adventure With CFGrid

I’m using a <cfgrid> to display some data returned from a query.  Not exactly rocket science, but I’ve managed to run into a bit of difficulty meeting a couple of final client requirements.  Let me set up the scene here.  First I have a form with several different search criteria, which are independent of each other.  Depending on which search criterion is selected, a different grid will display.  Each grid will has an Action column with links to another page.

First we call the CFC and add the “Actions” column to the returned query recordset:

<cfinvoke component=“#APPLICATION.ManageFieldOffice#” method=“retrieveProjectNames” returnvariable=“qRetrieveProjectNames” CountryID=“#CountryID#”/>

<cfset queryAddColumn(qRetrieveProjectNames, “viewlink”,“varchar”, arrayNew(1))/>

<cfloop query=“qRetrieveProjectNames”>

<cfsavecontent variable=“viewtext”>

<cfoutput>

<a href=“#APPLICATION.self##xfa.lnkViewFieldOffice#&cntFieldOffice=#cntFieldOffice#&vTab=1″>View</a> | <a href=“#APPLICATION.self##xfa.lnkViewCountry#&countryID=#CountryID#”>View Country Record</a>

</cfoutput>

</cfsavecontent>

<cfset querySetCell(qRetrieveProjectNames, “viewlink”,viewtext, currentRow)>

</cfloop>

Then we display the results in the grid:

<cfgrid format=“html” name=“countryResults” query=“qRetrieveProjectNames” pagesize=“25″ width=“700″ striperows=“true”>

<cfgridcolumn name=“cntFieldOffice” display=“false” headerbold=“true”>

<cfgridcolumn name=“strProjectName” header=“Project Name” width=“300″ headerbold=“true”>

<cfgridcolumn name=“Country” headerbold=“true”>

<cfgridcolumn name=“strProjectNumber” header=“Project Code” headerbold=“true”>

<cfgridcolumn name=“viewlink” header=“” width=“200″>

</cfgrid>

Thanks to Ray Camden for his code example that guided me in creating the links for the Action column.

Here’s my CFC method:

<cffunction name=“retrieveProjectNames” output=“false” access=“public” returntype=“query” hint=“I retrieve all project office names”>

<cfargument name=“CountryID” type=“numeric” required=“false”/>

<cfargument name=“CountryList” type=“string” required=“false”/>

<!— var scope variables —>

<cfset var qRetrieveProjectNames = “”/>

<cftry>

<cfquery name=“qRetrieveProjectNames” datasource=“#variables.dsn#”>

SELECT tblFOAdminInfo.cntFieldOffice, tblFOAdminInfo.strProjectName, tblFOAdminInfo.strProjectNumber, tblFOAdminInfo.CountryID, tlkpCountry.Country

FROM tblFOAdminInfo

JOIN tblFOFieldOffices ON tblFOFieldOffices.cntFieldOffice = tblFOAdminInfo.cntFieldOffice

JOIN tlkpCountry ON tlkpCountry.CountryID = tblFOAdminInfo.CountryID

WHERE

tblFOFieldOffices.ysnDisable = <cfqueryparam value=“0″ cfsqltype=“cf_sql_bit”>

<cfif IsDefined(“ARGUMENTS.CountryID”) AND ARGUMENTS.CountryID GT 0>

AND tblFOAdminInfo.CountryID = <cfqueryparam value=“#ARGUMENTS.CountryID#” cfsqltype=“cf_sql_integer”>

<cfelseif IsDefined(“ARGUMENTS.CountryList”)AND ARGUMENTS.CountryList NEQ “”>

AND tblFOAdminInfo.CountryID IN (#ARGUMENTS.CountryList#)

</cfif>

ORDER BY tblFOAdminInfo.strProjectName

</cfquery>

<cfreturn qRetrieveProjectNames />

<cfcatch type=“ANY”>

<cfreturn false>

</cfcatch>

</cftry>

</cffunction>

So here’s what the grid looks like:

So this works just fine, however there’s one small problem.  The client wants to page through large  recordsets.  Not an extraordinary request by any means, but I’ve run into a problem.  The <cfgrid> ignores the pagesize attribute when a query attribute is specified.  To get around this, I could just use a bind parameter in <cfgrid>, which I did.  This cuts down significantly on the code since I no longer need to cfinvoke my CFC method.  I call it right from the <cfgrid> tag.

My grid code now looks like this:

<cfgrid name=“regionResult2″ format=“html” pagesize=“25″ preservepageonsort=“true” width=“700″ bind=“cfc:fieldoffice.field_v201_beta.cfapps.components.ManageCountry.getCountryByRegion({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection}, getRegionSearchParam())”>

<cfgridcolumn name=“Country” header=“Countries” width=“550″ headerbold=“true” />

<cfgridcolumn name=“viewlink” header=“Action” width=“200″>

</cfgrid>

I had to modify my CFC method as follows to supply the grid with the data:

<cffunction name=“getCountryByRegion” output=“false” access=“remote” returntype=“struct” hint=“I get Countries for a given strRegion”>

<cfargument name=“page” required=“true”/>

<cfargument name=“pagesize” required=“true”/>

<cfargument name=“gridsortcolumn” required=“true”/>

<cfargument name=“gridsortdirection” required=“true”/>

<cfargument name=“strRegion” type=“string” required=“true”/>

<cfset var qRetrieveCountryByRegion = “”/>

<cfquery name=“qRetrieveCountryByRegion” datasource=“FieldOffices”>

SELECT tlkpCountry.Country, tlkpCountry.CountryID

FROM tblFOCountry

INNER JOIN tlkpCountry ON tblFOCountry.CountryID = tlkpCountry.CountryID

WHERE tblFOCountry.strRegion = <cfqueryparam value=“#ARGUMENTS.strRegion#” cfsqltype=“cf_sql_varchar”>

ORDER BY tlkpCountry.Country

</cfquery>

<cfreturn queryconvertforgrid(qRetrieveCountryByRegion,page,pagesize) />

</cffunction>

A few things I learned in modifying my CFC method for this grid:

1. I had to change the returntype from query to structure as Firebug showed a returntype is not of type query.
2. Had to hardcode my DSN.  When I checked Firebug, I noticed the response was “Datasource ” not found” so my dynamic value was not working for some reason.
3. Had to add the 4 standard cfgrid arguments

Since I eliminated my cfinvoke to call the method, I figured I’d just move my code to add the Actions link to the query recordset.  After the query is executed, I add the code and the use queryconvertforgrid() to return the whole thing.  Now my grid appears and paging is available, but there is no Actions column.

I have to be missing something.  I cannot believe that I can’t add a link to a grid created using the bind parameter of the <cfgrid> tag.  Good thing I’m heading to CFUnited tomorrow.  Perhaps I can find some help there.

Great day at CFUnited ’08

Great information to be had today at CFUnited ’08 Day #2.  Two sessions that stood out for me were Peter Bell’s Practical Code Generation and Marc Esher’s Automating the Build/Deploy Process with ANT.

Practical Code Generation

I’ve worked with scaffolders such as Model Glue and Fusebox 5.5 with mixed results and opinions.  Primarily scaffolding for me has been used to create a quick prototype.  It’s great for routine tasks like creating a DAO with just a bit of information about the DB.  However, as Peter pointed out, most of the generated code is highly modified and most likely tossed by the time we deploy.  Additionally each change requires changes to the generated code.  I really liked Peter’s concept that code should be able to be generated easily as the application changes.

After going through a bit of theory on code generation, Peter walked through his CF Template application which is an open source code generation CFC which uses metadata and templates to generate all the code.  What’s really cool about it is that it uses ColdFusion to generate ColdFusion.  Even cooler is that in theory, it could be used to generate code in nearly any language if that’s what floats your boat.

I walked away with some good ideas about how to try to use code generation and CF Template in my daily work.  We use Fusebox 4.1 in our daily work, with much of our older code not in CFCs.  I’m thinking that some of the older apps I work on written strictly using Fusebox should have files simple enough to use as a starting point to experimenting with code generation using CF Template.  I did a little surfing to find tutorials and documentation and came upon Brian Caufield’s wiki where he has created templates for generating many routine files we might use regularly.  He even provides a demo based on the CFArtGallery database which comes with ColdFusion.  I’m really looking forward to trying to make practical use of CF Template.

Automating the Deployment/Build Process with ANT

I was eager to attend this session even though it was the last session of a long day.  At work, I’ve been playing with ANT to do simple copy tasks, but have been reading about the myriad of tasks available.  Since we work in Fusebox 4.1, each new application requires a copy of the core Fusebox files.  I have been able to create a basic ANT script which copies the files to the set directory for use in a new project.  Ultimately, I would like to use ANT to do daily checkouts via ftp at the start of the day from the development server and a checkin  at the end of the day back to the development server.

Marc must have known I was coming because he essentially covered all the stuff on my wishlist.  It actually looks pretty simple to automate a lot of the routine tasks using ANT.  You can bet I’ll be working to integrate this into my daily work in the very near future.

The day would have been perfect with lots of new information and tips that could prove useful in my daily work.  However, at the end of the day, I had to put out a fire at work which was made that much worse by the fact that all the developers are attending CFUnited this week.  Oops.

I’m taking the plunge into CFUnit

I have gotten to the point now where I’m fairly comfortable with Eclipse and CFEclipse, having used both for the past year. I don’t even have Dreamweaver installed on my new work laptop. Since I was in software QA before becoming a developer I am aware of the importance of unit testing, but have not really practiced it. After last year’s CFUnited, I was introduced to CFUnit, but never really got into it. So here I am ready to dive in to CFUnit. Over the next few days, weeks, etc., I will be documenting my experience with this seemingly useful tool. With the help of the most awesome ColdFusion user community, I have managed to locate a long list of references and tutorials. My adventure begins this evening as I try to set up unit tests for the latest application I’m working on in the office. The CFCs are already in place, so all I should need to do is set up my tests. Wish me luck.