A user asked me to provide an example of related ColdFusion powered Ajax data grids, where a selection made in one grid would update the contents in a second grid. So, here is a simple example which uses the sample databases that are included with ColdFusion 8. One <cfgrid> lists the artists in the database, and the second <cfgrid> lists the art created by that artist.
First the CFC:
<cfcomponent output="false">
<cfset THIS.dsn="cfartgallery">
<!--- Get artists --->
<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="">
<!--- Local variables --->
<cfset var artists="">
<!--- Get data --->
<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>
<!--- And return it as a grid structure --->
<cfreturn QueryConvertForGrid(artists,
ARGUMENTS.page,
ARGUMENTS.pageSize)>
</cffunction>
<!--- Get art --->
<cffunction name="getArt" 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="artistid" type="numeric" required="yes">
<!--- Local variables --->
<cfset var art="">
<!--- Get data --->
<cfquery name="art" datasource="#THIS.dsn#">
SELECT artid, artname, description
FROM art
WHERE artistid = #ARGUMENTS.artistid#
<cfif ARGUMENTS.gridsortcolumn NEQ ""
and ARGUMENTS.gridsortdir NEQ "">
ORDER BY #ARGUMENTS.gridsortcolumn# #ARGUMENTS.gridsortdir#
</cfif>
</cfquery>
<!--- And return it as a grid structure --->
<cfreturn QueryConvertForGrid(art,
ARGUMENTS.page,
ARGUMENTS.pageSize)>
</cffunction>
</cfcomponent>
The client side code is pretty simple:
<cfform>
<cflayout type="hbox">
<cflayoutarea>
<h1>Artists</h1>
<cfgrid name="artists"
format="html"
pagesize="10"
striperows="yes"
selectonload="yes"
bind="cfc:2grids.getArtists({cfgridpage},
{cfgridpagesize},
{cfgridsortcolumn},
{cfgridsortdirection})">
<cfgridcolumn name="lastname" header="Last Name" />
<cfgridcolumn name="firstname" header="First Name" />
</cfgrid>
</cflayoutarea>
<cflayoutarea>
<h1>Art</h1>
<cfgrid name="art"
format="html"
pagesize="10"
striperows="yes"
bindonload="no"
bind="cfc:2grids.getArt({cfgridpage},
{cfgridpagesize},
{cfgridsortcolumn},
{cfgridsortdirection},
{artists.artistid})">
<cfgridcolumn name="artname" header="Name" />
<cfgridcolumn name="description" header="Description" />
</cfgrid>
</cflayoutarea>
</cflayout>
</cfform>
The code uses <cflayout> tags to place the two grids side-by-side. The first <cfgrid> is bound to the getArtists() method, and selectonload="yes" is used to force an initial selection so that the second <cfgrid> can be populated. The second <cfgrid> is bound to the getArt() method. It specifies bindonload="no" to prevent getArt() from being invoked before a row in the artists <cfgrid> has been selected. When getArt() is invoked, the standard four cfgrid attributes are passed to it, along with {artists.artistid}, the artistid of the currently selected row in the artists <cfgrid>.
You need to make sure there is a mapping to the cfide directory, where the scripts cf uses are kept. Not having this mapping is often the cause of the problem you are describing.
I agree with Josh's previous comment. How does one cause the second cfgrid to refresh when there is no related Artist record in the Artist cfgrid. Using this example, I'm particially successful in getting both cfgrids to display properly. But the moment I move within the "Master" data grid to a record with no matching ID value for the second datagrid's cfc query function, my second grid remains populated with the data from the previous ID matching and will not clearout.
Thanks in advance!
The scenario I noted previously involving the Master/Detail cfgrid situation was indeed in CF9. It works up to the point I mentioned in my previous post. It initially displays data in both grids for matching ID values, but does not work after moving off the record.
Thanks!
I am using this for an employee directory that shows Last Name, First Name, Phone with Personnel set to display=no and I want to presort the grid based on a the Personnel column (Order by Personnel ASC)
How do I do that?
First, Thanks for the books your write. I learn a lot of from it. even it is English book. Ha! Ha! I live in Taiwan.
Second, I reference your demo and write a test code, then I found a problem.
For the master grid column for pass id to detail grid, it value is a length large than 13 and are pure digits , for example Id='2010090300001', while it passed to detail grid, the value will change to '2.01009E+12'( like excel cell does) , it makes cfc failure.
Is there any solution for it?
According to your demo, I write a test code, then I found a problem.
For the master grid column which pass id to detail grid, if its value is pure digit and length large than 13 (for example, id='2010090300001', while it pass to detail grid, the value will be changed to '2.0100+E12'(like Ms Excel cell does), it makes CFC failure.
Is there any solution?
Tanks
P.S.: Sorry for my poor English ability.....