Solved

SortTable js not recognizing numeric data

Posted on 2006-11-09
6
591 Views
Last Modified: 2011-08-18
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.getElementsByTagName) return;
    tbls = document.getElementsByTagName("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.length;i++) {
        var cell = firstRow.cells[i];
        var txt = ts_getInnerText(cell);
        cell.innerHTML = '<a href="#" class="sortheader" '+
        'onclick="ts_resortTable(this, '+i+');return false;">' +
        txt+'<span class="sortarrow">&nbsp;&nbsp;&nbsp;</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.length;ci++) {
        if (lnk.childNodes[ci].tagName && lnk.childNodes[ci].tagName.toLowerCase() == '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].length;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("sortdir") == 'down') {
        ARROW = '&nbsp;&nbsp;&uarr;';
        newRows.reverse();
        span.setAttribute('sortdir','up');
    } else {
        ARROW = '&nbsp;&nbsp;&darr;';
        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.indexOf('sortbottom') == -1))) table.tBodies[0].appendChild(newRows[i]);}
    // do sortbottom rows only
    for (i=0;i<newRows.length;i++) { if (newRows[i].className && (newRows[i].className.indexOf('sortbottom') != -1)) table.tBodies[0].appendChild(newRows[i]);}
   
    // Delete any other arrows there may be showing
    var allspans = document.getElementsByTagName("span");
    for (var ci=0;ci<allspans.length;ci++) {
        if (allspans[ci].className == 'sortarrow') {
            if (getParent(allspans[ci],"table") == getParent(lnk,"table")) { // in the same table as us?
                allspans[ci].innerHTML = '&nbsp;&nbsp;&nbsp;';
            }
        }
    }
       
    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[SORT_COLUMN_INDEX]);
    bb = ts_getInnerText(b.cells[SORT_COLUMN_INDEX]);
    if (aa.length == 10) {
        dt1 = aa.substr(6,4)+aa.substr(3,2)+aa.substr(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.substr(0,2);
    }
    if (bb.length == 10) {
        dt2 = bb.substr(6,4)+bb.substr(3,2)+bb.substr(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.substr(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[SORT_COLUMN_INDEX]).replace(/[^0-9.]/g,'');
    bb = ts_getInnerText(b.cells[SORT_COLUMN_INDEX]).replace(/[^0-9.]/g,'');
    return parseFloat(aa) - parseFloat(bb);
}

function ts_sort_numeric(a,b) {
    aa = parseFloat(ts_getInnerText(a.cells[SORT_COLUMN_INDEX]));
    if (isNaN(aa)) aa = 0;
    bb = parseFloat(ts_getInnerText(b.cells[SORT_COLUMN_INDEX]));
    if (isNaN(bb)) bb = 0;
    return aa-bb;
}

function ts_sort_caseinsensitive(a,b) {
    aa = ts_getInnerText(a.cells[SORT_COLUMN_INDEX]).toLowerCase();
    bb = ts_getInnerText(b.cells[SORT_COLUMN_INDEX]).toLowerCase();
    if (aa==bb) return 0;
    if (aa<bb) return -1;
    return 1;
}

function ts_sort_default(a,b) {
    aa = ts_getInnerText(a.cells[SORT_COLUMN_INDEX]);
    bb = ts_getInnerText(b.cells[SORT_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(evType, fn, useCapture);
    return true;
  } else if (elm.attachEvent){
    var r = elm.attachEvent("on"+evType, fn);
    return r;
  } else {
    alert("Handler could not be removed");
  }
}

=======================================



0
Comment
Question by:MichaelEvangelista
  • 4
  • 2
6 Comments
 
LVL 6

Expert Comment

by:sodalitas
ID: 17909194
Looks to me like it's not matching any of the expressions and defaulting the sort function to text.  Quick way to validate:
in ts_ResortTable replace

    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;

with

    if (itm.match(/^\d\d[\/-]\d\d[\/-]\d\d\d\d$/)) { sortfn = ts_sort_date; alert("sorting by date"); }
    if (itm.match(/^\d\d[\/-]\d\d[\/-]\d\d$/)) { sortfn = ts_sort_date; alert("sorting by date"); }
    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.
0
 

Author Comment

by:MichaelEvangelista
ID: 17909720
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?
0
 

Author Comment

by:MichaelEvangelista
ID: 17909729
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?
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 6

Accepted Solution

by:
sodalitas earned 500 total points
ID: 17914288
(/^[£$]/)
It looks to me like this regular expression is only looking for the optional pound or dollar symbol, you want to look for the optional pound or number symbol followed by a number of integers with optional commas followed optionally by a decimal point and two integers.

Something like this (untested):
(/^[£$]\d{1,3}(,\d{3})*[\.\d{2}]/)

This says "start of string"
/^
optionally a symbol
[£$]
followed by between 1 and 3 decimal numbers
d{1,3}
followed by 0 or more sets of comma and 3 decimal numbers
(,\d{3})*
optionally follwed by a decimal point and two decimal numbers
[\.\d{2}]
end of string
/
0
 

Author Comment

by:MichaelEvangelista
ID: 17915289
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
&nbsp; into the cell. I even tried $&nbsp; , $-- , 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</span>
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.

 
0
 

Author Comment

by:MichaelEvangelista
ID: 17915304
Hmmm..... afterthought... I bet I could come up with a reg-ex that allows for that &nbsp; entry, couldn't I ? Oh well... it is working, dont want to mess with it now!
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In this article, we'll look how to sort an Array in JavaScript, including the more advanced techniques of sorting a collection of records either ascending or descending on two or more fields. Basic Sorting of Arrays First, let's look at the …
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

744 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

13 Experts available now in Live!

Get 1:1 Help Now