Forta.com - Blog

The data grid is critical to all sorts of development on all sorts of platforms and in all sorts of languages. ColdFusion has supported data grids since ColdFusion 2 - first a Java applet, then a Flash control, and in ColdFusion 8 we've added an HTML data grid that can be pre-populated with data, or which can be used to display live data loaded asynchronously.

The basic pre-populated data grid functions much like the <CFGRID> of old, pass it a query and it displays the data. Here is an example (which uses the example tables that come with ColdFusion):

<cfquery name="artists" datasource="cfartgallery">
SELECT artistid, lastname, firstname, email
FROM artists
ORDER BY lastname, firstname
</cfquery>

<cfform>
   <cfgrid name="artists"
         format="html"
         striperows="yes"
         query="artists">
      <cfgridcolumn name="lastname" header="Last Name" width="100"/>
      <cfgridcolumn name="firstname" header="First Name" width="100"/>
      <cfgridcolumn name="email" header="E-Mail" width="200"/>
   </cfgrid>
</cfform>

Here a <CFQUERY> retrieves data, and <CFGRID> displays the results. The grid is straight client side HTML with CSS and JavaScript. You have control over look and feel including size, columns, colors, and fonts. And users can sort up and down, resize columns, and more.

The new <CFGRID> also supports Ajax type interaction, where data is not pre-populated, but is asynchronously loaded as needed. Here is a sample data grid:

This data grid is displayed in a window created using the new <CFWINDOW> tag (for no good reason other than I like the look of it). The data grid itself has no passed query, instead, it is bound to artists.cfc. When the data grid needs data it fires an asynchronous call to the getArtists() method in artists.cfc, and passes it four pieces of information: the current page, the page size (specified previously in the pagesize attribute), and the column being sorted on and sort direction (if the user opts to sort data). <CFGRID> thus requests data, and simply displays whatever ColdFusion returns, automatically supporting paging (assuming there are enough rows to so warrant).

Now for the CFC:

<cfcomponent output="false">

   <cfset THIS.dsn="cfartgallery">

   
   <cffunction name="getArtists" 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="">

      
      <cfset var artists="">

      
      <cfquery name="artists" datasource="#THIS.dsn#">
      SELECT artistid, lastname, firstname, email
      FROM artists
      <cfif ARGUMENTS.gridsortcolumn NEQ ""
         and ARGUMENTS.gridsortdir NEQ "">
         ORDER BY #ARGUMENTS.gridsortcolumn# #ARGUMENTS.gridsortdir#
      </cfif>
      </cfquery>

      
      <cfreturn QueryConvertForGrid(artists,
                     ARGUMENTS.page,
                     ARGUMENTS.pageSize)>
   </cffunction>

</cfcomponent>

The getArtists returns a structure (containing data in the format required by the data grid), and accepts four arguments, the same four arguments passed in the client side bind attribute. The first two are always passed by the client, and so they are required. The latter two are only passed if the user clicks on a column header to sort the data, and so those arguments are not required and default to "". <CFQUERY> performs the actual data retrieval, conditionally sorting the data if sorting is required. And finally, the new QueryConvertForGrid() function extracts the desire data subset (using the passed page and pagesize values) and formats it as a structure which is returned to the data grid.

This is a basic example, and we'll look at additional functionality in future posts.

Comments

[Add Comment]

When you use the built-in QueryConvertForGrid in the CFC's getArtists method, does ColdFusion load the entire recordset into server memory before paginating based on the ARGUMENTS.page and ARGUMENTS.pageSize variables?

# Posted By Clint Miller | 5/31/07 6:23 PM

Clint, it uses whatever query you pass to it, and extracts a subset of the data.

--- Ben

# Posted By Ben Forta | 5/31/07 8:02 PM

Simply wonderful. This brings AJAX to the masses!

# Posted By William from Lagos | 6/1/07 1:31 PM

So far, I'm loving CF8...

Quick question.. In this example with the HTML CFGRID, is there a way to change the background color of a row dynamically?

For example, if I had a list of tasks, I'd like to change the background color if the task is urgent...

Thanks for the quick and easy CF8 tutorials, Ben... much appreciated!

# Posted By Andre Audet | 6/13/07 3:17 PM

Any idea why the source cannot be execute correctly under IE but can work on Firefox.

Thanks,
Oliver

# Posted By Oliver | 6/20/07 12:35 AM

Has this been fixed for IE yet? Gawd, I hate cross-browser incompatibilities.

# Posted By I love my free PS3 | 7/31/07 5:52 PM

Hi Ben,

This is good stuff. What I'm trying to figure out is how to add a link to the grid, so that when the user clicks on a record, it takes them to the appropriate page. I've been digging through the docs and google, but no luck so far.

# Posted By Charles Sheehan-Miles | 8/9/07 10:11 PM

My pagesize attribute doesnt work, any suggestions?

<cfquery name="R1" datasource="canadadb" maxrows="200">
SELECT *
FROM dbo.cities
</cfquery>
<cfform>
<cfgrid query="R1"
name="R1"
format="html"
pagesize="10"
striperows="yes">
<cfgridcolumn name="id" header="First Name"/>
<cfgridcolumn name="weather" header="weather"/>
<cfgridcolumn name="cities" header="cities"/>
<cfgridcolumn name="province" header="province"/>
</cfgrid>
</cfform>

# Posted By Keith Korry | 8/11/07 3:47 PM

Charles, you can add a link to the grid using the "href" attribute in the appropriate "cfgridcolumn" tag: just point that to where you want to drill down to. The "hrefkey" attribute will append a column value to the URL.

# Posted By Hal Helms | 8/15/07 9:56 AM

when i try ajax grids,it has some wrong information:
"Attribute validation error for tag CFGRID.
The tag does not allow the attribute(s) HEADER. The valid attribute(s) are "
What is the reason for this?

thanks

# Posted By hans | 8/15/07 4:10 PM

oh, i'm so sorry.
i found the wrong reasons for the last aid.
I have written 'cfgridcolumn ' misuse of the 'cfgrid'.
so slack~

BTW, i like your articles.

# Posted By hans | 8/15/07 4:18 PM

Awesome Grid Tutorial, I just started working with this myself and was wondering if there is a way to Autoformat the WIDTH of cfgridcolumn to the size of ethier the column header or column value.

Double clicking the Grid line formats the width based on value, I was wondering if you know of a dynamic way to do this?

Thanks,
Jason

# Posted By Jaseowns | 10/15/07 5:31 PM

Hi,

I've got a select that filters a grid. If I check items without changing the filter I can post the form and get the results. However, when I change the filter I don't get anything when I post.

How can I fix this?

========================================
<cfscript>
rs = queryNew("id,checked,fname,lname,playerposition,salary");
addRow(rs, '','Chester','Taylor','Running Backs','$3,500,000');
addRow(rs, 'true','Mewelde','Moore','Running Backs','$1,750,000');
addRow(rs, '','Tavaris','Jackson','Quarterbacks','$1,250,000');
addRow(rs, '','Bobby','Wade','Receivers','$2,250,000');
function addRow( qry, checked, fname, lname, playerposition, salary){
queryAddRow(rs);
querySetCell(rs,'checked',checked);
querySetCell(rs,'fname',fname);
querySetCell(rs,'lname',lname);
querySetCell(rs,'playerposition',playerposition);
querySetCell(rs,'salary',salary);
}
</cfscript>

<cfquery dbtype="query" name="getPos">
SELECT distinct playerposition
FROM rs
ORDER BY playerposition
</cfquery>
<cfquery dbtype="query" name="MemberList">
SELECT * from rs
ORDER BY playerposition
</cfquery>

<cfsavecontent variable="astest">
if(_global.arrMembers == undefined) _global.arrMembers = data.dataProvider.slice(0);

var arrMembers = _global.arrMembers;
var arrDisplay:Array = [];

for(var i = 0; i < arrMembers.length; i++)
{
if(arrMembers[i].playerposition == myselect1.value || myselect1.value == 'All')
{
arrDisplay.push(arrMembers[i]);
}
}
data.dataProvider = arrDisplay;
</cfsavecontent>

<cfform name="myForm" format="flash" width="400" height="300" action="#cgi.script_name#" method="playerpositiont">
<cfformgroup type="panel" label="Search">
<cfselect query="getPos" queryposition="below" label="Position" name="myselect1" value="playerposition" display="playerposition" width="200" onChange="#astest#">
<option value="All">All</option>
</cfselect>
<cfgrid name="data" query="MemberList" selectmode="edit" >
<cfgridcolumn header="Select" name="checked" type="boolean" headeralign="center" dataalign="center" />
<cfgridcolumn header="First Name" name="fname" />
<cfgridcolumn header="Last Name" name="lname" />
<cfgridcolumn header="salary" name="salary" />
<cfgridcolumn header="Position" name="playerposition" display="false" />
</cfgrid>
<cfinput type="submit" name="submitBtn" style="cornerRadius:1;" value="Submit"/>
</cfformgroup>
</cfform>

<p><cfdump var=#form#><P>

# Posted By Cheryl | 10/17/07 9:11 PM

I copied the code, and changed it for my datasource, and it is coming up with an error invoking the CFC, I have the CFC in the same directory as the page calling the bind attribute, where is the problem.

CFC:

<cfcomponent output="false">

<cfset THIS.dsn="dsPlanningApps">


<cffunction name="LookupApps" 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="">


<cfset var rsLookup="">


<cfquery name="rsLookup" datasource="#THIS.dsn#">
SELECT [File Nbr] AS FileNbr, Applicant, Planner
FROM dbo.PlanningZoningApplications
<cfif ARGUMENTS.gridsortcolumn NEQ ""
and ARGUMENTS.gridsortdir NEQ "">
ORDER BY #ARGUMENTS.gridsortcolumn# #ARGUMENTS.gridsortdir#
</cfif>
</cfquery>


<cfreturn QueryConvertForGrid(rsLookup,
ARGUMENTS.page,
ARGUMENTS.pageSize)>
</cffunction>

</cfcomponent>

HERE IS THE FORM WITH THE GRID:

<cfform>
<cfgrid name="artists"
format="html"
pagesize="10"
striperows="yes"
bind="cfc:member.PlanningLookup.LookupApps({cfgridpage},
{cfgridpagesize},
{cfgridsortcolumn},
{cfgridsortdirection})">
<cfgridcolumn name="FileNbr" header="File No." width="100"/>
<cfgridcolumn name="Applicant" header="Applicant" width="100"/>
<cfgridcolumn name="Planner" header="Planner" width="200"/>
</cfgrid>
</cfform>

# Posted By RG | 10/18/07 12:40 PM

How would I bind the CFC if it were in a different directory. This example works great when everything is in the same folder, but if I move the artists.cfc to a mapped folder, it fails.

Here's how I'm calling it: bind="cfc:CobeTemplates.manager.artists.getArtists(..."

I have a Mapping called "CobeTemplates" with a sub folder called manager. I have moved artists.cfc to that folder.

When I run it, I get a javascript error (Line 767, Error: Exception thrown and not caught) and the grid never displays.

I'm storing it in a mapped folder because I want to be able to call this CFC from several different applications. Any ideas?

Thanks,
Bruce

# Posted By Bruce Van Horn | 10/18/07 2:24 PM

Am I correct in saying that when you click to sort on a column it will go back to the .cfc, rerun the query, sorting it on the column? Wouldn't this be absolutely horrible if your initial query took say 20-30 seconds to complete?

If this is the case is there anyway to have it sort on the client?

Also is there anyway to get rid of pagination and just let it use a scrollbar? It see examples on extjs.com that seem to provide this.

# Posted By Scott C | 10/19/07 7:04 PM

Hi,

I have modified the code to populate the grid from a table and added a hard coded argument which is passed to the cfc and used in the modified select with a where clause to get selected data. All is working but my question is what would I add if I wanted to be able to be able to select the argument to use in the where clause from a drop list to repolulate the grid with this new data. I'm a bit stuck on how to do this so please help if you can.

Thanks,
Reg

# Posted By Reg | 10/24/07 2:28 PM

This is the cfm page
<cfparam name="user_id" default="1">
<cfparam name="month" default="1">
<cfparam name="year" default="2007">

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitiona...">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
<title>Untitled Document</title>
</head>

<body>
<cfwindow initshow="true" center="true"
width="480" height="340" title="Blog Entries" draggable="false" closable="false" resizable="false">

<cfform>
<cfgrid name="entrylinks"
format="html"
pagesize="10"
striperows="yes"
bind="cfc:getentrylinks.getEntryLinks({cfgridpage},
{cfgridpagesize},
{cfgridsortcolumn},
{cfgridsortdirection},'#user_id#','#month#','#year#')">
<cfgridcolumn name="Date_Created" header="Date_Created" width="150"/>
<cfgridcolumn name="Title" header="Title" width="200"/>
<cfgridcolumn name="Counter" header="Counter" width="100"/>
</cfgrid>

