DataTables - How do I return the value of a specific column for each row

Question

I am trying to loop through a DataTable to pick up the value of two columns, the first contains and ID and the second the amount so I can create an array to be passed to update the ID with the amount on the database. My code returns "undefined". How do I return the value in column 'x' for each row please?

function disperse() {
    memDispTable.rows().every(function() {
        var row = this.data();
        console.log(row[0]);
    //  Create array to send for updating
    });
}

In the DataTable column 0 is:

{data: 'cdId',
visible: false,
searchable: false},

Column 6 (the amount to update) is an input field:

{data: null,
className: "center",
defaultContent: '<input class="ilValue" type="number" min="0" max="99999.99" step=".01" placeholder="0.00">'
},

The result of:

    var row = this.data();
    console.log(row);

Is:

cdFirstName: "Tendon"
cdId: "MTQ5"
cdSurname: "Achilles"
grpId: "MQ=="
section: "Cub"
__proto__:
constructor: ƒ Object()
hasOwnProperty: ƒ hasOwnProperty()
isPrototypeOf: ƒ isPrototypeOf()
propertyIsEnumerable: ƒ propertyIsEnumerable()
toLocaleString: ƒ toLocaleString()
toString: ƒ toString()
valueOf: ƒ valueOf()
__defineGetter__: ƒ __defineGetter__()
__defineSetter__: ƒ __defineSetter__()
__lookupGetter__: ƒ __lookupGetter__()
__lookupSetter__: ƒ __lookupSetter__()
get __proto__: ƒ __proto__()
set __proto__: ƒ __proto__()

I am now iterating through the rows. The first row contains an amount:

However, when I iterate through the Total is returned for a different row:

And when I sort the Total is returned for a different row again:

The Total on the page stays with the correct row when sorted (i.e., Glyn Bartlett).

My code is:

function postDispersements() {
    //Disperse the amounts to each person
    
    //Iterate through each row
    memDispTable.rows().data().each( function ( rowData, index ) {
        
        var scoutNo = rowData.scoutNumber;
        var surname = rowData.cdSurname;
        var firstName = rowData.cdFirstName;
        
        var amtNode = memDispTable.cells(index, 7).nodes().to$()[0];//Total column
        var amt = parseFloat($('input', amtNode ).val());
        
        if (isNaN(amt)) {
          amt = 0.0;
        }
        amt = (Math.floor((amt + Number.EPSILON) * 100) / 100).toFixed(2);
        
        //There are a large number of records so only log the rows with an amount
        if (amt > 0) {
            console.log("scoutNo: " + scoutNo + " surname: " + surname + " firstName: " + firstName + " amt: " + amt);
        }
    });
}

Apologies - I did not read your question carefully when I wrote my answer. Can you show a sample of the source JSON data you are using (including the structure)? And can you show the DataTable definition? I think your source data is provided as an array of objects, not an array of arrays - and that would require a small change to the approach. — andrewJames
– andrewJames, Commented Aug 16, 2020 at 11:20

andrewJames · Accepted Answer · 2020-08-17 13:57:57Z

Revised answer:

Your function for iterating over row data memDispTable.rows().every(function() {...}) looks good.

However, the structure of the data in each row can be either an array:

[ "value 1", "value2", "value3", ... ]

or an object:

{ field1: "value1", field2: "value2", field3: "value3", ... }

If you are not able to access values in the row data using an array index, such as row[0], then that suggests you need to access the data using field names: row.field1.

You can double-check the structure of a row by printing the entire row to the console:

memDispTable.rows().every(function() {
  var row = this.data();
  console.log(row);
});

That will show you the row structure - and, if it's an object, you will also see the field names you need to use.

Whether you have row data as arrays or objects depends on how the data was provided to DataTables in the first place. Typically, it's via JSON data - so it depends on the structure of that JSON.

Revised Approach

Based on the updated info in the question, you have data which looks like the following sample (for two rows of test data):

[
  { "cdFirstName": "Tendon",
    "cdId": "MTQ5",
    "cdSurname": "Achilles",
    "grpId": "MQ==",
    "section": "Cub" 
  },
  { "cdFirstName": "John",
    "cdId": "MTQ6",
    "cdSurname": "Smith",
    "grpId": "MQ==",
    "section": "Cub" 
  }
]

This means you can access values in each row using the following:

memDispTable.rows().every(function() {
  var row = this.data();
  console.log(row.cdId);
});

So, you were very close in your comment this.row.cdId - you just did not need the "this" part.

However, you also want to get the entered money amount from each row - and that requires a different approach. Because the amount is entered manually by a user into a field, that data is not directly visible to DataTables - it's part of the HTML table, but not part of the DataTables object.

We can combine the above row() iterator with the same technique used previously to keep track of the grand total assigned amount, to get what you need.

That would be something like this:

function disperse() {
  memDispTable.rows().data().each( function ( rowData, index ) {
    var amtNode = memDispTable.cells(index, 5).nodes().to$()[0];
    var amt = parseFloat($('input', amtNode ).val());
    if (isNaN(amt)) {
      amt = 0.0;
    } 
    console.log( "ID: " + rowData.cdId + ", amount: " + amt );
  } );
}

This iterates over each table row to get the cdId value - which is in DataTables.

We also use a cells(index, 5) function to get a specific cell (5) in the current row. And then we use the nodes() function to help DataTables get access to the user-entered value in that specific cell.

