CODEWITHSUNDEEP
javascriptdjangosortingtablesorter
Anthony Roberts
Anthony Roberts

Reputation: 2021

jQuery tablesorter sorting formatted currency

EDIT, new code based on Mottie's answer:

jQuery.tablesorter.addParser({
    id: "monetaryValue",
    is: function (s) {
        return false;
    }, format: function (s) {
        return s.replace('$','').replace(/,/g,'');
    }, type: "numeric"
});
var tablezor = jQuery(".tablesorter");
tablezor.tablesorter({
        headers: {
            4 : { sorter: "monetaryValue" }
        },
        sortList: [[7,0]]
});

and HTML:

<table class="tablesorter">
<thead>
    <tr>
        ...
        <th>Processing Fees</th>
        ...
    </tr>
</thead>
<tbody>
{% for cs in customer_stats %}
    <tr class="js_striped">
        ...
        <td>
            <script>
                var options = {style:"currency", currency:"USD", minimumFractionDigits: 2,maximumFractionDigits: 2};
                document.write(new Intl.NumberFormat("en-US", options).format({{ customer_stats[cs]['processing_fee'] }}));
            </script>
        </td>
        ....
    </tr>
{% endfor %}
</tbody>
</table>

I've tried a dozen examples to fix this issue, and nothing is working.

I'm using jQuery.tablesorter to sort a table, and the currency columns will not sort correctly when I format the numbers as currency (US Dollars, in the format $1,945.00)

It's a Django template, and the template variables render as unformatted numbers like 1945.0, so I need to add formatting using javascript. I wanted to use the Humanize template filters library, but we use Jinja, which doesn't have that. I need to do the formatting on the client, so python suggestions probably aren't going to work.

When it's just a number, it sorts fine. When I use a little script tag (yes, I know this isn't the best way, it's a short-term fix untill we re-write the front-end with Backbone) to format the number as currency, the sort doesn't work. It sorts like this:

$3,380.00

$350.00

$353.24

$3,535.24

etc.

here's the function to format as currency:

function formatDollar(num) {
    var p = num.toFixed(2).split(".");
    return '$' + p[0].split("").reverse().reduce(function(acc, num, i, orig) {
        return  num + (i && !(i % 3) ? "," : "") + acc;
    }, "") + "." + p[1];
}

Here's the HTML:

<table class="tablesorter">
    <thead>
        <tr>
            ...
            <th class="{sorter:'monetaryValue'}">Processing Fees</th>
            ...
        </tr>
    </thead>
    <tbody>
    {% for cs in customer_stats %}
        <tr class="js_striped">
            ...
            <td>
                <script>
                    var money = formatDollar({{ customer_stats[cs]['processing_fee'] }});
                    document.write(money);
                </script>
            </td>
            ....
        </tr>
    {% endfor %}
    </tbody>
</table>

and the custom parser for tablesorter:

var tablezor = jQuery(".tablesorter");
tablezor.tablesorter({
        sortList: [[1,1]]
});
tablezor.addParser({
    id: "monetaryValue",
    is: function (s) {
        return false;
    }, format: function (s) {
        return s.replace('$','').replace(/,/g,'');
    }, type: "numeric"
});

Feel free to tell me how terrible any of this is; Any and all criticizm is welcome if you can tell me how to get tablesorter to sort currency correctly.

Thanks,

Upvotes: 2

Views: 2731

Answers (2)

Anthony Roberts
Anthony Roberts

Reputation: 2021

The solution to my problem was to do the formatting on the server.

May this be a lesson to not put hacky crap in my markup.

For the record, in my Django view, I did this:

customer_stats[c['customer']]['processing_fee'] = '${:,.2f}'.format(float(c['processing_fee']))

Then I removed the custom parser and used this:

tablezor.tablesorter({
    sortList: [[4,1]],
    textExtraction: function(node){ 
        return $(node).text().replace(/[,$£€]/g,'');
     }
});

The textExtraction took care of the parsing problem.

Thanks again Mottie for all the effort.

Upvotes: 3

Mottie
Mottie

Reputation: 86433

The main issue here is that the parser needs to be added using $.tablesorter.addParser(), not $('.tablesorter').addParser().

jQuery.tablesorter.addParser({
    id: "monetaryValue",
    is: function (s) {
        return false;
    }, format: function (s) {
        var n = parseFloat( s.replace('$','').replace(/,/g,'') );
        return isNaN(n) ? s : n;
    }, type: "numeric"
});
var tablezor = jQuery(".tablesorter");
tablezor.tablesorter({
        headers: {
            0 : { sorter: "monetaryValue" }
        },
        sortList: [[1,1]]
});

If you are using my fork of tablesorter, the default (automatically detected) currency parser will work with the provided currency values (demo).

For currency from other countries, just set the usNumberFormat option to false.

Upvotes: 4

Related Questions