Remove ColumnControl on certain Columns

dnettles10

Link to test case:
Debugger code (debug.datatables.net):
Error messages shown:
Description of problem:

Here's the link to the test case but I couldn't get the output to match what I'm doing.
http://live.datatables.net/cupumifi/1/edit

I only want ColumnControl on columns MESSAGE_CODE, AID_YEAR, CREATE_DATE and STATUS but it shows up for all the columns.

Also, for column STATUS, that column will mostly be NULL but could have a value of "SAVED". With ColumnConrol, "SAVED" is never displayed like it does for MESSAGE_CODE or AID_YEAR. I'm assuming since STATUS can have NULL rows then nothing is displayed with ColumnControl. Can that be fixed? Or how do I get "SAVED" to part of ColumnControl?

HTML

<table id="example" class="display" data-page-length="100">
        <thead>
            <tr>
                <th></th>
                <th>Message Code</th>
                <th>Student ID</th>
                <th>Student Name</th>
                <th>Aid Year</th>
                <th>Create Date</th>
                <th>Status</th>
            </tr>
        </thead>
        <tfoot>
            <tr>
                <th></th>
                <th>Message Code</th>
                <th>Student ID</th>
                <th>Student Name</th>
                <th>Aid Year</th>
                <th>Create Date</th>
                <th>Status</th>
            </tr>
        </tfoot>
    </table>

Javascript

<script>
  let toolbar = document.createElement("div");
  toolbar.innerHTML = "";

  function format(d) {
    // `d` is the original data object for the row
    return (
        '<dl>' +
        '<dt>Message Code:</dt>' +
        '<dd>' +
        d.MESSAGE_CODE +
        '</dd>' +
        '<dt>Name:</dt>' +
        '<dd>' +
        d.STUDENT_NAME +
        '</dd>' +
        '<dt>Extra info:</dt>' +
        '<dd>And any further details here (images etc)...</dd>' +
        '</dl>'
    );
  }


  let table = new DataTable('#example', {
        searching: true,

        layout: {
          topStart: toolbar
        },

        ajax: {
          url: '/xxxx',
          dataSrc: ''
        },

        columns: [
          {
            className: 'dt-control',
            orderable: false,
            data: null,
            defaultContent: ''
          },

          { data: 'MESSAGE_CODE' },
          { data: 'STUDENT_ID' },
          { data: 'STUDENT_NAME' },
          { data: 'AID_YEAR' },
          { data: 'CREATE_DATE' },
          { data: 'STATUS' }
        ],

        columnDefs: [
          {
           targets: [1, 4, 5],
             columnControl: {
               target: 1,
               className: 'dtcc-row_no-bottom-border',
               content: ['order', ['title', 'spacer', 'searchList']
               ]
             }
          }
        ],
        columnControl: [
          {
           target: 1,
           className: 'dtcc-row_no-bottom-border',
           content: ['order', ['title']]
          },
          {
           target: 2,
           className: 'dtcc-row_no-top-padding',
           content: ['search']
          }
        ],
        ordering: {
          handler: false,
          indicators: false
        }
  });

  table.on('click', 'td.dt-control', function (e) {
    let tr = e.target.closest('tr');
    let row = table.row(tr);
 
    if (row.child.isShown()) {
        // This row is already open - close it
        row.child.hide();
    }
    else {
        // Open this row
        row.child(format(row.data())).show();
    }
  });

</script>

kthorngren

I updated your test case by copying your code into it.
http://live.datatables.net/cupumifi/2/edit

I used the Download Builder to get the libraries needed instead of using the nightly versions you had.

I used columns.className to add the class no-col-control (you can use any name you like) to the columns you don't want ColumnControl added to. Used columnControl: [] to apply nothing to those columns like this example.

I commented out the main columnControl option as that is adding those options to each column.

I added column 6 to the column.targets to apply the ColumnControl to MESSAGE_CODE, AID_YEAR, CREATE_DATE and STATUS.

I commented out the ajax option and created some fake data which I added using data.

I believe this is what you asked for. Note the following error occurs due to having null as data for STATUS.

Uncaught TypeError: Cannot read properties of null (reading 'toString')

I used Orthogonal data to change null to "" for the filter operation, for example:

           render: function (data, type, row) {
             if (type === 'filter') {
               return data === null ? '' : data;
             }
             return data;
           }

Kevin

dnettles10

This is wonderful!!! Thank you so much.

I've been playing around with the options and there's two more things I haven't figured out yet.

