Home > ColdFusion, Web Application Development > My Adventure With CFGrid

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:

cfgrid take 1So 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.

cfgrid take 2

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.

Advertisements
  1. Debbie
    • kwbarrett
      January 21, 2010 at 4:14 pm

      The sort may not working properly because if you did like I did in my post, you added a column to the query that does not exist in the actual table. Each time you sort, it executes the query in the background and attempts to sort by the field you select. Only problem is that field does not exist in the table. The solution to this is to add logic to your original query that says if ARGUMETNS.gridsortcolumn = name of your new field ORDER BY name of correct colum.

      As far as the text wrapping issue, I’m not sure how to solve that one. It’s been a while but I seem to recall an attribute to make text wrap. There is an autowidth attribute for cfgridcolumn that will make the grid fit into the allotted space.

      Hope this helps.

  2. Lonnie Broadnax
    October 5, 2010 at 5:41 pm

    Greetings Kenneth, I’m relatively new to CF, having only been working in it for about a year now. In reviewing the above code I’m still a little unsure of how you’ve implemented this solution.

    My task is simpler. I have a cfgrid with a column containing shipment tracking numbers. What I need to do is make each of these tracking number values into a link, and when one is clicked, call a JavaScript function which displays a confirmation message, then redirects to the shipment carrier’s tracking site. The JavaScript function passes the tracking number to the carrier’s site and displays the shipping tracking information in a separate cfwindow.

    I having trouble figuring out how to set up a link that calls a HavaScript function using the cfgridcolumn’s href attribute.

    I would greatly appreciate any input you could provide. And I agree with you concerning Ray Camden; he is indeed a “ColdFusion Jedi”. He’s assisted me as well.

    Thanks in advance Kenneth!

    Lonnie Broadnax
    Inquiry from a new CF Web Developer, devoted Dad, Apostolic, Vocalist, Musician, African American man also in suburban Maryland

    • kwbarrett
      October 7, 2010 at 10:07 am

      Thanks for checking me out. I think the key for you is in how you call the cfwindow. You’ll construct your link as normal, but you call a cfwindow using the Coldfusion.window.show() call. You’ll first need to do a ColdFusion.window.create() perhaps in a body onload(). If you construct your link with JavaScript, you won’t need to use the href attribute at all.

      Here’s how I would do it:
      -First execute your query.
      -Then use queryAddColumn() to add a new column to the query recordset.
      -Use cfsavecontent to create your link including any javascript
      -Use querysetcell() to add the new value created above to your new column also added above.
      -Generate your grid and use the newly created column as one of your cfgridcolumns

      Make sure you do a cfajaximport at the beginning so that cfwindow is available to your page. Hope this helps.

  1. June 17, 2010 at 9:02 am
  2. January 2, 2011 at 2:43 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: