Answers

• allan Posts: 61,930Questions: 1Answers: 10,154 Site admin

  February 2017

  Those parameters don't effect the data that is displayed in the table - they are used primarily for the table information output, but also for the sorting information.

  Use the number renderer if you want to change the format of the number that it output by SQL.

  Allan
• rf1234 Posts: 2,809Questions: 85Answers: 406

  February 2017 edited February 2017

  Unfortunately I couldn't get the number renderer to work because I am already using something else for rendering:

  {   data: null,
      render: function ( data, type, row ) {
             if (lang == 'de') {
                 return row.rfp.amount + ' ' + row.rfp.currency;
              } else {
                  return row.rfp.currency + ' ' + row.rfp.amount;
              }
        }    
   },
  

  I have no idea how to integrate the number helper into this. I have multiple currencies and depending on the language the currency needs to be displayed before or after the amount. Do you have an idea how to resolve this?

  I fixed it on the server side with PHP like this:

  Editor::inst( $db, 'rfp' )
  ->field(
  Field::inst( 'rfp.amount' )
  //->validator( 'Validate::numeric', array('required' => true, 'message' => $msg[0]) )
  ->getFormatter( function($val, $data, $opts) {
      if ($_SESSION['lang'] === 'de') {     
          return number_format($val, 2, ',', '.');
      } else {
          return number_format($val, 2);
      }
  })
  ->setFormatter( function($val, $data, $opts) {
      //explode to get rid of the 1,000 separators
      if ($_SESSION['lang'] === 'de') {     
          $numberArray = explode('.', $val);
      } else {
          $numberArray = explode(',', $val);
      }
      //implode without delimiter to join the pieces again
      $numberString = implode($numberArray);
      //replace the German decimal comma with a period
      if ($_SESSION['lang'] === 'de') {   
          $numberString = str_replace(',', '.', $numberString);
      }
      return $numberString;
  })
  

  MySql accepts this without any issue as long as the German 50.000.000,97 or the English 50,000,000.97 are transformed into 50000000.97. (The MySql field format is DECIMAL(15,2)).

  Another issue is that validator does not work very well in this environment either. It rejects German numbers like -50.000.000,99 because it thinks they are not numeric.
  Any idea? Many thanks in advance.
• allan Posts: 61,930Questions: 1Answers: 10,154 Site admin

  February 2017 Answer ✓

  I have no idea how to integrate the number helper into this.

  You can make use of the number renderer built into DataTables directly by using:

  $.fn.dataTable.render.number( .... ).display( numberToRender );
  

  It might be easier to do something like:

  var numberRenderer = $.fn.dataTable.render.number( .... ).display;
  

  and then simply use:

  numberRenderer( numberToRender );
  

  whenever you want to render a number.

  Another issue is that validator does not work very well in this environment either. It rejects German numbers like -50.000.000,99 because it thinks they are not numeric.

  The numeric validator has a decimal option which you can use to tell it what to expect for the decimal place. You'd need to dynamically switch that based on the user's session language.

  Allan
• rf1234 Posts: 2,809Questions: 85Answers: 406

  February 2017

  Thank you, Allan! You are doing a great job and your product is awesome!