Get a value of a cell in last row - not working getting "undefined" result

raphiw90

Hi all,

I use a DataTables table to display a list of outdoor tours with columns such as Tour Reference, Name, Start Date, End Date etc.

Now I would like to to get the value of the End Date of the last row. I have tried to approach it with the row().data() approach as stated here in this forum article: https://datatables.net/forums/discussion/58285/finding-a-value-of-a-cell-of-the-last-row

Although I don't get this working. My console output always ends up with "undefined".

When I try to get the desired End Date value I can get it easily with the row().data() function when I click on it as described in the example here: https://datatables.net/reference/api/row().data()

However I would like that the End Date is fetched automatically without a user interaction, as I would like to store it in a variable later so that I can use it as a defined value for the editor instance.

I have also used the debugger and I do not get any errors. The data is processed client-side and is AJAX sourced via a MySQL query from my database. Also understood the difference between "dataTables" and "DataTables". I have also found on some posts on SO and here on the forum that the order of the scripts (when they are loaded) have an impact but I have the correct order.

Any ideas how to resolve this?

Thanks,
Raphael

My Javascript code:

(function($){
    
$(document).ready(function() {

    var editor = new $.fn.dataTable.Editor( {
        ajax: 'assets/php/table.tours.php',
        table: '#tours',
        fields: [
            {
                "label": "Reference:",
                "name": "tours.tour_ref"
            },
            {
                "label": "Supplier:",
                "name": "tours.supplier_id",
                "type": "select",
                "placeholder": "Select a supplier"
            },
            {
                "label": "Tour Name:",
                "name": "tours.tour_name"
            },
            {
                "label": "Start Date:",
                "name": "tours.tour_start",
                "type": "datetime",
                "format": "DD\/MM\/YY HH:mm"
            },
                        {
                "label": "End Date",
                "name": "tours.tour_end",
                "type": "datetime",
                "format": "DD\/MM\/YY HH:mm"
            },
            {
                "label": "Max. Space:",
                "name": "tours.tour_maxspace"
            },
            {
                "label": "Guide:",
                "name": "tours.guide_id",
                "type": "select",
                "placeholder": "Select a guide"
            }
        ]
    } );
    var table = $('#tours').DataTable( {
        dom: 'Bfrtip',
        ajax: {
            url: "assets/php/table.tours.php",
            type: 'POST'
        },
        columns: [
            {
                "data": "tours.tour_ref"
            },
            {
                "data": "suppliers.name"
            },
            {
                "data": "tours.tour_name"
            },
            {
                "data": "tours.tour_start"
            },
            {
                "data": "tours.tour_end"
            },
            {
                "data": null,
                "render": function(data, type,row)
                {
                    var start = moment(data.tours.tour_start, "DD/MM/YY hh:ii:ss");
                    var end = moment(data.tours.tour_end, "DD/MM/YY hh:ii:ss");
                    return end.diff(start, 'days')+1+' days';
                }
            },
            {
                "data": null,
                "render": function(data,type,row)
                {
                    return data.tours.tour_maxspace+' Pers.'
                }
            },
            {
                "data": "accounts.username",
                
            }
        ],
        select: true,
        colReorder: true,
        buttons: [
            { extend: 'create', editor: editor },
            { extend: 'edit',   editor: editor },
            { extend: 'remove', editor: editor },
            {
                extend: 'collection',
                text: 'Export',
                buttons: [
                    'copy',
                    'excel',
                    'csv',
                    'pdf',
                    'print'
                ]
            }
        ]
        
    } );
    // Working Example
    $('#tours tbody').on( 'click', 'tr', function () {
    var test = table.row( this ).data();
    console.log(test.tours.tour_end);});
    // Working Example End
    var test2 = table.row(':last').data();
    console.log(test2);
    
} );

}(jQuery));

My PHP script:

<?php

// DataTables PHP library and database connection
include( "lib/DataTables.php" );

// Editor Classes
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