Putting it all together, we have something like this - which should work as a stand-alone test file, if you want to try it (and then adapt to your specific code):

<!doctype html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <title>Demo</title>
  <script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
  <script src="https://cdn.datatables.net/1.10.21/js/jquery.dataTables.min.js"></script>
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.21/css/jquery.dataTables.min.css">
  <link rel="stylesheet" type="text/css" href="https://datatables.net/media/css/site-examples.css">

  <script src="https://cdnjs.cloudflare.com/ajax/libs/big-integer/1.6.48/BigInteger.min.js"></script>

</head>

<body>

<div style="margin: 20px;">
    
    <div id="showsum">Grand Total: $0.00</div>
    <br><br>

    <table id="demo" class="display dataTable cell-border" style="width:100%">
    </table>

</div>

<script type="text/javascript">

var dataSet = [
  { "cdFirstName": "Tendon",
    "cdId": "MTQ5",
    "cdSurname": "Achilles",
    "grpId": "MQ==",
    "section": "Cub" 
  },
  { "cdFirstName": "John",
    "cdId": "MTQ6",
    "cdSurname": "Smith",
    "grpId": "MQ==",
    "section": "Cub" 
  }
];

var table;

function doSum() {
  var sum = 0.0;
  memDispTable.columns(5).nodes().to$()[0].forEach(function (item) {
    var amt = parseFloat($('input', item ).val());
    if (!isNaN(amt)) {
        sum += amt;
    }
  });
  sum = (Math.round((sum + Number.EPSILON) * 100) / 100).toFixed(2);
  $('#showsum').text("Grand Total: $" + sum);

  disperse();
}

function disperse() {
  memDispTable.rows().data().each( function ( rowData, index ) {
    var amtNode = memDispTable.cells(index, 5).nodes().to$()[0];
    var amt = parseFloat($('input', amtNode ).val());
    if (isNaN(amt)) {
      amt = 0.0;
    } 
    console.log( "ID: " + rowData.cdId + ", amount: " + amt );
  } );
}

  $(document).ready(function() {

    memDispTable = $('#demo').DataTable( {
      "data": dataSet,
      "columns": [
        { title: "ID", "data": "cdId", "visible": false, "searchable": false },
        { title: "First Name", "data": "cdFirstName" },
        { title: "Surname", "data": "cdSurname" },
        { title: "Group", "data": "grpId" },
        { title: "Section", "data": "section" },
        { title: "Amount" }
      ],
      "columnDefs": [ {
        "targets": 5,
        "name": "amt",
        "data": function ( row, type, val, meta ) {
          return '<input type="number" min="0" max="99999.99" step=".01" placeholder="0.00" onchange="doSum()">';
        }
      } ]
    } );

    disperse();

  } );

</script>

</body>
</html>

This demo prints the following to the console:

ID: MTQ5, amount: 0
ID: MTQ6, amount: 0

And as the amounts are changed, the print-out shows the changes:

ID: MTQ5, amount: 1.23
ID: MTQ6, amount: 456.78

Hi Andrew, this returns 'undefined' for me: memDispTable.rows().every(function() { var row0 = this.data()[0]; console.log(row0); var row4 = this.data()[4]; console.log(row4); // Create array to send for updating });
Hi Andrew, Thank you very much for your help and patience. I have added the result of the "this.data()". So I used "var row0 = this.row.cdId;" and this returns "undefined". Also, column 6 is not being displayed.
Based on the additional info (thank you!) I think I have a working approach now. See the updated answer.
Hi Andrew, I have completed all the pre-work to updating each member's records. Now when I iterate through each row the Total column is not being returned for the correct row. An when I sort the Total is applied to a different row in the iteration. The amount is still on the correct row on the page displayed. I will add this to my original question so you can see the output and my actual code.
At first glance, this is because we are mixing two different types of row index: (1) the row index stored by DataTables (which is based on the order in which data was loaded into DataTables); and (2) the display index that the user sees when looking at the table on the page (which can be different due to sorting/filtering). I can take a closer look when I have some time (not sure when), to confirm if this is what is really happening - and maybe I will be able to suggest a fix.

Glyn · Accepted Answer · 2020-08-25 03:05:32Z

Just to make it clear in case someone else is in a similar situation. If your are using:

tablename.rows().data().each( function ( rowData, index ) {

Then, within the loop, do not use:

var surname = rowData.surname;

As the value returned may not be in the same row as:

var amtNode = memDispTable.cells(index, 7).nodes().to$()[0];//Total column
var amt = parseFloat($('input', amtNode ).val());// Returns the value in the

Use this instead:

var surname = memDispTable.cell( index, 3 ).data();

And all values returned will be in the same row.

In full:

function postDispersements() {
//Disperse the amounts to each person

    //Iterate through each row
    memDispTable.rows().data().each( function ( rowData, index ) {
    
        //var surname = rowData.surname; //Do not use this it will return a value; however, may not the the same row as your <input>
        var surname = memDispTable.cell( index, 3 ).data(); // Use this
    
        var amtNode = memDispTable.cells(index, 7).nodes().to$()[0];//Total column
        var amt = parseFloat($('input', amtNode ).val());// Returns the value in the <input>
    
    });
}

Collectives™ on Stack Overflow

DataTables - How do I return the value of a specific column for each row

2 Answers 2

7 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Related