Jquery Datatables how to sort a custom formatted numeric column?

Question

I have a column in a data table which renders numeric values as formatted currency values e.g "$5,66666.77 USD". Here is the part of the Jquery table definition for this column,

            data: "amount",
            orderable: true,
            searchable: false,
            render: (data, type, row) => {
                var formattedAmount = "$" + data.toFixed(2).replace(/(\d)(?=(\d{3})+\.)/g, "$1,") + " " + "USD";
                return formattedAmount;
            }

When I don't have the "USD" suffix it gets sorted correctly. But when I have USD it sorts like a string. Is there a work around for this? Can I define a different value for sort data?

I am using this the Typescript typings.

"data" attribute accepts ObjectColumnData, I tried to use this but it's not using the sort field I define.

 interface ObjectColumnData {
        _: string;
        filter?: string;
        display?: string;
        type?: string;
        sort?: string;
    }

Do I have to write a custom sort for this?

Gyrocode.com · Accepted Answer · 2017-03-31 04:31:48Z

6

You can return formatted value only when data would be displayed, otherwise you can return unformatted value for sorting, filtering or type detection.

For example, in JavaScript:

render: function(data, type, row){
    if(type === 'display'){
        data = "$" + data.toFixed(2).replace(/(\d)(?=(\d{3})+\.)/g, "$1,") + " " + "USD"; 
    }

    return data;
}

answered Mar 31, 2017 at 4:31

Gyrocode.com

59k16 gold badges157 silver badges192 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dhanuka777 Over a year ago

a-w-e-s-o-m-e!!

JestaBlunt Over a year ago

thx, sorting now works as it should despite of the slashy number format ("123456/7")

Collectives™ on Stack Overflow

Jquery Datatables how to sort a custom formatted numeric column?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related