WordPress.org

Support

Support » Plugins and Hacks » TablePress » [Resolved] Sorting of numbers doesnt work

[Resolved] Sorting of numbers doesnt work

  • I have a simple table which I imported via .csv file. There are several columns with numbers – but TablePress doesnt sort them correctly if I activate the Javascript functions so that users can decide which column they want to sort. It seems that it does sort the column alphabetically.

    This would be the ascending order:
    1.000.000
    200.000
    30
    4.000.000

    All other Javascript functions work correctly.
    I found a topic in the forum where you suggested to check all cells if they contain something else than numbers. I did this already but couldnt find anything.

    The plugin is really great – but without the sorting function it is useless for me.

    Do you have any idea what the problem is?
    Thx, Peter

    http://wordpress.org/plugins/tablepress/

Viewing 15 replies - 1 through 15 (of 16 total)
  • Plugin Author TobiasBg

    @tobiasbg

    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: http://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

    Plugin Author TobiasBg

    @tobiasbg

    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

    Plugin Author TobiasBg

    @tobiasbg

    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

    Plugin Author TobiasBg

    @tobiasbg

    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 http://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

    Plugin Author TobiasBg

    @tobiasbg

    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

    Plugin Author TobiasBg

    @tobiasbg

    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

    Plugin Author TobiasBg

    @tobiasbg

    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?

Viewing 15 replies - 1 through 15 (of 16 total)
  • The topic ‘[Resolved] Sorting of numbers doesnt work’ is closed to new replies.
Skip to toolbar