Sorting of numbers doesnt work

Hi Peter,

thanks for your post, and sorry for the trouble.

The problem here is that your numbers are not actually numbers, at least from a technical point of view. The “.” as a thousand delimiter makes them strings, and strings are simply sorted differently that numbers. (In fact, the “.” is interpreted as a comma, as the internal JS library that TablePress uses, only deals with the English number format by default).
To change this, please take a look at this TablePress Extension that can be used to change the used sorting algorithm: https://tablepress.org/extensions/datatables-sorting-plugins/

Regards,
Tobias

Thx very much for your fast answer. I really appreciate that. I installed this additonal plugin and inserted a line of code in the table:

“aoColumnDefs”: [ { “sType”: “numeric-comma”, “aTargets”: [ 2, 3, 4, 5 ] } ]

As far as I understand now columns 3 to 6 should be sorted correctly (all containing the same kind of comma-numbers with a “.” as a thousand delimiter. But it did only work in one column which was column number 4. This seems to be really tricky. <g>

Do you have any idea what could be wrong?

Regards, Peter

Hi Peter,

yes, that is correct. Your code should make columns 3 to 6 sortable – if they contain only numbers, and no other strings.
Can you maybe post a link to the page with the table, so that I can take a direct look? Thanks!

Regards,
Tobias

Hi Tobias,

I′ve published this page now but made it accessable only with a password which is “tobias”. No secrets on that page but it′s not really ready for publishing. Thank you very much for your efforts!

Regards, Peter

Ooops, again with link in correct way:

Hi Tobias,

I′ve published this page now but made it accessable only with a password which is “tobias”. No secrets on that page but it′s not really ready for publishing. Thank you very much for your efforts!

Link to page

Regards, Peter

Hi,

thanks for the link!

As it turns out, the “numeric-comma” type is not sufficient in this case, as that only changes the “,” to a “.” internally, but does nothing about the existing “.”. For that, you’ll need to use the “formatted-num” algorithm, so please try again with this “Custom Command”:

"aoColumnDefs": [ { "sType": "formatted-num", "aTargets": [ 2, 3, 4, 5 ] } ]

Regards,
Tobias

Thank you for your answer. I tried this command and its now included in the table but doesnt work. No change in behavior. To be sure: Do I have to exchange this command for the one before or do I have to add it – so that both commands are in the line?

Regards, Peter

Hi Peter,

first: Replacing the old command with the new one is correct.

However, I did a mistake here, as the “formatted-num” sort type is also not what we want. That only removes all characters from a string that do not belong to a number — but the “.” does belong to a number (in the English format), as it resembles the comma.
So, we’ll actually need to go back to the “numeric-comma” format, but we’ll also need to extend the actual sorting code…
For that, please open the file “/wp-content/plugins/tablepress-datatables-sorting-plugins/jquery.datatables.sorting-plugins.js” and replace the line

a = ( a == "-" ) ? 0 : a.replace( /,/, "." );

with

a = ( a == "-" ) ? 0 : a.replace( /\./, "" )replace( /,/, "." );

Then, copy the contents of the modified file into the file “/wp-content/plugins/tablepress-datatables-sorting-plugins/jquery.datatables.sorting-plugins.min.js” (if you want, you can run them trough a minifier like https://refresh-sf.com/yui/ ).

After that, change the “Custom Commands” back to the first one that you tried. Sorry again for this confusion ??

Regards,
Tobias

Thx again. I did this. The result is that the table cannot be sorted any longer. When I remove the custom command (where I entered
“aoColumnDefs”: [ { “sType”: “numeric-comma”, “aTargets”: [ 2, 3, 4, 5 ] } ]
then it can be sorted – but with the well known problems of wrong sorting order that brought me to the forum.

I really do not want to waste your time. But I cant imagene that there is no way to do a sort with a (from a european perspective) very normal way of formatting numbers.

I tried to run the file through this minifier, but it delivered error messages so I uploaded the uncompressed file to my server. Could this be a problem?

Regards, Peter

Hi Peter,

argh, I made a typo in my code… So sorry…
The new line should be

a = ( a == "-" ) ? 0 : a.replace( /\./, "" ).replace( /,/, "." );

(with a “.” before the second replace call).
Please try again with that.
The minifier should then work as well, without showing errors.

Regards,
Tobias

Ok, no problem. I corrected this now. Result: Table can be sorted – but sort order remains incorrect as before. The 4th column (which is the 2nd with numbers) sorts correctly (as it did several tries before, so nothing new). Could we learn something from this? Meaning: What is different in this column than in the others?

Regards, Peter

Hi Peter,

ah, one more thing ?? Right now, the code only removes the first “.”, but some cells have two “.” in them. Thus, we need to do a “global” search and replace. Please replace

a = ( a == "-" ) ? 0 : a.replace( /\./, "" ).replace( /,/, "." );

with

a = ( a == "-" ) ? 0 : a.replace( /\./g, "" ).replace( /,/, "." );

(note the added “g”).

Regards,
Tobias

Genius! Now everything seems to work properly. Thank you very, very much for spending so much time on that issue! Two – I hope – last questions:

1. How can I get a right-aligned-column? (which seems to be much better for numbers, now it is by default left aligned)

2. In case of an update of the plugin: Do I have to backup these two files before so that these alterations are not overwritten?

Regards, Peter

Hi Peter,

awesome! ?? Great to hear that this works now!

To right align the columns, just add this to the “Custom CSS” textarea on the “Plugin Options” screen:

.tablepress-id-7 .column-3,
.tablepress-id-7 .column-4,
.tablepress-id-7 .column-5,
.tablepress-id-7 .column-6 {
  text-align: right;
}

As this is a TablePress Extension, it will not be affected or overwritten by a regular TablePress update (they are independent plugins). Of course, a backup can never harm.

Regards,
Tobias

Thank you very, very much for taking so much time in helping me. I really appreciate that. Everything′s fine now and I will start to use your excellent plugin on a regular basis. Have a nice day!

Regards, Peter

P.S.: How about adding these formatting possibilites to the settings of the plugin?