Sorting Currency in Bootstrap Table

Question

I want to add the currency sign to my number while making the value remain sortable as a number in my Bootstrap Table (line 30-34; column 4 in the table):

for (var p=0; p<variable_data.length; p++){
    try{
        variable_data[p]["4"] = "$" + Math.round(variable_data[p]["4"])
    } catch(e){ }
}

http://jsfiddle.net/mademoiselletse/s0d1xgzt/

The values with the '$' attached are sorted as strings instead of numbers. All the currency sorting problems I found online pertain to the DataTable and tablesorter() plugins. Is there a quicker fix to the problem than installing more jQuery plugins?

Thank you very much for your help!

Answer 1

Check the custom example here

You need to send a data-sorter function that removes the $ from your string and then compares it as numbers.

Answer 2

I ran into similar problems and finally looked at the source code of the custom sorter example from their actual website: http://issues.wenzhixin.net.cn/bootstrap-table/#options/custom-sort.html

To begin with, when defining the column containing currency/money/dollar values add a reference for sorter to whatever javascript function you want to use. Here's what my currency column definition looks like:

[
    "field" => "total",
    "title" => "Total",
    "sortable" => true,
    "sorter" => "totalCurrencySort"
]

After that you'll want to create that javascript function which executes when that particular column is sorted. Here we define totalCurrencySort which can take 4 parameters, however only the first two are needed.

function totalCurrencySort(a, b, rowA, rowB) {  
    a = +a.substring(1); // remove $
    b = +b.substring(1);
    if (a > b) return 1;
    if (a < b) return -1;
    return 0;
}

To do a proper comparison the "$" is stripped—as desired—and a numerical evaluation determines the proper sort state between the two values and returns accordingly. The rowN reference provides the full dataset of the row in case your custom sorting logic requires more complicated calculations using other data from within the row.

Answer 3

I'm too new to comment on Art's post, but wanted to add notes for what worked for me. The totalCurrencySort function in Art's post worked for me. In the table header, I had the attribute as data-sorter (as opposed to just sorter).

function totalCurrencySorter(a, b, rowA, rowB) {
a = +a.substring(1);  // remove $
b = +b.substring(1);
if (a > b) return 1;
if (a < b) return -1;
return 0;
}

function totalCurrencyFormatter(data) {
var field = this.field
return '$' + data.map(function (row) {
return +row[field].substring(1)
}).reduce(function (sum, i) {
return sum + i;
}, 0)
}

For the HTML table, I had the heading generally like this:

<thead>
<tr>
<th data-field="id" data-sortable="true">ID</th>
<th data-field="exp_date" data-sortable="true">Date</th>
<th data-field="amount" data-sortable="true" formatter="totalCurrencyFormatter" data-sorter="totalCurrencySorter">Amount</th>
</tr>
</thead>

Answer 4

To add to Art and OptimisticToaster's answers, I'm also too new to comment, so my take on the problem is this:

function totalCurrencySort(a, b, rowA, rowB) {  
    
    a = Number(a.replace(/[^0-9.-]+/g,""));
    b = Number(b.replace(/[^0-9.-]+/g,""));
    
    if (a > b) return 1;
    if (a < b) return -1;
    return 0;
}

I was working with numbers formatted as so £1,000.50 so stripping any non-numeric allows the sort to work whatever format the figure is using.