Table Clear and Draw choose what columns to draw and clear

martin1223345

I dont know how to make a test case for this, so sorry.

I found the folowing script that I now use to auto reload my table. But I have a few elements in my table that I dont want to refres. Those are in column 4 and 20 of my table. In here I have a class added to last clicked buttons and a input field stored in a dropdown. See below the code. I am searching for a way to keep those elements as if no refresh has taken place. Is this possible? Maybe the same way the selected items are retained? Please help I cant find anything related to this problem.

function getData() {
    $.ajax({
      url: "DBScriptLoadAccounts.php",
      success: function (data) {

        // Parse JSON string.
        data = JSON.parse(data);

        // Get Datatable API
        var table = $('#AccountsTable').DataTable();

        // Get IDs of selected rows.
        var selected = table.rows({selected: true}).ids().toArray();

        // Add '#' to the IDs of each row
        selected.forEach(function(part, index) {
          this[index] = '#' + this[index];
        }, selected);

        //console.log(selected)

        // Clear table.
        table.clear();

        // Add new row data, draw and stay on same page.
        table.rows.add(data.data).draw(false);

        // Reselect rows absed on ID.
        table.rows( selected ).select();
      }
    });
}

The columns I don't want to refresh/reload after the table is refreshed. or in other words, where the data must be retained.

            Column 4
            { 'data': null, title: '', wrap: true, "render": function (item) { return '<ol class="loginicon"><li aria-label="login" name = ' + item.id + ' class="fas fa-external-link-alt mobdetective LoginOnAccount" ></li></ol>' } },

            Column 20
            { 'data': null, title: '', wrap: true, "render": function (item) { return '<div class="dropleft"><i class="fas fa-user-secret mobdetective" id="dropdownMenuButton' + item.id + '" data-bs-toggle="dropdown" aria-expanded="false"></i><span class="caret"></span><ul class="dropdown-menu checkbox-menu allow-focus" aria-labelledby="dropdownMenuButton' + item.id + '"><li role="presentation" class="dropdown-header">Detectives</li><li role="presentation" class="divider"></li><li ><input type="text" name="Searchname" class="form-control Searchname" placeholder="Accountname"></li><li ><label><input type="checkbox" value="United States"> United States</label></li><li ><label><input type="checkbox" value="Netherlands"> Netherlands</label></li><li ><label><input type="checkbox" value="Italy"> Italy</label></li><li ><label><input type="checkbox" value="China"> China</label></li><li ><label><input type="checkbox" value="Great Britain"> Great Britain</label></li><li role="presentation" class="divider"></li><li role="presentation"><a role="menuitem" tabindex="-1" class="btn btn-outline-dark searchtarget" type="button">Search (80x)</a></li></ul></div>' } },

colin

One thing you could do, is before your call to

table.clear();

call rows().data() to get the existing data. Then, merge those columns in to the data you got back from the ajax call,

Colin

martin1223345

I am no expert in javascript, jquery. Do you have a example on how to do this? How do merge those columns?

martin1223345

I am trying to make sense of the documentation to keep those columns data stored and to use it as redraw. But i don’t understand it.

kthorngren

Here is an example:
http://live.datatables.net/jezolecu/1/edit

It shows two ways of updating the table data. It also shows how the render function might affect the buttons. If using row().data() to update the data the render function runs for the display, type and filter operations - which might affect columns 4 and 20. While with cell().data() it only runs for the filter operation and shouldn't affect these fields.

I added a class clicked to show how it might affect the button. The class is added initially. And also added in each click event. This shows the row().data() will cause the input to be overwritten. When using the cell().data() technique the input columns shouldn't be affected.

Kevin

martin1223345

Thanks for your reaction ! But these classes are added when one clicks, so i need that state saved after the refresh of the table. So I need to save all that was present in those columns including the clicked class and the dropdown with all its inputs etc. Is it posible to just exclude those from the redraw? or better yet ,chose what to remove from table and what to redraw?

