Table Clear and Draw choose what columns to draw and clear

Table Clear and Draw choose what columns to draw and clear

martin1223345martin1223345 Posts: 84Questions: 23Answers: 0

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>' } },

This question has accepted answers - jump to:

Answers

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    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

  • martin1223345martin1223345 Posts: 84Questions: 23Answers: 0

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

  • martin1223345martin1223345 Posts: 84Questions: 23Answers: 0
    edited February 2022

    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.

  • kthorngrenkthorngren Posts: 20,142Questions: 26Answers: 4,736

    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

  • martin1223345martin1223345 Posts: 84Questions: 23Answers: 0

    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?

  • martin1223345martin1223345 Posts: 84Questions: 23Answers: 0

    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();
          }
        });
    }
    
  • martin1223345martin1223345 Posts: 84Questions: 23Answers: 0
    edited February 2022

    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..

  • kthorngrenkthorngren Posts: 20,142Questions: 26Answers: 4,736

    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

  • martin1223345martin1223345 Posts: 84Questions: 23Answers: 0

    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

  • kthorngrenkthorngren Posts: 20,142Questions: 26Answers: 4,736

    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

  • kthorngrenkthorngren Posts: 20,142Questions: 26Answers: 4,736
    edited February 2022 Answer ✓

    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

  • martin1223345martin1223345 Posts: 84Questions: 23Answers: 0

    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();
          }
        });
    }
    
  • martin1223345martin1223345 Posts: 84Questions: 23Answers: 0
    edited February 2022

    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 ?

  • kthorngrenkthorngren Posts: 20,142Questions: 26Answers: 4,736

    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

  • martin1223345martin1223345 Posts: 84Questions: 23Answers: 0

    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();
          }
        });
    }
    
  • kthorngrenkthorngren Posts: 20,142Questions: 26Answers: 4,736
    edited February 2022

    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 :smile:

    Kevin

  • martin1223345martin1223345 Posts: 84Questions: 23Answers: 0

    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>';
                    }}, ],
    
  • kthorngrenkthorngren Posts: 20,142Questions: 26Answers: 4,736

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

    Kevin

  • martin1223345martin1223345 Posts: 84Questions: 23Answers: 0

    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();
          }
        });
    }
    
  • kthorngrenkthorngren Posts: 20,142Questions: 26Answers: 4,736

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

    Kevin

  • martin1223345martin1223345 Posts: 84Questions: 23Answers: 0

    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?

  • kthorngrenkthorngren Posts: 20,142Questions: 26Answers: 4,736
    Answer ✓

    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

  • martin1223345martin1223345 Posts: 84Questions: 23Answers: 0

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

  • kthorngrenkthorngren Posts: 20,142Questions: 26Answers: 4,736

    No bother, glad its working.

    Kevin

Sign In or Register to comment.