render a number to string

jobloggs

hi all, I'm just learning so be nice.

I have a tinyint field type that I want to display as a string, i think.

in the database we store yes/no as "0" or '1", in the datatable, but in datatables I want to display it as Yes or No. In the form itself I'm displaying a checkbox which is working well.

I've found a few examples but are stuck on where to place

http://datatables.cullis.com.au/examples/advanced/testdeepObjects.html

jobloggs

ok, I've found the solution in "getFormatter" which is return Yes / No in datatables, however the editor is no updating the datatables.

here is the code I have, note the commented out parts I've tried.

...

Editor::inst( $db, 'tblProducts' )
->field(
Field::inst( 'tblProducts.partNo' ),
Field::inst( 'tblProducts.productName' ),
Field::inst( 'tblProducts.description' ),
Field::inst( 'tblProducts.categoryID_FK' )
->options( 'tblCategories', 'id', 'categoryName' ),
Field::inst( 'tblCategories.categoryName' ),

    Field::inst( 'tblProducts.supplierID_FK' )
        ->options( 'tblSuppliers', 'id', 'supplierName' ),
    Field::inst( 'tblSuppliers.supplierName' ),

    Field::inst( 'tblProducts.unitPrice'),
    Field::inst( 'tblProducts.discontinued' )
        ->setFormatter( function ($val, $data, $field) {
                        //if ($val == "Yes") return 0;
                        //else return 1;
                        return ! $val ? 0 : 1;
                    } )
        ->getFormatter( function ($val, $data, $field) {
                    if ($val == 0) return "Yes";
                    else return "No";
        } )
)
->leftJoin( 'tblCategories', 'tblCategories.id', '=', 'tblProducts.categoryID_FK' )
->leftJoin( 'tblSuppliers', 'tblSuppliers.id', '=', 'tblProducts.supplierID_FK' )
->process($_POST)
->json();

...

glenderson

I think that a rowCallback could solve this issue.

, "rowCallback": function(row, data, index) {
  var cellValue = data["... name of the column ..."];
  var output ="No";
  if (cellValue="-1" || cellValue="1" || cellValue="true") {
    output="Yes";
  }
  $("td:eq(column #)",row).html(output);
}

jobloggs

hi, thanks for giving you time, unfortunately it didn't work.

datatables isn't displaying now. I've updated the JavaScript but something is not right, if someone can take a look at the source of this page http://datatables.cullis.com.au/examples/advanced/testdeepObjects.html you may see the error.

I tried with your script then modifying that to other examples to see if it would work.

glenderson

Sorry, the if statement should have double == signs.

if (cellValue=="1" || cellValue=="-1" || cellValue=="true") {

allan

Hi,

For this use case I would actually recommend using columns.render - for example:

data: 'tblProducts.discontinued',
render: function ( data, type, row ) {
  return data == 1 ? 'Yes' : 'No';
}

The reason for suggesting this over a get / set formatter is that this method retains the original value so Editor can edit that. There is then no need for the get / set formatter - only the output in DataTables is being rendered.

Likewise the advantage over rowCallback is that row callback can be a little slow since it is directly manipulating the DOM. Also you wouldn't be able to filter on the string "Yes" or "No" in the table.

Allan

jobloggs

thanks Alan and glenderson, you put me on the right track and finally got what I wanted.

...

{ data: "tblProducts.discontinued", render: function ( data, type, row ) {return data == 0 ? 'Yes' : 'No'; }
}
...

It sorts beautifully and updates finally :)