How to sort numeric column in jqgrid

Question

In my jqGrid I have a column that has this format: '1.234,678'. I want to sort this column, and I am using this code:

{name:'ZMENG_SOR_VRKME', index:'ZMENG_SOR_VRKME', width:'5', align:'right', sorttype:'number' }

Sorting doesn't work. I think that it is not considering .(dot) as thousands, for examples if I have 21,000 - 991,000 - 1.188,000. The results is: 991,000 - 21,000 - 1.188,000.

How can I solve this?

Answer 1

If I understand your problem correctly you should change the format of data filled in the grid. You should use pure numbers as the input and use formatter: "number" to include thousands separator in the number. It will be the best way to solve your problem in my opinion. You can use correct language file grid.locale-XX.js the thousands separator will be used corresponds the locale. Alternatively you can use decimalSeparator and thousandsSeparator of formatoptions to specify your custom decimal and thousand separators.

Alternatively you can either provide unformatter (unformat function described here) which converts the text displayed in the cell to the number. I think it should work.

One more possibility is usage of custom sorting. You need define sorttype as function which would be return "normalized" version of the data. The "normalized" data will be used instead of original data during sorting. See the answer and this one for code examples.

UPDATE: Free jqGrid provides some standard templates like template: "integer", number: "number", template: "integerStr", number: "numberStr" and other. Thus one can use

{name: "ZMENG_SOR_VRKME", width: 50, template: "numberStr" }

which would be equivalent to the usage of

{name: "ZMENG_SOR_VRKME", width: 50,
    formatter: "number", align: "right", sorttype: "number",
    searchoptions: { sopt: ["eq", "ne", "lt", "le", "gt", "ge"] } }