1. How do you have ColumnControls and a search box under the header? I know you must use target: 0 and target: 1 but I haven't found the correct combination to make it work.

2. How can I change AID_YEAR to be displayed as text versus numeric?

Thank you again.

allan

1) Have a look at this example which does what you are looking for.

2) Explicitly set that column to use -content searchText. The -content search content type will attempt to use one of -content searchText, -content searchDate or -content searchNumber based on the column's data type, but you can override it if you need. Column specific configuration can be done as shown in this example.

Allan

dnettles10

I still can't get this to work.

1. How do you have ColumnControls and a search box under the header?

2. How can I change AID_YEAR to be displayed as text versus numeric?

3. Is it possible to eliminate the Search Box on the Top Right hand above the Header?

1 - How do I combine these?

        columnDefs: [
          {
           targets: [1, 4, 5, 6],
             columnControl: {
               target: 0,
               className: 'dtcc-row_no-bottom-border',
               content: ['order', ['searchList']]
             },
             columnControl: {
               target: 1,
               content: ['searchText']
             }
          }
        ],

        columnDefs: [
          {
           targets: [1, 4, 5, 6],
             columnControl: {
               target: 0,
               className: 'dtcc-row_no-bottom-border',
               content: ['order', ['searchList']]
             },
             columnControl: {
               target: 1,
               content: ['searchText']
             }
          }
        ],

2 - I've tried this but this didn't work. The controls are always on the Left versus the Right.

        columnDefs: [
          {
           targets: [1, 4, 5, 6],
             columnControl: {
               target: 0,
               className: 'dtcc-row_no-bottom-border',
               content: ['order', ['searchList']]
             }
          },

          {
           targets: [1, 4, 5, 6],
             columnControl: {
               target: 1,
               content: ['searchText']
             }
          },

          {
           targets: [4],
             columnControl: {
               target: 0,
               content: ['searchText']
             }
          }
        ],

allan

You can't have two columnControl objects in a single object - just as you can't do:

{
  a: 1,
  a: 2
}

and expect a to be both 1 and 2.

You need to define it in an array if you want to define multiple rows of options for ColumnControl, as shown in the examples.

Your first code block should be:

columnDefs: [
  {
    targets: [1, 4, 5, 6],
    columnControl: [
      {
        target: 0,
        className: 'dtcc-row_no-bottom-border',
        content: ['order', ['searchList']]
      },
      {
        target: 1,
        content: ['searchText']
      }
    ]
  }
]

I've tried this but this didn't work. The controls are always on the Left versus the Right.

Oh, is it that you just want the controls on the right - not that you specifically need it to be string based? Have a look at this example for how to do that?

Allan

dnettles10

That worked. Thank you.

1. Can the SEARCH above the Header not be shown?

I have a few more questions but I'll see if I can figure them out before asking.

Things like:
1. Fixed column width for the Child Row Icon?
2. Can the Child Row Icon be changed?
3. "Loading...." text be a different color?

kthorngren

Can the SEARCH above the Header not be shown?

Use the layout option to control which Datatables elements are shown and where they are shown. See the default section to learn how to remove the default elements like search.

Fixed column width for the Child Row Icon?

Try columns.width.

Can the Child Row Icon be changed?

See this thread for one option.

"Loading...." text be a different color?

Yes. Usually you can right click on the elements to figure out the CSS selector to use. For this one its a bit difficult so I created this test case that errors and leaves the loading message on the page. Right click and inspect the element with the loadig records and you will find it has the class dt-empty. You can override the text color with something like this:

.dt-empty {
  color: red;
}

See the CSS tab of the test case.
http://live.datatables.net/pedijebo/1/edit

Kevin

dnettles10

Thank you for all your assistance.

Also, thank you for your assistance, Allan.

This is all coming together nicely.

Just curious. Is there an option to use a spinner and gray out the table while data is being loaded?

kthorngren

Use the processing event with something like blockui to block the page while loading then unblock the page when done.

Kevin

dnettles10

Thank you.

I have more item I need to figure out but it's more of a Javascript/jQuery problem if you can help.

Issue: Grid works fine with a Child Row being displayed when a row is clicked. This Child Row has a Form with a Comment field (to make this simple but the actual Form contains radio buttons, dropdown....etc). I have a function called formSubmit (formAction) that submits the Form using Fetch. That works just fine but I want to Remove that Row once Submit has been clicked. In the "then.response" I'm trying to do "table.row(".selected").remove().draw(false);" and of course, "table" doesn't exist. I'm using "var table = new DataTable("#example", {...."