// Editor Instance
Editor::inst( $db, 'tours', 'tour_id' )
    ->fields(
        Field::inst( 'tours.tour_ref' )
            ->validator( Validate::notEmpty() ),
        Field::inst( 'tours.supplier_id' )
            ->options( Options::inst()
                ->table( 'suppliers' )
                ->value( 'id' )
                ->label( 'name' )
            )
            ->validator( Validate::dbValues() )
            ->validator( Validate::notEmpty() ),
        Field::inst( 'suppliers.name' ),
    
        Field::inst( 'tours.tour_name' )
            ->validator( Validate::notEmpty() ),
        Field::inst( 'tours.tour_start' )
            ->validator( Validate::dateFormat( 'd/m/y H:i' ) )
            ->getFormatter( Format::datetime( 'Y-m-d H:i:s', 'd/m/y H:i' ) )
            ->setFormatter( Format::datetime( 'd/m/y H:i', 'Y-m-d H:i:s' ) )
            ->validator( Validate::notEmpty() ),
        Field::inst( 'tours.tour_end' )
            ->validator( Validate::dateFormat( 'd/m/y H:i' ) )
            ->getFormatter( Format::datetime( 'Y-m-d H:i:s', 'd/m/y H:i' ) )
            ->setFormatter( Format::datetime( 'd/m/y H:i', 'Y-m-d H:i:s' ) )
            ->validator( Validate::notEmpty() ),
        Field::inst( 'tours.tour_maxspace' )
            ->validator( Validate::notEmpty() ),
        Field::inst( 'tours.guide_id')
            ->options( Options::inst()
                ->table( 'accounts' )
                ->value( 'id' )
                ->label( 'username' )
            )
            ->validator( Validate::dbValues() ),
        Field::inst( 'accounts.username' )
    )
    ->leftJoin( 'accounts',     'accounts.id',          '=', 'tours.guide_id' )
    ->leftJoin( 'suppliers',    'suppliers.id',         '=', 'tours.supplier_id' )
    ->process( $_POST )
    ->json();

kthorngren

I'm confused with your problem description. Sounds like you are having problems with this section of code:

    $('#tours tbody').on( 'click', 'tr', function () {
    var test = table.row( this ).data();
    console.log(test.tours.tour_end);});
    // Working Example End
    var test2 = table.row(':last').data();
    console.log(test2);
     
} );

your comments in the code seem to indicate that it works but your description indicates that table.row(':last').data(); results in undefined. I put your code snippet here and it works:
http://live.datatables.net/wijejifu/1/edit

Please provide a link to your page or a test case replicating the issue so we can help debug.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

raphiw90

Hi Kevin

Thank you very much for your message. I'm very sorry that I haven't provided a test case via jsBin or similar as the problem described does not appear on the test platform.

The thing is: whenever I fetch the data with something like clicking a button or so then I get the correct data returned, however I would like to store the data in a variable at the end without having to click a row or a button. Thats why I up the commented "working example in" and below my approach to fetch the data once upon the DOM is loaded, which I guess is correct where I have put it because its still in the js $(document).ready( function()

You can test the case live on my site here: https://happytracks.ch/app/tours.php

credentials: testguide / testguide

the following errors are produced in the console with the following code:

var test2 = table.row( ':last' ).data();
console.log(test2);

Console output:

"undefined"

var test2 = table.row( ':last' ).data();
console.log(test2.tours.tour_end);

Console output:

"jQuery.Deferred exception: undefined is not an object (evaluating 'test2.tours')"
"TypeError: undefined is not an object (evaluating 'test2.tours')"

kthorngren

the following errors are produced in the console with the following code:

I don't see that error. Please provide the steps needed to generate the error.

whenever I fetch the data with something like clicking a button or so then I get the correct data returned, however I would like to store the data in a variable at the end without having to click a row or a button.

Since you are using ajax (async process) to fetch the data you will need to use initComplete to get the row data. Otherwise you are trying to get the data before the table is complete. Something like this:

initComplete: function () {
  var api = this.api();
  var test2 = api.row( ':last' ).data();
  console.log(test2.tours.tour_end);
},

If you still need help then please provide more information and steps to show the problem in your test case.

Kevin

raphiw90

Thank you very much Kevin for your patience and for answering my question.

It worked with initComplete. I wasn't aware that my code was trying to fetch the data before the table was fully loaded.

All the best,
Raphael