We help IT Professionals succeed at work.

cfgrid column record counts.

jfreeman2010 asked
cfgrid column records count:

I have the following CFGRID with a bind attribute:

<cfform method="get" format="HTML" id="Testlist" name="Testlist" >
  <cfgrid name="Testlist" striperows="yes" format="html" selectmode="row"  sort="yes"
        selectonload="false"  pagesize="26" height="690"  colheaderbold="yes"
                           onfocus="" >
        <cfgridcolumn name="TEST_CODE"         width="120" header="TEST Code"             headeralign="left"  display="no"   >
        <cfgridcolumn name="FIRST_NAME"          width="160" header="First Name"       headeralign="left">
        <cfgridcolumn name="LAST_NAME"       width="160" header="Last Name"             headeralign="left">
        <cfgridcolumn name="TEST_1"             width="120" header="Test 1"             headeralign="left">
        <cfgridcolumn name="TEST_2"             width="100" header="Test 2"             headeralign="left">


The CFC call a stored produce to return the result query.

I had the cfgrid display find.

How do I get total result query count, total TEST_1 count and TEST_2 count, and display them on top of the grid?
Watch Question

you can do this, but you need to use <cfajaxproxy> tag and bind your grid to a javascript function. plus there are some pre-requisites about data returned by your query/storedproc.

attached is a working example (main .cfm page and test.cfc).

read comments in code!

<!--- this is the mail page. save it as .cfm file and run it in your browser --->

for the exmple to work, make sure that:
a) secureJSOM is NOT enabled in your application;
b) your Application.cfm/cfc DOES NOT output anything (like page headers, meta tags, etc etc)
c) your DO NOT have onRequest() method in your Application.cfc
d) test.cfc is saved in the same folder as this page

<!--- create a javascript proxy to our test.cfc cfc using cfajaxproxy tag: --->
<cfajaxproxy cfc="test" jsclassname="jsproxy">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<script type="text/javascript">
/* js function that populates the cfgrid AND the 3 totals above it */
getGridData = function(page,pagesize,sortcol,sortdir,testid){
	var p = new jsproxy(); // create an instance of our proxy to our cfc
	var res = p.get_Testlist(page,pagesize,sortcol,sortdir,testid); // call the get_Testlist() method of our cfc passing it all required agruments
	/* populate the totals (total records and sums of TEST_1 and TEST_2 columns) */
	// IMPORTANT: key names of structures created using dot-notation (e.g: result.griddata in our cfc) are returned by cf in UPPERCASE!
	// javascript is case-sensitive, so must use uppercased key names
	document.getElementById('ttlrec').innerHTML = res.GRIDDATA.TOTALROWCOUNT;
	document.getElementById('test1sum').innerHTML = res.TEST1SUM;
	document.getElementById('test2sum').innerHTML = res.TEST2SUM;
	document.getElementById('totals').style.visibility = 'visible'; // show the totals <div>
	return res.GRIDDATA; // return the data to the cfgrid (populate cfgrid with data)
<cfform name="f1">
	<cfinput type="hidden" name="testid" value="1">
	<!--- totals will be shown in this div --->
	<div id="totals" style="visibility:hidden">Total Records: <span id="ttlrec"></span> | Test 1 Total: <span id="test1sum"></span> | Test 2 Total: <span id="test2sum"></span></div>
	<!--- the grid.
	IMPORTANT: it is now binded to our getGridData() javascript function, not to a cfc directly! --->
	<cfgrid name="Testlist" striperows="yes" format="html" selectmode="row"  sort="yes"
		selectonload="false"  pagesize="26" height="690"  colheaderbold="yes"
		onfocus="" >
			<cfgridcolumn name="TEST_CODE"         width="120" header="TEST Code"             headeralign="left"  display="no"   >
			<cfgridcolumn name="FIRST_NAME"          width="160" header="First Name"       headeralign="left">
			<cfgridcolumn name="LAST_NAME"       width="160" header="Last Name"             headeralign="left">
			<cfgridcolumn name="TEST_1"             width="120" header="Test 1"             headeralign="left">
			<cfgridcolumn name="TEST_2"             width="100" header="Test 2"             headeralign="left">

<!--- this is test.cfc that returns data for the cfgrid  - save this as test.cfc in the same folder as calling page for the example to work --->
<!--- IMPORTANT: note that the get_Testlist() function now returns a STRUCT (returntype="struct") --->
	<cffunction name="get_Testlist" access="remote" returntype="struct" output="no">
		<cfargument name="page" type="numeric" required="true">
		<cfargument name="pagesize" type="numeric" required="true">
		<cfargument name="sortcol" type="string" required="true">
		<cfargument name="sortdir" type="string" required="true">
		<cfargument name="testid" required="yes" type="numeric">
		<!--- the example creates a query using querynew() function - you will be using your storedproc instead --->
		<cfset var data = querynew('TEST_CODE,FIRST_NAME,LAST_NAME,TEST_1,TEST_2', 'integer,varchar,varchar,integer,integer')>
		<cfset var x = 1><!--- this is cfloop index var, for example purposes only. ignore.--->
		<cfset var result = structnew()><!--- this is the structure this function will return --->
		<!--- populate query with some data - this is for example purposes only. ignore. --->
		<cfloop from="1" to="100" index="x">
			<cfset queryaddrow(data)>
			<cfset querysetcell(data, 'TEST_CODE', arguments.testid)>
			<cfset querysetcell(data, 'FIRST_NAME', 'First Name ' & x)>
			<cfset querysetcell(data, 'LAST_NAME', 'Last Name ' & x)>
			<cfset querysetcell(data, 'TEST_1', randrange(1,50))>
			<cfset querysetcell(data, 'TEST_2', randrange(51,100))>
		<!--- IMPORTANT:
			the TEST_1 and TEST_2 columns returned by your query/storedproc MUST NOT contain nulls/empty strings!
			all their values MUST be numeric, otherwise arraysum() functions below will fail --->
		<!--- populate the structure to return: --->
		<cfset result.griddata = queryconvertforgrid(data, arguments.page, arguments.pagesize)><!--- this key holds data for cfgrid --->
		<cfset result.test1sum = arraysum(data['TEST_1'])><!--- this key holds sum of TEST_2 column values --->
		<cfset result.test2sum = arraysum(data['TEST_2'])><!--- this key holds sum of TEST_2 column values --->
		<cfreturn result>

Open in new window


Thank you very much for the example. Its works great!!.  I only have a little issue here:  I need a count on TEST_1 and TEST_2.  In the live data I return Y/N on those two columns.  If is 'Y', it count as 1, if is 'N' it did not count.  so in the test.cfc, line 83,84, how can I change the arraysum to count base on the Y/N condition?

Thank you!!
do you mean that the values of those columns are actually text strings 'Y' or 'N'?

you can edit your query/storedproc and use CASE-WHEN-THEN-ELSE-END construct to create 2 new columns with 1 or 0 as values, then use arraysum() on those columns.

or you can do this in the cfc function instead of arraysum():

<cfset result.test1sum = 0>
<cfset result.test2sum = 0>
<cfoutput query="yourqueryname">
  <cfif yourqueryname.TEST_1 eq 'Y'>
    <cfset result.test1sum++>
  <cfif yourqueryname.TEST_2 eq 'Y'>
    <cfset result.test2sum++>



that will work.  thank you very much.