Solved

cfgrid format="html" numbers sorted as text?

Posted on 2011-02-14
4
498 Views
Last Modified: 2012-05-11
I am using CF9 and cfgrid, with cfgrid format="html".  

The cfgrid sorts simple numbers in text order, not number order:

0
1
10
101
11
12
2
21

How do I fix this?
The grids sorts correctly if format="flash", but I want to use format="html".

Here is my code:
<!--- cfgrid.cfc --->
<cffunction name="getLogins" returnType="query" access="remote" hint="Retrieves logins">
<cfset var data="">
<cfquery name="data" datasource="dsn">
SELECT PreferredName, TotalLogins FROM cfgriddata
</cfquery>
<cfreturn data>
</cffunction>

Open in new window

and
<!--- test.cfm --->
<cfinvoke component="cfgrid" method="getLogins" returnvariable="logins">
</cfinvoke>
<cfform action="" method="post" id="form1">
 <cfgrid name="data" width="260" query="logins" colheaderbold="Yes" font="Tahoma" fontsize="14" rowHeaders="No" height="600" format="html"> 
	<cfgridcolumn name="PreferredName" header="Name" width="200"> 
	<cfgridcolumn name="TotalLogins" type="numeric" header="Logins" width="60" dataalign="right"> 
</cfgrid>
</cfform>

Open in new window

The mysql table is:
-- ----------------------------
--  Table structure for `cfgriddata`
-- ----------------------------
DROP TABLE IF EXISTS `cfgriddata`;
CREATE TABLE `cfgriddata` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `PreferredName` varchar(24) NOT NULL DEFAULT '',
  `TotalLogins` smallint(6) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=851 DEFAULT CHARSET=utf8 PACK_KEYS=1;

-- ----------------------------
--  Records of `cfgriddata`
-- ----------------------------
BEGIN;
INSERT INTO `cfgriddata` VALUES ('19', 'Carol', '11'), ('33', 'Mary', '1'), ('98', 'Lucille', '0'), ('413', 'Max', '21'), ('679', 'Kate', '10'), ('708', 'Julie', '101'), ('728', 'William', '3'), ('748', 'Dawn', '12'), ('785', 'Judy', '2');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

Open in new window

0
Comment
Question by:ShanghaiD
  • 3
4 Comments
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34894599
Hi,

did you try to put order by within query.


<cfquery name="data" datasource="dsn">
SELECT PreferredName, TotalLogins FROM cfgriddata order by totallogins
</cfquery>

Open in new window

0
 

Author Comment

by:ShanghaiD
ID: 34894629
Yes, it sorts correctly then (when the grid first loads the query result).  However, the sort order reverts to text order when the cfgrid sorts each column (PreferredName by person name -- correclty -- and TotalLogins by number -- incorrectly -- reverts to text sort order).
0
 

Accepted Solution

by:
ShanghaiD earned 0 total points
ID: 34897247
I've found a fix by changing from "query" to "bind" and now cfgrid sorts correctly with format="html" (which I want instead of format="flash" so I can use Raymond Camden's neat formatting tricks).

Exctracted revisions to my code are below (in case anyone is interested):
<cffunction name="logins"
access="remote"
returntype="struct">
....
....
<cfreturn QueryConvertForGrid(data,
ARGUMENTS.page,
ARGUMENTS.pageSize)>
</cffunction>


<cfgrid
....
....
bind="cfc:cfgrid.getLogins({cfgridpage},
{cfgridpagesize},
{cfgridsortcolumn},
{cfgridsortdirection})">

Open in new window

0
 

Author Closing Comment

by:ShanghaiD
ID: 34936396
Alternative solution found.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now