</cfform>

</cfwindow>
</body>
</html>

As you can see three arguments are passed. How can I put a drop list populated with months 1,2,3,4 etc in the cfwindow and have the selection used repolulate the grid with the new data obtained from the cfc.

Thanks,
Reg

# Posted By Reg | 10/24/07 2:54 PM

I do like the new cfgrid controls but I am pulling out my hair trying to work with them. Specifically, I want to use a grid within a tab. Selecting a row in the grid should open a new detail page within the tab. I can make the links work using the href and hrefkey commands but this always opens a new page. Am I doing something wrong?

# Posted By Michael Lato | 11/5/07 10:08 PM

Try using target="_top" like this
<cfgridcolumn name="Title" header="Title" width="220" target="_top" href="http://www.yourdomain.com/yourpage.cfm?id=#user_id... hrefKey="Counter"/>

# Posted By Reg | 11/6/07 2:59 AM

<cfgridcolumn name="Title" header="Title" width="220" target="_top" href="http://www.cascobayblog.com/blog.cfm?id=#user_id#&... hrefKey="Counter"/>
where Counter is a Database field. The previous comment shows #user_id... this should have the closing hash symbol and closing quote it's a mystery why it showed it as it did . Anyway hope this helps

# Posted By Reg | 11/6/07 3:07 AM

The new enhancement to CFGRID is classic ColdFusion beauty. Neatly package a lot of AJAX wiring in a few custom tags and attributes. It took me less than a couple hours late at night to implement an AJAX based data bound grid on SalaryMap.com at http://www.salarymap.com/salary-finder.cfm.

# Posted By Tom Barr | 11/8/07 5:23 PM

I have facing one problem with cfgird

Let i explaing i have two field client and memer and i bind grid with client and member and serch according this two field.

I am also using one more featured in this member is depend upon client. when i select any client at that time member drop -down filed automatic
here client and member both are drop-down field
My problem is when i change client name grid refresh two time. because when i chenged client at that time meber drop -down change and cfgrid bind on client and meber so it is referesh to time.
Can you please tell us how to stop cfgrid bind two time . cfgrid should be refresh when i click on search button not on bind.

# Posted By Print | 1/2/08 5:37 AM

I am using the cf8 html grid. Anyone know how to remove the "-" that appears on blank rows when pagination is allowed on the cfgrid? I have one column on the grid linking to a different URL. When the record exists (row is not blank) it works fine. Anyone come across this issue. Code below:
<cfgrid name = "ViewedReports" format="html" height="170" width="780" pageSize=5 sort=true selectcolor="##CCCCCC" selectmode="single" selectOnLoad="false"
bind="cfc:ViewReportsByInv.getViewedReportsByInv({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection})"
<cfgridcolumn name = "REP_REPORT_ID" display="no">
<cfgridcolumn width="110" name = "VIEWREPORT" header="Report" href="ViewReport.cfm?status=viewed" hrefkey="REP_REPORT_ID" target="_blank"> </cfgrid>
</cfform>

# Posted By Anusha | 1/14/08 12:50 PM

Hi Ben,

what if I need to return a selected columname from the CFGRID in the CFWINDOW in order to affect a selectIndex of a specific CFSELECT on the main (caller) page. I cannot find the way to transmit the value to the main page from the CFWINDOW. :-|

# Posted By Francois-Yanick Bourassa | 1/31/08 1:02 PM

Has anyone been able to get this to work:

<cfgridcolumn name="questionText" header="Question" width="345" href="javascript:TestFunction()" hrefkey="questionUUID" />

I can easily pass in a url but when i try to pass in a javascript funtion it does not work.

# Posted By Derrick | 2/21/08 2:05 PM

How can I pass URL variables into the BIND CFC to filter the SQL query there in the WHERE clause?

Ive tried to make the URL var a hidden form variable passed in the BIND attribute, with an ARGUMENT tag on the CFC, but it just sits there thinking eternally.

<CFFORM>
   <CFINPUT TYPE="HIDDEN" NAME="equitytype" VALUE="#url.equitytype#">
   <CFGRID NAME="GetMemberDataForDirectory"
      FORMAT="HTML"
