Replies

• rf1234 Posts: 2,951Questions: 87Answers: 416

  February 2023 edited February 2023

  Greetings to Brazil! Germany has the exact same number format than you guys have across the Atlantic Ocean: Decimal commas and periods as thousand separators.

  Run this code before Data Table initialization.

  //sorting of formatted numbers in English and German format
  $.extend( $.fn.dataTable.ext.type.order, { 
      "formatted-num-pre": function ( a ) {
          if (lang == 'de') {
              a = a.toString().replace( /[\.]/g, "" );
              a = a.toString().replace( /[\,]/g, "." );
          } else {
              a = a.toString().replace( /[\,]/g, "" );
          }
          a = a.toString().replace( /[^\d.-]/g, "" );
          a = parseFloat(a);
          if ( ! isNaN(a) ) {
              return a;
          } else {
  //14 digit negative number to make sure empty cells always stay at the bottom / top
              return -99999999999999;
          }
      },
      "formatted-num-asc": function ( a, b ) {
              return a - b;
      },
      "formatted-num-desc": function ( a, b ) {
              return b - a;
      }
  } );
  

  The code above works for English and German or Portuguese, also in case you use currency codes in the column. In case you only have Portuguese as user language just get rid of the English stuff and reuse everything for "lang === 'de'"

  Like in here:

  //sorting of formatted numbers in German / Portuguese format
  $.extend( $.fn.dataTable.ext.type.order, { 
      "formatted-num-pre": function ( a ) {
          a = a.toString().replace( /[\.]/g, "" );
          a = a.toString().replace( /[\,]/g, "." );
          //get rid of text e.g. currency codes
          a = a.toString().replace( /[^\d.-]/g, "" );
          a = parseFloat(a);
          if ( ! isNaN(a) ) {
              return a;
          } else {
  //14 digit negative number to make sure empty cells always stay at the bottom / top
              return -99999999999999;
          }
      },
      "formatted-num-asc": function ( a, b ) {
              return a - b;
      },
      "formatted-num-desc": function ( a, b ) {
              return b - a;
      }
  } );
  

  If you are not using auto-detection you would still need your columnDef like this:

  columnDefs: [
         { type: 'formatted-num', targets: 4 }
       ]
  

  My code above is based on this plugin that @allan wrote some time ago. Allan's plugin only works for English numbers, not for yours.
  https://datatables.net/plug-ins/sorting/formatted-numbers

  Please also see this comment in the plugin description:
  

  I guess that comment only refers to English numbers though. So you will need something for Portuguese. Let me try ...

  Ok, just ran a data table without my modified plugin code: Sorting didn't work in English and German. It should have worked in English but since I use formatted numbers with currency codes (EUR, USD, GBP, etc.) you will still need a plugin, also for English.

  So this is proper ordering in German and English with empty cells at the top / bottom:
  English:
  

  German / Portuguese:
  
• rf1234 Posts: 2,951Questions: 87Answers: 416

  February 2023 edited February 2023

  If you want to use auto detection of formatted numbers it gets a bit complicated. Here is my code that detects formatted numbers in English and German / Portuguese format that contain a currency code from a list that is defined in an array. Maybe there is a simpler way for auto-detection but I guess it is not available for my particular use case involving numbers with currency codes. You will notice that auto-column type detection can be conflicting. Hence I integrated something to make sure I don't get in conflict with date columns for German dates (28.2.2023 as opposed to English 28/2/2023 and American 2/28/2023).

  var cur = [  'EUR', 'USD', 'GBP', 'CHF', 'JPY', 'AUD', 'CAD', 'CNY', 
               'DKK', 'SEK', 'NOK', 'CZK', 'PLN', 'HUF', 'RUB'         ];
  
  //auto detection only works if ALL cells of a column comply with the criterion !!!
  //fields must contain an ISO currency code or space to be detected as formatted-numbers
  //since all table fields with ISO codes always contain a number this works.
  //in addition German number fields should be detected as formatted-num regardless of
  //whether they contain an ISO code or not
  $.fn.dataTable.ext.type.detect.unshift( function ( data ) {
  //        if (typeof data !== 'undefined' && data !== null) {
  //            if ( isNaN(data) ) {
      if (typeof data !== 'undefined') {
          if ( data != null )  {
              var i=0;
              while ( cur[i] ) {
                  if ( isNaN(data) ) {  //search may only be performed on non-numeric fields
                      if ( data.search( cur[i] ) > -1 )   {
                          return 'formatted-num';
                      }
                  } else { //for pure numbers we want the same sorting as well!!
                      return 'formatted-num';
                  }
                  i++;
              }
              if ( data === '') {
                  return 'formatted-num';
              }                
              if (lang == 'de') {
  //we are not using strict mode in moment.js so that 28.12.2017 09:42 is also
  //recognized as a date!!
                  if ( ! moment(data, 'L').isValid() ) {
                      if ( isNaN(data) ) {
                          data = data.replace( /[\.]/g, "" );
                          data = data.replace( /[\,]/g, "." );
                          if ( ! isNaN(data) ) {
                              return 'formatted-num';
                          }
                      }
                  }
              }                    
          }
      }
      return null;
  } );
  
• Horeom Posts: 3Questions: 1Answers: 0

  February 2023

  Thank you! The formatted number option worked perfectly in all the tables I had, thank you very much!