martin1223345

Trying to implent it in my script. But I get errors..

        $(document).ready(function() {
              function getData() {
    $.ajax({
      url: "DBScriptLoadAccounts.php",
      success: function (data) {

        // Parse JSON string.
        data = JSON.parse(data);
        var update = JSON.parse(data);
        // Get Datatable API
        var table = $('#AccountsTable').DataTable();

        // Get IDs of selected rows.
        var selected = table.rows({selected: true}).ids().toArray();

        // Add '#' to the IDs of each row
        selected.forEach(function(part, index) {
          this[index] = '#' + this[index];
        }, selected);

        update.forEach(function ( item ) {

// Get the id
var id = item.id;
var rowId = '#' + id;

// Get the current row using the ID
var row = table.row('#' + id);

// Update the row data

table.cell( rowId, 9 ).data( item.notes )
table.cell( rowId, 10 ).data( item.ranker )

});

        //console.log(selected)

        // Clear table.
        table.clear();

        // Add new row data, draw and stay on same page.
        table.rows.add(data.data).draw(false);

        // Reselect rows absed on ID.
        table.rows( selected ).select();
      }
    });
}

martin1223345

Basicly all I need is to have as stated above. A call to my ajax php, and update only a few columns, not all are needed. Is there a way to add such code in my script? What or where should I look. Because if I can just choose what columns need new information I assume that those others remain untouched. That way I am also helped. And if that is needed with cell update, that is fine for me to. But I am stuck here..

kthorngren

But these classes are added when one clicks

Understood. My example replicates this by adding a class to each button before updating the table. I just didn't take the time to create a click event.

. Is it posible to just exclude those from the redraw?

Sort of. Datatalbes renders the cell data for various functions like display, filter, sort, etc. These are explained in the orthogonal data docs. My example show that you can use cell().data() to update the columns you want and it doesn't update the display (class) of the button. Please look closer at the two examples I provided. I think the cell).data() example is what you are asking for. Let us know if you have any questions.

Try replacing this code:

        // Clear table.
        table.clear();
 
        // Add new row data, draw and stay on same page.
        table.rows.add(data.data).draw(false);

With loop in the cell().data() example.

Kevin

martin1223345

I will try. I indeed just need to refresh data on some columns. How do I use the old data as the select retain script does to just update the cells? I am a bit confused and lack the knowledge to get this working. I am sorry for asking

kthorngren

I made some assumptions that you are using columns.data and your data is object based. Also that you have rowId set to the id object. Maybe you can post your Datatables init code so we can see what you have.

Trying to implent it in my script. But I get errors

What are the errors?

You will want to remove these lines:

 
        // Clear table.
        table.clear();
 
        // Add new row data, draw and stay on same page.
        table.rows.add(data.data).draw(false);

update.forEach(function ( item ) {

update is the array of data. In your case it looks like data.data. That statement should look like this:

 data.data.forEach(function ( item ) {

Kevin

kthorngren

How do I use the old data as the select retain script does to just update the cells?

Just to clarify. The example is using the row-selector as a string id. Using rowId sets the ID of row, ie, #my_id. This is selecting the row based on the ID.

var row = table.row('#' + id);

It can be changed to this to use the variable rowId created with var rowId = '#' + id;:

var row = table.row(' rowId );

Kevin

martin1223345

Thank you verry much for your help and explenation! Works like a charm. I use it now like this.

 function getData() {
    $.ajax({
      url: "DBScriptLoadAccounts.php",
      success: function (data) {

        // Parse JSON string.
        data = JSON.parse(data);

        // Get Datatable API
        var table = $('#AccountsTable').DataTable();

        // Get IDs of selected rows.
        var selected = table.rows({selected: true}).ids().toArray();

        // Add '#' to the IDs of each row
        selected.forEach(function(part, index) {
          this[index] = '#' + this[index];
        }, selected);

        data.data.forEach(function ( item ) {

// Get the id
var id = item.id;
var rowId = '#' + id;

// Get the current row using the ID
var row = table.row('#' + id);

// Update the row data

table.cell( rowId, 9 ).data( item.notes )
table.cell( rowId, 10 ).data( item.ranker )

});
        // Clear table.
        //table.clear();

        // Add new row data, draw and stay on same page.
        //table.rows.add(data.data).draw(false);

        // Reselect rows absed on ID.
        table.rows( selected ).select();
      }
    });
}

martin1223345

Last Question.

I have one row with the following data.

render: function ( data, type, row, meta ) {
                           if ( row.cat_color == "#ffffff")
               return ''+data+' ';
             else { return '<span aria-label="editcategory" id="dropdownMenuCat' + row.id + '" data-bs-toggle="dropdown" aria-expanded="false" class="badge" style="background-color:' + row.cat_color + ';" >' + row.cat_name + ' <i class="fas fa-pen" title="Edit Category"> </i></span><ul class="dropdown-menu" data-clientid="' + row.id + '" aria-labelledby="dropdownMenuCat' + row.id + '" >' + ChangeUser + '</ul>';
             }
             return data;
        } } , 

The folowing inline CSS is used to style the badge with the color saved on the database.

style="background-color:' + row.cat_color + ';"

The cell.data is not updating that variable so the old color is shown instead on the new color. Is there a way to have this updated to ?

kthorngren

Glad you got it working. You might want to add table.draw(); on line 35. This will update the table's display, ie, sorting and searching, with the new data.

You can try cells().invalidate() by passing in the column with the columns.render function. Like this, assuming the column number is 20:

table.cells( 20 ).invalidate().draw();

Kevin

martin1223345

Hey Kevin,

Thanks again for your reaction !

Tried it like this with no luck, what am I doing wrong?

 function getData() {
    $.ajax({
      url: "DBScriptLoadAccounts.php",
      success: function (data) {

        // Parse JSON string.
        data = JSON.parse(data);

        // Get Datatable API
        var table = $('#AccountsTable').DataTable();

        // Get IDs of selected rows.
        var selected = table.rows({selected: true}).ids().toArray();

        // Add '#' to the IDs of each row
        selected.forEach(function(part, index) {
          this[index] = '#' + this[index];
        }, selected);

        data.data.forEach(function ( item ) {

// Get the id
var id = item.id;
var rowId = '#' + id;

// Get the current row using the ID
var row = table.row('#' + id);

// Update the row data

// Update the row data
table.cell( rowId, 1 ).data( item.paydays )
table.cell( rowId, 2 ).data( item.name )
table.cell( rowId, 3 ).data( item.cat_name )
table.cell( rowId, 5 ).data( item.email )
table.cell( rowId, 6 ).data( item.password )
table.cell( rowId, 7 ).data( item.rang )
table.cell( rowId, 8 ).data( item.bullets )
table.cell( rowId, 9 ).data( item.notes )
table.cell( rowId, 10 ).data( item.ranker )
table.cell( rowId, 11 ).data( item.typenaam )
table.cell( rowId, 12 ).data( item.lead )
table.cell( rowId, 13 ).data( item.mobcash)
table.cell( rowId, 14 ).data( item.plane )
table.cell( rowId, 15 ).data( item.land )
table.cell( rowId, 16 ).data( item.honor )
table.cell( rowId, 17 ).data( item.secret )
table.cell( rowId, 18 ).data( item.door )
table.cell( rowId, 19 ).data( item.do_lead )

});

table.draw();
table.cells( 3 ).invalidate().draw();
table.rows( selected ).select();
      }
    });
}

kthorngren

I updated the example to test and found a change is needed:
http://live.datatables.net/jezolecu/4/edit

Added the cells().invalidate() to the end of the cell().data() example. You will see columns.render runs for display and other operations in this is called.

Use this - the null selects all rows :

table.cells( null, 3 ).invalidate().draw();

Since you are placing the table.cells( 3 ).invalidate().draw(); after the loop you can remove the table.draw() on line 53. Its only needed once

Kevin

martin1223345

Hey Kevin,

Thanks again for the quick response! Still not working. The ' + row.cat_color + ' from below rendered column is simply not refreshed.

"columns": [
 {data: 'cat_name',
                render: function ( data, type, row, meta ) {
              return '<span aria-label="editcategory" id="dropdownMenuCat' + row.id + '" data-bs-toggle="dropdown" aria-expanded="false" class="badge" style="background-color:' + row.cat_color + ';" >' + row.cat_name + ' <i class="fas fa-pen" title="Edit Category"> </i></span><ul class="dropdown-menu" data-clientid="' + row.id + '" aria-labelledby="dropdownMenuCat' + row.id + '" >' + ChangeUser + '</ul>';
                }}, ],

kthorngren

Doesn't look like you are updating that field in your loop of cell().data().

Kevin

martin1223345

Well. It does update the data gor the cat_name variable. I do it in below code.

table.cell( rowId, 3 ).data( item.cat_name ) is the column that stores that render.

 function getData() {
    $.ajax({
      url: "DBScriptLoadAccounts.php",
      success: function (data) {

        // Parse JSON string.
        data = JSON.parse(data);

        // Get Datatable API
        var table = $('#AccountsTable').DataTable();

        // Get IDs of selected rows.
        var selected = table.rows({selected: true}).ids().toArray();

        // Add '#' to the IDs of each row
        selected.forEach(function(part, index) {
          this[index] = '#' + this[index];
        }, selected);

        data.data.forEach(function ( item ) {

// Get the id
var id = item.id;
var rowId = '#' + id;

// Get the current row using the ID
var row = table.row('#' + id);

// Update the row data

// Update the row data
table.cell( rowId, 1 ).data( item.paydays )
table.cell( rowId, 2 ).data( item.name )
table.cell( rowId, 3 ).data( item.cat_name )
table.cell( rowId, 5 ).data( item.email )
table.cell( rowId, 6 ).data( item.password )
table.cell( rowId, 7 ).data( item.rang )
table.cell( rowId, 8 ).data( item.bullets )
table.cell( rowId, 9 ).data( item.notes )
table.cell( rowId, 10 ).data( item.ranker )
table.cell( rowId, 11 ).data( item.typenaam )
table.cell( rowId, 12 ).data( item.lead )
table.cell( rowId, 13 ).data( item.mobcash)
table.cell( rowId, 14 ).data( item.plane )
table.cell( rowId, 15 ).data( item.land )
table.cell( rowId, 16 ).data( item.honor )
table.cell( rowId, 17 ).data( item.secret )
table.cell( rowId, 18 ).data( item.door )
table.cell( rowId, 19 ).data( item.do_lead )

});

//table.draw();
table.cells( null, 3 ).invalidate().draw();
table.rows( selected ).select();
      }
    });
}

kthorngren

Are you expecting the cat_color value to change? If so you need to update it like the other columns.

Kevin

martin1223345

Yes I do expect it to change. I tried doing it the same way.

table.cell( rowId, 3 ).data( item.cat_name )
table.cell( rowId, 3 ).data( item.cat_color )

But that way will result in having the cat_color value in my column insteady of the cat_name value. So is there a other way to achieve that?

kthorngren

If is not a column defined in columns then you might need to do something like this instead:

// Get the current row using the ID
var row = table.row('#' + id);

row.data().cat_color = item.cat_color;

The expectation is this will update the Datatables data cache.

Kevin

martin1223345

Yes! That is it. Many Thanks Kevin ! I wont be bothering you anymore, thanks for your time !

kthorngren

No bother, glad its working.

Kevin