WordPress.org

Ready to get started?Download WordPress

Forums

TablePress
[resolved] Sorting of numbers doesnt work (17 posts)

  1. peterkro
    Member
    Posted 9 months ago #

    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/

  2. TobiasBg
    Member
    Plugin Author

    Posted 9 months ago #

    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

  3. peterkro
    Member
    Posted 9 months ago #

    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

  4. TobiasBg
    Member
    Plugin Author

    Posted 9 months ago #

    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

  5. peterkro
    Member
    Posted 9 months ago #

    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

  6. peterkro
    Member
    Posted 9 months ago #

    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

  7. TobiasBg
    Member
    Plugin Author

    Posted 9 months ago #

    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

  8. peterkro
    Member
    Posted 9 months ago #

    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

  9. TobiasBg
    Member
    Plugin Author

    Posted 9 months ago #

    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

  10. peterkro
    Member
    Posted 9 months ago #

    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

  11. TobiasBg
    Member
    Plugin Author

    Posted 9 months ago #

    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

  12. peterkro
    Member
    Posted 9 months ago #

    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

  13. TobiasBg
    Member
    Plugin Author

    Posted 9 months ago #

    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

  14. peterkro
    Member
    Posted 9 months ago #

    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

  15. TobiasBg
    Member
    Plugin Author

    Posted 9 months ago #

    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

  16. peterkro
    Member
    Posted 9 months ago #

    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?

  17. TobiasBg
    Member
    Plugin Author

    Posted 9 months ago #

    Hi,

    no problem, you are very welcome! :-) Good to hear that this helped!

    Adding formatting options to the plugin is not that easy, as it's not very flexible and only makes the user interface harder to use :-( But I do have some ideas here :-)

    Best wishes,
    Tobias

    P.S.: In case you haven't, please rate TablePress here in the plugin directory. Thanks!

Reply

You must log in to post.

About this Plugin

About this Topic