Sort a custom formatted column in jqgrid

Question

My jqgrid has an amount column which has custom formatting applied and the result can be the amount itself, or special characters (for eg: *, "", NA, image). All the special characters are needed as it describes the kind of amount. (for eg: * means user is not authorized to see the amount)

My challenge is how do I sort this.

Below is my column model for the amount column.

[name: 'amount', index: 'amount', type: "String", align: "right", sorttype: "float", title: false, formatter : someCustomFormatterFunction]

For example: Suppose my amount data was something like this [100, 180, 120, 130, 160] However after custom formatting this changed to [100, *, image, 130, NA] My sorttype is "float". Because requirements was initially for amounts only. But updated requirements introduced these special characters.

Now, on sorting, i should see all the special characters together and numbers together. Something like [100, 130, *, image, NA] or [*, image, NA, 100, 130] for ascending order.

But, I am getting it as [100, image, 130, NA, *]. I can understand why I am getting like this.

How can I achieve sorting in a proper way.

Also, next step would be to sort the special characters also in a specified order. All my thoughts are taking me to implement some ugly big logic. Is there any ideas on how to implement this.

Thanks, Sam

Answer 1

If you use custom formatting and place the text like *, image, NA, 100, 130 in the column you will be unable to use sorttype is "float".

I would recommend you to use custom sorting instead. What you need is just to define sorttype as function. In the case you can replace the data to another value which will be used during sorting instead of the original one. So you can for example replace values "*", "image", "NA", 100, 130 to "*", "image", "NA", "000100", "000130" (all values are strings now). In the case the data sill be successful sorted as strings. Alternatively you can even use original numbers 100, 180, 120, 130, 160. The function sorttype get obj as the second parameter and the obj represent full data for the row. If you hold the original value then you can use it for sorting. You don't posted any code which shows which kind of jqGrid you use, and how you fill it so I can't get you more details now.

See the answer and this one for more details and code examples about custom sorting.