asked on

cfgrid column record counts.

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"
bind="cfc:cfc.Testlist.get_Testlist({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection},{testid})"
             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">

</cfgrid>

</cfform>

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?

azadisaryev

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!

Azadi

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

<!--- IMPORTANT: 
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">
<html>
<head>
<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)
}
</script>
</head>
<body>
<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"
		bind="javascript:getGridData({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection},{testid})"
		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">
	</cfgrid>
</cfform>
</body>
</html>




<!--- 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") --->
<cfcomponent>
	<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))>
		</cfloop>
		<!--- 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>
	</cffunction>
</cfcomponent>

Open in new window

jfreeman2010

ASKER

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!!

ASKER CERTIFIED SOLUTION

azadisaryev

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jfreeman2010

ASKER

that will work. thank you very much.