MichaelEvangelista
asked on
SortTable js not recognizing numeric data
On this page, http://sshomes.info/estrellaprices.cfm,
I have a table using the very clever SortTable javascript ( code below )
but I am having an issue.
The Price column is being sorted alphanumerically, instead of numerically,
so that 5,000 is being sorted after 10,000 and 20,000.
( It makes no difference whether I remove the $ or commas from the data ).
However, the lot numbers in the first column are sorting correctly by number.
Setup of this script is extremely simple, and I have not made any alterations to the js code. I just cannot figure out why the first column is automatically recognized as numeric, but not the price column.
I think the key lies in these lines - but I am not a javascript reader/writer
===
if (itm.match(/^[£$]/)) sortfn = ts_sort_currency;
if (itm.match(/^[\d\.]+$/)) sortfn = ts_sort_numeric;
===
Below is the entire JS code I am using - and of course the source of the page shows the table structure.
Any help would be greatly appreciated.
========================== ========
addEvent(window, "load", sortables_init);
var SORT_COLUMN_INDEX;
function sortables_init() {
// Find all tables with class sortable and make them sortable
if (!document.getElementsByTa gName) return;
tbls = document.getElementsByTagN ame("table ");
for (ti=0;ti<tbls.length;ti++) {
thisTbl = tbls[ti];
if (((' '+thisTbl.className+' ').indexOf("sortable") != -1) && (thisTbl.id)) {
//initTable(thisTbl.id);
ts_makeSortable(thisTbl);
}
}
}
function ts_makeSortable(table) {
if (table.rows && table.rows.length > 0) {
var firstRow = table.rows[0];
}
if (!firstRow) return;
// We have a first row: assume it's the header, and make its contents clickable links
for (var i=0;i<firstRow.cells.lengt h;i++) {
var cell = firstRow.cells[i];
var txt = ts_getInnerText(cell);
cell.innerHTML = '<a href="#" class="sortheader" '+
'onclick="ts_resortTable(t his, '+i+');return false;">' +
txt+'<span class="sortarrow"> &n bsp; </span></a >';
}
}
function ts_getInnerText(el) {
if (typeof el == "string") return el;
if (typeof el == "undefined") { return el };
if (el.innerText) return el.innerText; //Not needed but it is faster
var str = "";
var cs = el.childNodes;
var l = cs.length;
for (var i = 0; i < l; i++) {
switch (cs[i].nodeType) {
case 1: //ELEMENT_NODE
str += ts_getInnerText(cs[i]);
break;
case 3: //TEXT_NODE
str += cs[i].nodeValue;
break;
}
}
return str;
}
function ts_resortTable(lnk,clid) {
// get the span
var span;
for (var ci=0;ci<lnk.childNodes.len gth;ci++) {
if (lnk.childNodes[ci].tagNam e && lnk.childNodes[ci].tagName .toLowerCa se() == 'span') span = lnk.childNodes[ci];
}
var spantext = ts_getInnerText(span);
var td = lnk.parentNode;
var column = clid || td.cellIndex;
var table = getParent(td,'TABLE');
// Work out a type for the column
if (table.rows.length <= 1) return;
var itm = ts_getInnerText(table.rows [1].cells[ column]);
sortfn = ts_sort_caseinsensitive;
if (itm.match(/^\d\d[\/-]\d\d [\/-]\d\d\ d\d$/)) sortfn = ts_sort_date;
if (itm.match(/^\d\d[\/-]\d\d [\/-]\d\d$ /)) sortfn = ts_sort_date;
if (itm.match(/^[£$]/)) sortfn = ts_sort_currency;
if (itm.match(/^[\d\.]+$/)) sortfn = ts_sort_numeric;
SORT_COLUMN_INDEX = column;
var firstRow = new Array();
var newRows = new Array();
for (i=0;i<table.rows[0].lengt h;i++) { firstRow[i] = table.rows[0][i]; }
for (j=1;j<table.rows.length;j ++) { newRows[j-1] = table.rows[j]; }
newRows.sort(sortfn);
if (span.getAttribute("sortdi r") == 'down') {
ARROW = ' ↑';
newRows.reverse();
span.setAttribute('sortdir ','up');
} else {
ARROW = ' ↓';
span.setAttribute('sortdir ','down');
}
// We appendChild rows that already exist to the tbody, so it moves them rather than creating new ones
// don't do sortbottom rows
for (i=0;i<newRows.length;i++) { if (!newRows[i].className || (newRows[i].className && (newRows[i].className.inde xOf('sortb ottom') == -1))) table.tBodies[0].appendChi ld(newRows [i]);}
// do sortbottom rows only
for (i=0;i<newRows.length;i++) { if (newRows[i].className && (newRows[i].className.inde xOf('sortb ottom') != -1)) table.tBodies[0].appendChi ld(newRows [i]);}
// Delete any other arrows there may be showing
var allspans = document.getElementsByTagN ame("span" );
for (var ci=0;ci<allspans.length;ci ++) {
if (allspans[ci].className == 'sortarrow') {
if (getParent(allspans[ci],"t able") == getParent(lnk,"table")) { // in the same table as us?
allspans[ci].innerHTML = ' ';
}
}
}
span.innerHTML = ARROW;
}
function getParent(el, pTagName) {
if (el == null) return null;
else if (el.nodeType == 1 && el.tagName.toLowerCase() == pTagName.toLowerCase()) // Gecko bug, supposed to be uppercase
return el;
else
return getParent(el.parentNode, pTagName);
}
function ts_sort_date(a,b) {
// y2k notes: two digit years less than 50 are treated as 20XX, greater than 50 are treated as 19XX
aa = ts_getInnerText(a.cells[SO RT_COLUMN_ INDEX]);
bb = ts_getInnerText(b.cells[SO RT_COLUMN_ INDEX]);
if (aa.length == 10) {
dt1 = aa.substr(6,4)+aa.substr(3 ,2)+aa.sub str(0,2);
} else {
yr = aa.substr(6,2);
if (parseInt(yr) < 50) { yr = '20'+yr; } else { yr = '19'+yr; }
dt1 = yr+aa.substr(3,2)+aa.subst r(0,2);
}
if (bb.length == 10) {
dt2 = bb.substr(6,4)+bb.substr(3 ,2)+bb.sub str(0,2);
} else {
yr = bb.substr(6,2);
if (parseInt(yr) < 50) { yr = '20'+yr; } else { yr = '19'+yr; }
dt2 = yr+bb.substr(3,2)+bb.subst r(0,2);
}
if (dt1==dt2) return 0;
if (dt1<dt2) return -1;
return 1;
}
function ts_sort_currency(a,b) {
aa = ts_getInnerText(a.cells[SO RT_COLUMN_ INDEX]).re place(/[^0 -9.]/g,'') ;
bb = ts_getInnerText(b.cells[SO RT_COLUMN_ INDEX]).re place(/[^0 -9.]/g,'') ;
return parseFloat(aa) - parseFloat(bb);
}
function ts_sort_numeric(a,b) {
aa = parseFloat(ts_getInnerText (a.cells[S ORT_COLUMN _INDEX]));
if (isNaN(aa)) aa = 0;
bb = parseFloat(ts_getInnerText (b.cells[S ORT_COLUMN _INDEX]));
if (isNaN(bb)) bb = 0;
return aa-bb;
}
function ts_sort_caseinsensitive(a, b) {
aa = ts_getInnerText(a.cells[SO RT_COLUMN_ INDEX]).to LowerCase( );
bb = ts_getInnerText(b.cells[SO RT_COLUMN_ INDEX]).to LowerCase( );
if (aa==bb) return 0;
if (aa<bb) return -1;
return 1;
}
function ts_sort_default(a,b) {
aa = ts_getInnerText(a.cells[SO RT_COLUMN_ INDEX]);
bb = ts_getInnerText(b.cells[SO RT_COLUMN_ INDEX]);
if (aa==bb) return 0;
if (aa<bb) return -1;
return 1;
}
function addEvent(elm, evType, fn, useCapture)
// addEvent and removeEvent
// cross-browser event handling for IE5+, NS6 and Mozilla
// By Scott Andrew
{
if (elm.addEventListener){
elm.addEventListener(evTyp e, fn, useCapture);
return true;
} else if (elm.attachEvent){
var r = elm.attachEvent("on"+evTyp e, fn);
return r;
} else {
alert("Handler could not be removed");
}
}
========================== ========== ===
I have a table using the very clever SortTable javascript ( code below )
but I am having an issue.
The Price column is being sorted alphanumerically, instead of numerically,
so that 5,000 is being sorted after 10,000 and 20,000.
( It makes no difference whether I remove the $ or commas from the data ).
However, the lot numbers in the first column are sorting correctly by number.
Setup of this script is extremely simple, and I have not made any alterations to the js code. I just cannot figure out why the first column is automatically recognized as numeric, but not the price column.
I think the key lies in these lines - but I am not a javascript reader/writer
===
if (itm.match(/^[£$]/)) sortfn = ts_sort_currency;
if (itm.match(/^[\d\.]+$/)) sortfn = ts_sort_numeric;
===
Below is the entire JS code I am using - and of course the source of the page shows the table structure.
Any help would be greatly appreciated.
==========================
addEvent(window, "load", sortables_init);
var SORT_COLUMN_INDEX;
function sortables_init() {
// Find all tables with class sortable and make them sortable
if (!document.getElementsByTa
tbls = document.getElementsByTagN
for (ti=0;ti<tbls.length;ti++)
thisTbl = tbls[ti];
if (((' '+thisTbl.className+' ').indexOf("sortable") != -1) && (thisTbl.id)) {
//initTable(thisTbl.id);
ts_makeSortable(thisTbl);
}
}
}
function ts_makeSortable(table) {
if (table.rows && table.rows.length > 0) {
var firstRow = table.rows[0];
}
if (!firstRow) return;
// We have a first row: assume it's the header, and make its contents clickable links
for (var i=0;i<firstRow.cells.lengt
var cell = firstRow.cells[i];
var txt = ts_getInnerText(cell);
cell.innerHTML = '<a href="#" class="sortheader" '+
'onclick="ts_resortTable(t
txt+'<span class="sortarrow"> &n
}
}
function ts_getInnerText(el) {
if (typeof el == "string") return el;
if (typeof el == "undefined") { return el };
if (el.innerText) return el.innerText; //Not needed but it is faster
var str = "";
var cs = el.childNodes;
var l = cs.length;
for (var i = 0; i < l; i++) {
switch (cs[i].nodeType) {
case 1: //ELEMENT_NODE
str += ts_getInnerText(cs[i]);
break;
case 3: //TEXT_NODE
str += cs[i].nodeValue;
break;
}
}
return str;
}
function ts_resortTable(lnk,clid) {
// get the span
var span;
for (var ci=0;ci<lnk.childNodes.len
if (lnk.childNodes[ci].tagNam
}
var spantext = ts_getInnerText(span);
var td = lnk.parentNode;
var column = clid || td.cellIndex;
var table = getParent(td,'TABLE');
// Work out a type for the column
if (table.rows.length <= 1) return;
var itm = ts_getInnerText(table.rows
sortfn = ts_sort_caseinsensitive;
if (itm.match(/^\d\d[\/-]\d\d
if (itm.match(/^\d\d[\/-]\d\d
if (itm.match(/^[£$]/)) sortfn = ts_sort_currency;
if (itm.match(/^[\d\.]+$/)) sortfn = ts_sort_numeric;
SORT_COLUMN_INDEX = column;
var firstRow = new Array();
var newRows = new Array();
for (i=0;i<table.rows[0].lengt
for (j=1;j<table.rows.length;j
newRows.sort(sortfn);
if (span.getAttribute("sortdi
ARROW = ' ↑';
newRows.reverse();
span.setAttribute('sortdir
} else {
ARROW = ' ↓';
span.setAttribute('sortdir
}
// We appendChild rows that already exist to the tbody, so it moves them rather than creating new ones
// don't do sortbottom rows
for (i=0;i<newRows.length;i++)
// do sortbottom rows only
for (i=0;i<newRows.length;i++)
// Delete any other arrows there may be showing
var allspans = document.getElementsByTagN
for (var ci=0;ci<allspans.length;ci
if (allspans[ci].className == 'sortarrow') {
if (getParent(allspans[ci],"t
allspans[ci].innerHTML = ' ';
}
}
}
span.innerHTML = ARROW;
}
function getParent(el, pTagName) {
if (el == null) return null;
else if (el.nodeType == 1 && el.tagName.toLowerCase() == pTagName.toLowerCase()) // Gecko bug, supposed to be uppercase
return el;
else
return getParent(el.parentNode, pTagName);
}
function ts_sort_date(a,b) {
// y2k notes: two digit years less than 50 are treated as 20XX, greater than 50 are treated as 19XX
aa = ts_getInnerText(a.cells[SO
bb = ts_getInnerText(b.cells[SO
if (aa.length == 10) {
dt1 = aa.substr(6,4)+aa.substr(3
} else {
yr = aa.substr(6,2);
if (parseInt(yr) < 50) { yr = '20'+yr; } else { yr = '19'+yr; }
dt1 = yr+aa.substr(3,2)+aa.subst
}
if (bb.length == 10) {
dt2 = bb.substr(6,4)+bb.substr(3
} else {
yr = bb.substr(6,2);
if (parseInt(yr) < 50) { yr = '20'+yr; } else { yr = '19'+yr; }
dt2 = yr+bb.substr(3,2)+bb.subst
}
if (dt1==dt2) return 0;
if (dt1<dt2) return -1;
return 1;
}
function ts_sort_currency(a,b) {
aa = ts_getInnerText(a.cells[SO
bb = ts_getInnerText(b.cells[SO
return parseFloat(aa) - parseFloat(bb);
}
function ts_sort_numeric(a,b) {
aa = parseFloat(ts_getInnerText
if (isNaN(aa)) aa = 0;
bb = parseFloat(ts_getInnerText
if (isNaN(bb)) bb = 0;
return aa-bb;
}
function ts_sort_caseinsensitive(a,
aa = ts_getInnerText(a.cells[SO
bb = ts_getInnerText(b.cells[SO
if (aa==bb) return 0;
if (aa<bb) return -1;
return 1;
}
function ts_sort_default(a,b) {
aa = ts_getInnerText(a.cells[SO
bb = ts_getInnerText(b.cells[SO
if (aa==bb) return 0;
if (aa<bb) return -1;
return 1;
}
function addEvent(elm, evType, fn, useCapture)
// addEvent and removeEvent
// cross-browser event handling for IE5+, NS6 and Mozilla
// By Scott Andrew
{
if (elm.addEventListener){
elm.addEventListener(evTyp
return true;
} else if (elm.attachEvent){
var r = elm.attachEvent("on"+evTyp
return r;
} else {
alert("Handler could not be removed");
}
}
==========================
ASKER
Thanks - very slick test... and you are right, no alert.
So, on to phase 2
>> At that point you just need to change your regular expression to match the format of your data, or add a new one.
I figured the fix would be something like this, but I am not great with RegExp's.
Can you help me with this?
So, on to phase 2
>> At that point you just need to change your regular expression to match the format of your data, or add a new one.
I figured the fix would be something like this, but I am not great with RegExp's.
Can you help me with this?
ASKER
I am assuming the fix is in these lines..
if (itm.match(/^\d\d[\/-]\d\d [\/-]\d\d\ d\d$/)) sortfn = ts_sort_date;
if (itm.match(/^\d\d[\/-]\d\d [\/-]\d\d$ /)) sortfn = ts_sort_date;
if (itm.match(/^[£$]/)) sortfn = ts_sort_currency;
if (itm.match(/^[\d\.]+$/)) sortfn = ts_sort_numeric;
But it seems, with the $ in the data, it should already be sorting by currency?
if (itm.match(/^\d\d[\/-]\d\d
if (itm.match(/^\d\d[\/-]\d\d
if (itm.match(/^[£$]/)) sortfn = ts_sort_currency;
if (itm.match(/^[\d\.]+$/)) sortfn = ts_sort_numeric;
But it seems, with the $ in the data, it should already be sorting by currency?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I was so sure that was going to fix it... but it appears there is more to the story.
I just spent an hour or so testing various possibilities - took that column out altogether and replaced it with a simple sequence of numbers, still no luck sorting numerically.. so it isnt the $ sign or commas... then what the...? more hunting...
Apparently this script does not like the fact that, if there is no price, I was inserting
into the cell. I even tried $ , $-- , or just plain --... no luck. It wanted a dollar sign and numbers in every cell in that column. When I put in $0, everything worked just like it should! ( aha!... but of course, client doesnt want $0 showing... )
So, funky as it may be, I found something that appears to be working.
If there is no price to be shown, the cell now gets
<span style="display:none">$0</s pan>
which inserts a $0 price, but tells the browser not to show it.
As far as I can tell, this works just fine.
I will accept your answer because, if things were working the way I thought they should have been, you would have been right on the money. That and your testing definitely led me to find a solution that worked.
Thanks again for your time and support.
I just spent an hour or so testing various possibilities - took that column out altogether and replaced it with a simple sequence of numbers, still no luck sorting numerically.. so it isnt the $ sign or commas... then what the...? more hunting...
Apparently this script does not like the fact that, if there is no price, I was inserting
into the cell. I even tried $ , $-- , or just plain --... no luck. It wanted a dollar sign and numbers in every cell in that column. When I put in $0, everything worked just like it should! ( aha!... but of course, client doesnt want $0 showing... )
So, funky as it may be, I found something that appears to be working.
If there is no price to be shown, the cell now gets
<span style="display:none">$0</s
which inserts a $0 price, but tells the browser not to show it.
As far as I can tell, this works just fine.
I will accept your answer because, if things were working the way I thought they should have been, you would have been right on the money. That and your testing definitely led me to find a solution that worked.
Thanks again for your time and support.
ASKER
Hmmm..... afterthought... I bet I could come up with a reg-ex that allows for that entry, couldn't I ? Oh well... it is working, dont want to mess with it now!
in ts_ResortTable replace
if (itm.match(/^\d\d[\/-]\d\d
if (itm.match(/^\d\d[\/-]\d\d
if (itm.match(/^[£$]/)) sortfn = ts_sort_currency;
if (itm.match(/^[\d\.]+$/)) sortfn = ts_sort_numeric;
with
if (itm.match(/^\d\d[\/-]\d\d
if (itm.match(/^\d\d[\/-]\d\d
if (itm.match(/^[£$]/)) { sortfn = ts_sort_currency; alert("sorting by currency");}
if (itm.match(/^[\d\.]+$/)) { sortfn = ts_sort_numeric; alert("sorting by numeric");}
If you resort by price and don't see one of those alerts, you know it's sorting by default, which is text. At that point you just need to change your regular expression to match the format of your data, or add a new one.