PAGESIZE="#URL.SHOWROWS#"
STRIPEROWS="YES"
SELECTONLOAD="FALSE"
APPENDKEY="YES"
HREF="/index.cfm?fuseaction=members.emailmember"
HREFKEY="ENCRYPTARCODE"
WIDTH="100%"
   BIND="cfc:memberDirectory.getMembers(
               {cfgridpage},
{cfgridpagesize},
{cfgridsortcolumn},
{cfgridsortdirection},
{equitytype}
)">
<CFGRIDCOLUMN NAME="lastname" HEADER="Last Name" WIDTH="100"/>
<CFGRIDCOLUMN NAME="firstname" HEADER="First Name" WIDTH="100"/>
<CFGRIDCOLUMN NAME="City" HEADER="Home City" WIDTH="125">
<CFGRIDCOLUMN NAME="State" HEADER="State" WIDTH="50">
<CFGRIDCOLUMN NAME="equitytype" HEADER="Type" WIDTH="50">
<CFGRIDCOLUMN NAME="misc1" HEADER="misc1" WIDTH="50">
<CFGRIDCOLUMN NAME="emailme" HEADER="CONTACT">
<CFGRIDCOLUMN NAME="ENCRYPTARCODE" DISPLAY="NO" HEADER="ENCRYPTARCODE">
</CFGRID>
</CFFORM>

CFC:
<CFARGUMENT NAME="equitytype" TYPE="string" REQUIRED="no" DEFAULT="">


<CFSET var GetMemberDataForDirectory="">


<CFQUERY NAME="GetMemberDataForDirectory" DATASOURCE="ORCIntranet">
SELECT ARCODE,Lastname, Firstname, City, State, equitytype, MISC1, EMAIL, CASE WHEN EMAIL LIKE ('%@%') THEN 'EMAIL'+' '+Firstname ELSE 'NO EMAIL' END AS EMAILME, CONVERT(VARCHAR, (ARCODE * 37))+CONVERT(VARCHAR, (FAMNUM * 37)) AS ENCRYPTARCODE
FROM view_familyinfo_all_activemembers_withcontactinfo
    WHERE FAMNUM <= '2' AND MISC1 <> '' and MISC1 BETWEEN '100' and '599'
<CFIF arguments.equitytype EQ 'equity'>AND MISC1 IN ('110')</CFIF>
<CFIF ARGUMENTS.gridsortcolumn NEQ "" and ARGUMENTS.gridsortdir NEQ "">
ORDER BY #ARGUMENTS.gridsortcolumn# #ARGUMENTS.gridsortdir#
</cfif>
</CFQUERY>

# Posted By Jonathan | 3/6/08 2:16 PM

I am having problems getting the CFGRID to display any data at all when used with components and QueryConvertForGrid

If I copy the examples from this and other sites and create the CFM and CFC pages using the databases built into ColdFusion I do not get any data at all displayed in the grid. The grid is there with the headers and grayed out page controls, and no CF errors are thrown, but there is no data either.

This is the case no matter if I use IE or Firefox to display the CFM page with the CFGRID in it.

I'm using the latest version of CF with the built in web server.

Any ideas?

# Posted By Dan K | 3/25/08 9:40 PM

I was able to begin passing URL variables into the grid (with help from others!) using the following code (this answers my posting above):
here's the final code for posterity and for others.
they keys i was missing were
1. the <CFINPUT tag type=hidden> that defined the form variable,
2. the BIND attribute

FORM PAGE:
<CFFORM>
   <CFINPUT NAME="equitytype" TYPE="HIDDEN" VALUE="#url.equitytype#">
   <CFINPUT NAME="state" TYPE="HIDDEN" VALUE="#url.state#">
   <CFINPUT NAME="initial" TYPE="HIDDEN" VALUE="#url.initial#">
   <CFGRID NAME="GetMemberDataForDirectory"
      FORMAT="HTML"
PAGESIZE="#URL.SHOWROWS#"
STRIPEROWS="YES"
SELECTONLOAD="FALSE"
APPENDKEY="YES"
HREF="/index.cfm?fuseaction=members.emailmember"
HREFKEY="ENCRYPTARCODE"
WIDTH="100%"
   BIND="cfc:memberDirectory.getMembers(
               {cfgridpage},
{cfgridpagesize},
{cfgridsortcolumn},
{cfgridsortdirection},
'#URL.equitytype#',
'#URL.state#',
'#URL.initial#'
)">
<CFGRIDCOLUMN NAME="lastname" HEADER="Last Name" WIDTH="100"/>
<CFGRIDCOLUMN NAME="firstname" HEADER="First Name" WIDTH="100"/>
<CFGRIDCOLUMN NAME="City" HEADER="Home City" WIDTH="125">
<CFGRIDCOLUMN NAME="State" HEADER="State" WIDTH="50">
<CFGRIDCOLUMN NAME="equitytype" HEADER="Type" WIDTH="50">