Question: Is there a way to pass "table" to the "function formSubmit (formAction)"? Or somehow "function formSubmit (formAction)" know that "table" is the DataTables instance?

DataTables

     var table = new DataTable("#example", {
       searching: true,
       responsive: true,

       language: {
         loadingRecords: 'Please wait - loading...'
       },

       layout: {
         topStart: null
       },

       ajax: {
         url: "/xxx",
         dataSrc: ""
       },

       columns: [
         {
          className: "dt-control no-col-control",
          orderable: false,
          data: null,
          defaultContent: ''
         },

         { data: "MESSAGE_CODE" },
         { data: "STUDENT_ID", className: "no-col-control"},
         { data: "STUDENT_NAME", className: "no-col-control"},
         { data: "AID_YEAR" },
         { data: "CREATE_DATE" },
         { data: "STATUS",
           render: function (data, type, row) {
             if (type === "filter") {
               return data === null ? "" : data;
             }
             return data;
           }
         }
       ],

       columnDefs: [
         { width: "100px", targets: 0 },
         {
          targets: [1, 4, 5, 6],
          className: "dt-body-center",
          columnControl: [
            {
             target: 0,
             className: "dtcc-row_no-bottom-border",
             content: ["order", ["searchList"]]
            },
            {
             target: 1,
             content: ["searchText"]
            }
          ]
         },
         {
          targets: [2],
          className: "dt-body-center",
          columnControl: [
            {
             target: 1,
             content: ["searchText"]
            }
          ]
         },
         {
          targets: [3],
          className: "dt-body-left",
          columnControl: [
            {
             target: 1,
             content: ["searchText"]
            }
          ]
         }
       ],

       ordering: {
         handler: false,
         indicators: false
       }
     });

     table.on("click", "td.dt-control", function (e) {
       let tr = e.target.closest("tr");
       let row = table.row(tr);

       if (row.child.isShown()) {
         row.child.hide();
       }
       else {
         row.child(format(row.data())).show();
       }
     });

   };

Function formSubmit (Only Partical)

   function formSubmit (formAction) {
     event.preventDefault();
     let form = event.target.form;
     let params;

     var options = {
           method: "post",
           headers: {
             "Content-Type": "application/json;charset=utf-8",
             "Accept": "application/json;charset=utf-8"
           },
           body: params,
           mode: "cors"
         };

     if (form.checkValidity()) {
       return fetch('/xxx', options)
         .then(response => {
            if (response.ok)
              {
               if (formAction == "Submit") {
                 table.row(".selected").remove().draw(false);
               }
               toastr["success"]("The information has been updated.", "Success");
              }
              else
                {
                 toastr["error"]("Something went wrong.", "Error");
                }
         }) 
         .catch((error) => { console.log("Error " + error);
            toastr["error"]("Something went wrong.", "Error");
         });
     }
   }