<CFGRIDCOLUMN NAME="emailme" HEADER="CONTACT">
<CFGRIDCOLUMN NAME="ENCRYPTARCODE" DISPLAY="NO" HEADER="ENCRYPTARCODE">
</CFGRID>
</CFFORM>

CFC:
<CFCOMPONENT OUTPUT="false">

<CFFUNCTION NAME="getMembers" 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="">
<CFARGUMENT NAME="equitytype" TYPE="string" REQUIRED="no" DEFAULT="">
<CFARGUMENT NAME="state" TYPE="string" REQUIRED="no" DEFAULT="">
<CFARGUMENT NAME="initial" TYPE="string" REQUIRED="no" DEFAULT="">


<CFSET var GetMemberDataForDirectory="">


<CFQUERY NAME="GetMemberDataForDirectory" DATASOURCE="ORCIntranet">
SELECT ARCODE,Lastname, Firstname, City, State, equitytype, MISC1, EMAIL, CASE WHEN EMAIL LIKE ('%@%') THEN 'EMAIL'+' '+Firstname ELSE 'NO EMAIL' END AS EMAILME, CONVERT(VARCHAR, (ARCODE * 37))+CONVERT(VARCHAR, (FAMNUM * 37)) AS ENCRYPTARCODE
FROM view_familyinfo_all_activemembers_withcontactinfo
    WHERE FAMNUM <= '2' AND MISC1 <> '' and MISC1 BETWEEN '100' and '599'
<CFIF arguments.equitytype EQ 'equity'>AND MISC1 IN ('400','500')</CFIF>
<CFIF arguments.equitytype EQ 'social'>AND MISC1 IN ('100','109','110','150','159','160','300','360')</CFIF>
<CFIF arguments.equitytype EQ 'legacy'>AND MISC1 IN ('130','420','430','520','530')</CFIF>
<CFIF arguments.initial NEQ ''>AND LEFT(Lastname,1) = '#arguments.initial#'</CFIF>
<CFIF arguments.state NEQ ''>AND STATE = '#arguments.state#'</CFIF>
<CFIF ARGUMENTS.gridsortcolumn NEQ "" and ARGUMENTS.gridsortdir NEQ "">
ORDER BY #ARGUMENTS.gridsortcolumn# #ARGUMENTS.gridsortdir#
<CFELSE>
ORDER BY Lastname,ARCODE,FAMNUM
</cfif>
</CFQUERY>


<CFRETURN QueryConvertForGrid(GetMemberDataForDirectory,
ARGUMENTS.page,
ARGUMENTS.pageSize)>
</CFFUNCTION>

</CFCOMPONENT>

# Posted By Jonathan | 3/26/08 9:18 AM

My grid looks like this. It's in a Mach-II app. Everything displays beautifully. However, as you see, I'm trying to pass each record's Record_Id in the href parameter of the first column. However, I get a Mach-II exception: "Variable RECORD_ID is undefined." Can anyone see what i've done wrong?

<cfform>
<cfgrid name="cars"
format="html"
pagesize="10"
striperows="yes"
bind="cfc:cars4grid.viewAllCars({cfgridpage},
{cfgridpagesize},
{cfgridsortcolumn},
{cfgridsortdirection})">
<cfgridcolumn name="make" header="Make" width="75" target="_top" href="index.cfm?event=editCar&id=#Record_Id#" hrefKey="Counter" />
<cfgridcolumn name="Record_Id" header="ID" width="20"/>
<cfgridcolumn name="model" header="Model" width="120"/>
<cfgridcolumn name="year" header="Year" width="60"/>
<cfgridcolumn name="ccRating" header="CCs" width="60"/>
<cfgridcolumn name="enginetype" header="Engine" width="60"/>
<cfgridcolumn name="transmission" header="Transmission" width="120"/>
</cfgrid>
</cfform>

# Posted By Paul | 4/18/08 6:24 AM

don't worry...got it sorted.

# Posted By Paul | 4/18/08 9:57 PM

How can you condition a cfgridcolumn based on another column in the query (without getting into extensions)?

<cfgrid format="html" name="grid_Companies" bind= "url:dspSearchResults_2.cfm?page={cfgridpage}&pageSize={cfgridpagesize}&sortCol={cfgridsortcolumn}&sortDir={cfgridsortdirection}&name2={name2@keyup}&name3={name3@keyup}" bindonload="yes" width="780" selectonload="no" pagesize="40" selectmode="row" autowidth="yes" colheaderalign="center" colheaders="yes" griddataalign="left" >
<cfgridcolumn name="ID" display="Yes" dataalign="left" width="12" href="index.cfm?fa=viewComp"/>
<cfgridcolumn name="DateViewed" display="no" />
<cfgridcolumn name="WhoViewed" display="no" />
<cfif #Gcomps.dateviewed# eq #today# and #Gcomps.whoviewed# eq "test">
<cfgridcolumn name="Name" header="Name" dataalign="left" width="40" bgcolor="green" />
<cfelse>
<cfgridcolumn name="Name" header="Name" dataalign="left" width="40" />
</cfif>
<cfgridcolumn name="PhoneNumber" header="Phone" dataalign="left" width="14" />
<cfgridcolumn name="CityState" header="City State" dataalign="left" width="20" />
<cfgridcolumn name="Type_Description" header="Type" dataalign="left" width="20" />

</cfgrid>

# Posted By vaughn | 5/2/08 11:01 AM

I'm having a couple problems with cfgrid although for the most part it is working - very cool:
1) while the same cfgrid with format=flash using cfform format=flash I get the ability to format my dates using the mask attribute. However since I did not want to go the flex route yet, but wanted the assynchronous data binding it appeared after many tests and web searches that my only choice is this AJAX approach. I saw some references to people customizing the grid renders, but is there another way? Will the next CF update fix this issue?
2) I'm trying to pass a Where clause to my cfc and have tried the above mentioned technique, but it just adds the text to a text field diadocsubject in the grid. Also if I use the clause commented out below, it blows up even though i handle the single quote issue in my CFC. What am I doing wrong here on passing a string and also how could I pass the sting with single quotes in it?

On my CFM page:
...

<cfset vp_WhereClause = "CODR" />
<cfinput name="vp_WhereClause" type="hidden" value="#vp_WhereClause#" />
<cfgrid name="searchGrid" format="html" bind="cfc:#strcomponentpath#readdiadocument({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection}, '#vp_WhereClause#')">
<cfgridcolumn name="diadocumentkey" header="diadocumentkey" display="false" />
<cfgridcolumn name="diadocno" header="docno" dataalign="right" width="70" />
<cfgridcolumn name="diadocsubject" header="subject" type="string_nocase" dataalign="left" width="80" />
<cfgridcolumn name="diadeliverydate" header="doc date" type="string_nocase" mask="mm/dd/yyyy" dataalign="right" width="80" />
</cfgrid>
...

In my CFC:
...
<cffunction name="readdiadocument" returntype="struct" output="false" access="remote">
<cfargument name="cfgridpage" required="false" >
<cfargument name="cfgridpageSize" required="false" >
<cfargument name="cfgridsortcolumn" required="false" />
<cfargument name="cfgridsortdirection" required="false" />
<cfargument name="vp_WhereClause" required="false" type="string" default="0=0 ">
<cfquery name="qRead" datasource="DocumentImaging">
SELECT Top 100 diadocumentkey, diadocno, diadocsubject, diadeliverydate
FROM diadocument

WHERE diadocument.diadocsubject = '#vp_WhereClause#'
<cfif len(arguments.cfgridsortcolumn) and len(arguments.cfgridsortdirection)>
ORDER BY #arguments.cfgridsortcolumn# #arguments.cfgridsortdirection#
<cfelse>
ORDER BY diadocno Asc
</cfif>
</cfquery>
<cfreturn queryConvertForGrid(qRead, cfgridpage, cfgridpageSize) />
</cffunction>
...

# Posted By Brian | 5/8/08 1:44 PM

Actually #2 worked, I misinterpreted the data, but how could I pass the string with single quotes in it?

# Posted By Brian | 5/9/08 5:23 PM

[Add Comment]

ColdFusion Ajax Tutorial 3: Live Data Grids