Form

           detail = `<br>
                     <html>
                     <body>
                     <form id="messageCodeForm-${rownumber}" class="row g-3 needs-validation was-validated" novalidate>
                       ${defaultInputValues}
                       <input type="hidden" id="campus_code" name="campus" value="${campus_code}">
                       <div class="container">
                         <div class="div-margin">
                           <div class="row">
                             <div class="col-6">
                               <fieldset class="border p-2">
                                 <legend  class="float-none w-auto">Student Information</legend>
                                 <div class="row div-margin-bottom">
                                   <div class="col-3">ID Number:</div>
                                   <div class="col">${id}</div>
                                 </div>
                                 <div class="row div-margin-bottom">
                                   <div class="col-3">Name:</div>
                                   <div class="col">${name}</div>
                                 </div>
                                 <div class="row div-margin-bottom">
                                   <div class="col-3">Email Address:</div>
                                   <div class="col">${email}</div>
                                 </div>
                                 <div class="row div-margin-bottom">
                                   <div class="col-3">Phone Number:</div>
                                   <div class="col">${phone_number}</div>
                                 </div>
                                 <div class="row div-margin-bottom">
                                   <div class="col-3">Status:</div>
                                   <div class="col">${rzar_status}</div>
                                 </div>
                                 <div class="row div-margin-bottom">
                                   <div class="col-3">Counselor ID:</div>
                                   <div class="col">${counselor_id}</div>
                                 </div>
                                 <div class="row div-margin-bottom">
                                   <div class="col-3">Processor ID:</div>
                                   <div class="col">${processor_id}</div>
                                 </div>
                               </fieldset>
                               <p>&nbsp;</p>
                               <div class="row">
                                 <div class="col">
                                   <div id="save-comment-limit-alert-${rownumber}" class="alert alert-warning alert-dismissible" role="alert" style="display: none">
                                     <button type="button" class="close" onclick="$('#save-comment-limit-alert-${rownumber}').hide()" aria-label="Close"><span aria-hidden="true">&times;</span></button>
                                     <div id="save-comment-limit-alert-text-${rownumber}">${savedCommentLimitText}</div>
                                   </div>
                                   <div class="form-floating">
                                     <textarea id="comments" name="comments" class="form-control" style="height: 100px">${rzar_saved_comment}</textarea>
                                     <label for="comments">Comments</label>
                                     <div class="valid-feedback">Completed!</div>
                                     <div class="invalid-feedback">Please include comments.</div>
                                   </div>
                                 </div>
                               </div>
                             </div>
                             <div class="col-6">
                               <fieldset class="border p-2">
                                 <legend  class="float-none w-auto">Contact</legend>
                                   ${rzar_decision}
                               </fieldset>
                             </div>
                           </div>
                           <p>&nbsp;</p>
                           <div class="row">
                             <div class="col-6">
                               <button type="submit" id="button" class="btn btn-outline-primary btn-sm" onclick="formSubmit('Save')">Save</button>
                               &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                               <button type="submit" id="button" class="btn btn-primary btn-sm" onclick="formSubmit('Submit')">Submit</button>
                             </div>
                           </div>
                         </div>
                       </div>
                     </form>
                     </body>
                     </html>`;

Child Row Return

      return (detail);

kthorngren

Some options:

1. Make the variable table a global variable so it is in the scope of the formSubmit function.
2. Get an instance of the API within the formSubmit function. See this doc. For example:

function formSubmit (formAction) {
  event.preventDefault();
  var tables = $('#example').DataTable();
....

1. To make the function a bit more generic pass the table ID in the onclick function call, ie onclick="formSubmit('Submit', '#example')", then use that to get the API instance:

function formSubmit (formAction, tableId) {
  event.preventDefault();
  var tables = $(tableId).DataTable();
....

There are probably other options. Choose the best option that fits within your solution requirements.

Kevin

dnettles10

I made the "table" a global variable. Doh!! That resolved the table error message.

My issue now is the row is not removed. The Child Row remains open and the Main Row is still there. The formAction is "Submit".

1. How do I get the Child Row to close and the Main Row to be removed?

     if (form.checkValidity()) {
       return fetch('/xxx', options)
         .then(response => {
            if (response.ok)
              {
               if (formAction == "Submit") { console.log("form action response " + formAction);
                 table.row(".selected").remove().draw(false);
               }
               toastr["success"]("The information has been updated.", "Success");
              }
              else
                {
                 toastr["error"]("Something went wrong.", "Error");
                }
         }) 
         .catch((error) => { console.log("Error " + error);
            toastr["error"]("Something went wrong.", "Error");
         });
     }
   }

kthorngren

The row probably doesn't have the selected class. I might be missing it but I don't see that you are using the Select extension for that class to be added.

You could update the event handler to show the child rows to toggle the selected or whatever class you choose using row().node(). Something like this:

  table.on("click", "td.dt-control", function (e) {
    let tr = e.target.closest("tr");
    let row = table.row(tr);
 
    if (row.child.isShown()) {
      row.child.hide();
      row.node().removeClass('selected');
    }
    else {
      row.child(format(row.data())).show();
      row.node().addClass('selected');
    }
  });

You will probably need to remove the child row before removing. Not sure though. Something like this:

            if (formAction == "Submit") { console.log("form action response " + formAction);
              var row = table.row(".selected")
              row.child.remove()
              row.remove().draw(false);
            }

Kevin

dnettles10

Thanks. I'll try this.

dnettles10

I'm using the code in the Child Row Example.

table.on('click', 'td.dt-control', function (e) {
    let tr = e.target.closest('tr');
    let row = table.row(tr);
 
    if (row.child.isShown()) {
        // This row is already open - close it
        row.child.hide();
    }
    else {
        // Open this row
        row.child(format(row.data())).show();
    }
});

I switched to your code and I'm getting an error of "row.node().addClass is not a function"

     table.on("click", "td.dt-control", function (e) {
       let tr = e.target.closest("tr");
       let row = table.row(tr);

       if (row.child.isShown()) {
         row.child.hide();
         row.node().removeClass('selected');
       }
       else {
         row.child(format(row.data())).show();
         row.node().addClass('selected');
       }
     });

kthorngren

Sorry use it like this:

$( row.node() ).addClass('selected');

Need to place row.node() inside $( ) to use the jQuery API.

Kevin

dnettles10

Perfect. Thank you so much. I'm really liking the way this is working.

dnettles10

I got everything working except for one thing I'm not sure about.

The current process has a grid that expands showing a form that can be filled out. Six unique forms based on the message code. There is a Save and Submit button which sends the data to the database with JSON data being updated. This works fine but the grid is reloaded each time for the current data.

With the DataTables grid, I like the fact that the data can be Submitted and the row can be removed without reloading the grid. The problem is that doesn't work very well when the data is Saved. The information is still sent to the database and the JSON is updated but DataTables doesn't know that. The row and child information remains but once you click the icon to close out that child information and then click again you are back to square one with everything needing to be filled out again.

Maybe you might have an idea.

Couple thoughts:
1. Session storage of the variables. Not sure if I would be allowed to do this.
2. Reload the grid each time.
3. When a row is clicked then return the JSON information at that time. That would ensure the latest JSON data is being return. Currently, the JSON data is being returned with the data that is displayed on the grid. This would make for a lot of AJAX calls.
4. Other method?

Sorry, this is well beyond the help you have already given me. Just thought you might have an idea on how to approach this issue without reloading the grid each time.

kthorngren

In the success function use row().data() to update the parent row's data. Then use row().child.hide() followed by row().child().show() to have the child row show the updated data. Do this just before this statement:

toastr["success"]("The information has been updated.", "Success");

Here is a simple example.
http://live.datatables.net/socirone/71/edit

Open a child and click the Update button. The name will be updated using row().data() and the child will be closed and reopened using the update table data.

Kevin

dnettles10

I probably didn't explain myself correctly. The only column in the grid that would change would be the Status column to show "Saved".

It's the Child Row information that I want to keep with the values selected. Looks like that can be done by using "row.child(format(row.data())).show();" but it's information from a column from the grid.

Hopefully, this will give you a better perspective. Here's some snippets from two forms but others have radio buttons, dropdowns and comment boxes. Any information selected or entered would need to be saved within those elements the next time the expand icon is selected. I hope that makes sense.

A user has selected the icon on one of the rows in the grid. The expanded Child Row displays a version of the form that looks similar to the two examples below. Clicking "Save" would retain any information selected or entered within the elements and would be shown again if the selected icon was clicked again.

kthorngren

I think the easiest place to store the form info is in the table row data. It doesn't need to be visible in the table. It doesn't even need to be defined with columns.data. It can be provided in the ajax response from the server so it is attached to the row.

I updated the above example with a simple text input to show this:
http://live.datatables.net/socirone/72/edit

It uses a property called text to store the value of the text input in the child of each row. It's not defined within Datatables. Enter data into one of the child rows and click Update. Close the row. Go to another page if you wish then back. Reopen the child row and the input has the changed value.

I'm not familiar with your code or solution so my idea might not be feasible.

Kevin

dnettles10

Yes, I think you are on to something.

Would it be possible for you to update your example with that same text box and an additional radio button selection (4 - 5 selections) and a dropdown. Basically, take my first graphic and incorporate that into the Child Row showing the radio button selected, the dropdown value selected and text entered into the text box. That's really what I'm up against. It's those multiple elements that need to retain the values selected.

I do wonder if I could have a hidden column that contains the JSON elements for that row. If the Status column contains "Saved" then read the JSON from that hidden column to update the values within the Form when the Child Row is expanded. Right now, those JSON elements are being read and populate the Form elements when the grid is being loaded and shown when the Child Row is expanded.

kthorngren

My example is just to simply show how you can add data to the table row without needing to make it a column. Datatables has a data cache for all the table data. It's a standard Javascript data structure so you can store a JSON string or whatever you need.

In the code used to populate the form just check to see if the Status column contains saved and if so retrieve the JSON saved in the row data and use that to populate the form.

If you need help with this then please update the test case to show an example form and how it is being populated now. Likely you can use a Javascript variable to fake some JSON data for the form.

Kevin

dnettles10

Thank you. I'm going to play around with this to see what I can get working.

Will probably be back asking questions but maybe I'll get lucky and won't have to ask questions.

Thanks again.