Drill-down data

Drill-down data

edited June 2011 in Blog Posts: 21,980
Hello all,

A new thread for a new blog post :-) http://datatables.net/blog/Drill-down_rows . In this post I show how a details row in the table can be controlled by the end user through the API, with help from a couple of new features in DataTables 1.8 and a nice display animation.

Enjoy!
Allan
«13

Replies

  • edited June 2011 Posts: 10
    Hey Allan, first off thank you for the time and support that you put into this plugin! My donation is on its way.

    I have modified this slightly to remove the td image and changed it to activate the details div based upon clicking anywhere on the row, below is my working code

    <code>
    $('#example tbody tr').live( 'click', function () {

    var nTr = this;
    var i = $.inArray( nTr, anOpen );

    if ( i === -1 ) {
    $(this).addClass('row_selected');
    var nDetailsRow = oTable.fnOpen( nTr, fnFormatDetails(oTable, nTr), 'details' );
    $('div.innerDetails', nDetailsRow).slideDown();
    anOpen.push( nTr );
    }
    else {
    $(this).removeClass('row_selected');
    $('div.innerDetails', $(nTr).next()[0]).slideUp( function () {
    oTable.fnClose( nTr );
    anOpen.splice( i, 1 );
    } );

    }
    } );
    </code>

    I have added a row_selected class to the <tr> so that I can change the open row's color. I have seen this question about row opening asked on many other forum posts so hopefully this can help others.

    One issue I have been having is I would like to only have one row "open" at a time. This is because I am using a form to post php to mysql inside my details div and with multiple rows open I have similar DOM elements that cause me problems with the form submission. I know I could probably dynamically generate the form id's so they would be able to be identified separately in the DOM but this is not needed for my implementation.

    Could I get an example of how I would check the anOpen array and just close all rows before opening a new one? I assume I could just add this as the first command in the open row check so that I would only have one details form in the DOM at one time. Thanks again for your help!

    Mike
  • Posts: 21,980
    Hi Mike,

    Chytkam said: One issue I have been having is I would like to only have one row "open" at a time

    This can be achieved by checking to see if there are any rows already open in the anOpen array and if so, then close them. Something like this:

    $('#example td.control').live( 'click', function () { var nTr = this.parentNode; var i = $.inArray( nTr, anOpen ); $(anOpen).each( function () { if ( this !== nTr ) { $('td.control', this).click(); } } ); if ( i === -1 ) { $('img', this).attr( 'src', sImageUrl+"details_close.png" ); var nDetailsRow = oTable.fnOpen( nTr, fnFormatDetails(oTable, nTr), 'details' ); $('div.innerDetails', nDetailsRow).slideDown(); anOpen.push( nTr ); } else { $('img', this).attr( 'src', sImageUrl+"details_open.png" ); $('div.innerDetails', $(nTr).next()[0]).slideUp( function () { oTable.fnClose( nTr ); anOpen.splice( i, 1 ); } ); } } ); } );
    The only new part there is the "$(anOpen).each( function () {" block which will simply close any open rows which are not the target.

    Regards,
    Allan
  • Posts: 14
    What if a details row is opened (using the above code) and the user navigates to the next page of the table, then opens a new details row. The one on the previous page will still remain open - is it because you're closing the details row with a simulated click?
  • Posts: 14
    I believe I figured out my own solution (using the same simulated click logic):

    var oTable = $('#example').dataTable({ "fnPreDrawCallback": function (oSettings) { $("td.control img[src$='details_close.png']").click(); } });
  • edited July 2011 Posts: 65
    I'm integrating this into http://www.modeltraintracker.com/sandbox/test_item.php but am having the following issue:
    If you click the expansion td to expand info out, the "edit" and "delete" buttons are enabled, as they should be. If you click on another row's expansion td, the old row collapses, the new row expands, but the "edit" for the new row is not enabled. It would appear that the "click" listener, being a "live" event, is handled serially, and the old row is collapsed after the new row expanded, thus disabling the edit button. What is the best way around this?

    Edit: here's my code:
    $('#item_table td.control').live( 'click', function () { var nTr = this.parentNode; var i = $.inArray( nTr, anOpen ); $(anOpen).each( function () { if ( this !== nTr ) { $('td.control', this).click(); } } ); if ( i === -1 ) { $('img', this).attr( 'src', sImageUrl+"details_close.png" ); $('div.innerDetails', nDetailsRow).slideDown(); var nDetailsRow = oTable.fnOpen( nTr, fnFormatDetails(oTable, nTr), 'details' ); anOpen.push( nTr ); $(nTr).addClass('row_selected'); clickedRowId = $(nTr).attr('id'); $('#btnEditRow').button( "option", "disabled", false ); } else { $('img', this).attr( 'src', sImageUrl+"details_open.png" ); $('div.innerDetails', $(nTr).next()[0]).slideUp( function () { $(nTr).removeClass('row_selected'); clickedRowId = 0; oTable.fnClose( nTr ); anOpen.splice( i, 1 ); $('#btnEditRow').button( "option", "disabled", true ); } ); } return false; } );
  • Posts: 1
    Hey Allen,

    This is a great plugin and excellent blog post. I wonder if you (or someone out there) can help me use this example for a table that does not use an ajax for a datasource.

    I have a standard html table with about 20-25 items in it. I've applied the datatable to it and everything looks good.

    When I added the code to add row details as shown in the blog post, I can't quite figure out how to approach the problem because I'm not sure how to correctly use the fnOpen function with HTML content that already exists somewhere in the DOM. Should I add all the row details elsewhere in the page and call them when needed?

    Thanks in advance for any help.
  • Posts: 106
    I'm following the code in the blog and would like to know how to obtain a value of a column in the row that was just clicked to reveal the details. What I have is an ID in the second column (immediately to the right of the '+' image. I'd like to have the value of that id so that I can then pull relevant data for that entity to be displayed in the details row.

    I've tried:

    var aData = oTable.fnGetData( nTr ); var id = aData[1];
    but that didn't work. id is always 'undefined'.

    Thanks in advance.
  • Posts: 106
    Never mind, I figured it out.
  • Posts: 15
    I wonder if in these cases the solution would not be of interest to others...
  • Posts: 106
    OK. If anyone cares, my solution had nothing to do with aData. I was recycling old code and aData just wasn't required. What I ended up doing was using:

    var id = $(nTr.children[1]).html();
    where children[1] happens to be the column where I store the ID. If there's a better way, do tell...
  • Posts: 7
    Hi, I am not able to show the detail view. I had a link saying View Details instead of a Image.

    I have a datatable which has JSON data, I added a link to see the details. When I am clicking on the link, nothing happening.

    Thanks,

    Below is my code:

    [code]
    <script type="text/javascript">

    var dataTable;
    $(function () {

    $("#GetUserBtn").click(function (e) {
    e.preventDefault();
    var postUrl = $("#UserInfo").attr("action");
    var roleCode = $("#RoleCode").val();

    if (roleCode.length <= 0) {
    $(".roleValidation").show();
    return false;
    }
    else {
    $(".roleValidation").hide();
    $.post(postUrl, { "roleCode": roleCode }, function (data) {

    var userData = data.Data != null ? data.Data : [];

    dataTable = $('#usersTable').dataTable({
    "bJQueryUI": true,
    "aaData": userData,
    "oLanguage": {
    "sZeroRecords": "No Records Found"
    },
    "bDestroy": true,
    "aoColumns": [{ "sTitle": "Full Name", "mDataProp": "FullName" }, { "sTitle": "UserName", "mDataProp": "UserName" }, { "sTitle": "Email", "mDataProp": "Email" }, { "sTitle": "Role", "mDataProp": "RoleName" }, { "sTitle": "View Details", "mDataProp": null, "sDefaultContent": '<a href="#">View Details</a>'}]

    });

    });
    return false;
    }
    });
    });

    $('#usersTable td.control').live('click', function () {
    var nTr = this.parentNode;
    var i = $.inArray(nTr, anOpen);

    if (i == -1) {
    //$('a', this).attr( 'src', sImageUrl+"details_close.png" );
    oTable.fnOpen(nTr, fnFormatDetails(oTable, nTr), 'details');
    anOpen.push(nTr);
    }
    else {
    //$('img', this).attr( 'src', sImageUrl+"details_open.png" );
    oTable.fnClose(nTr);
    anOpen.splice(i, 1);
    }
    });

    function fnFormatDetails(oTable, nTr) {
    var oData = oTable.fnGetData(nTr);
    var sOut =
    '<div class="innerDetails">' +
    '<table cellpadding="5" cellspacing="0" border="0" style="padding-left:50px;">' +
    '<tr><td>Full Name:</td><td>' + oData.FullName + '</td></tr>' +
    '<tr><td>UserName:</td><td>' + oData.UserName + '</td></tr>' +
    '<tr><td>Email:</td><td>' + oData.Email + '</td></tr>' +
    '</table>' +
    '</div>';
    return sOut;
    }
    </script>
    }


    </script>
  • Trying to figure out how the drill-down data piece works. What I have now is a regular table that just unhides rows when they are clicked on.

    Am I reading the example correct, do I have to essentially populate a js array with all the drop down values?

    On that same note, only 1/4 of the fields will have a drop down available, so does that mean I have to pass in a bunch of blank values?

    Thanx ahead of time.
  • Posts: 2
    Hi Allan, great job. I'm new here and I've been able to successfully implement a drill down dataTable. However, I have a challenge...how can I close and slide up a row from within the drill down? I have added a button within the drill down, but I'm not sure how to make sure I close the exact row I'm on. Can you help? Thanks.
  • Posts: 21,980
    What you need to do is basically the same as the code used in the example to close the row (see the part where fnClose is called). But that will need to be called by your button inside the details row. You'll probably be best using live events for that.

    Allan
  • Posts: 2
    Thanks for the reply Allan. Appreciated.
  • What's the best way to get the details html from an external url?

    oTable.fnOpen( nTr, $(this).load('http://www.google.com/';), 'details' );
  • I'm building a drill-down dataTable from an Ajax source, and am having trouble implementing a custom row filter on top of it. The general filter works fine, but I can't make the custom filter code work in conjunction with the code that populates and scrolls up/down for the inner table. Can you provide any help? Basically I'd like to figure out how to build a range filter and/or a pull-down filter based on the data within a certain column. Thanks in advance!
  • Posts: 21,980
    @jbrahy - Probably using a proxy script on your own server, so you don't run into problems with cross domain security concerns.

    @writermgb - This is how you build a range filter: http://datatables.net/release-datatables/examples/plug-ins/range_filtering.html - but I don't really understand how you would want that to relate to the information in the details row.

    Allan
  • edited February 2012 Posts: 2
    found it somewhere. sorry, don't remember where it was but here's the code to load an external url into a tab.

    replace the:

    oTable.fnOpen(nTr, fnFormatDetails(oTable, nTr), 'details');
    with this:

    $.get("/ajax/load_user.php?user_id=" + aData[1], function (response) {
    oTable.fnOpen(nTr, response.details, 'details');
    });

    and I return a JSON encoded array from /ajax/load_user.php?user_id=" + aData[1] using php like this:

    $data = array();
    $data['details'] = "<h1>hello world</h1>";

    return json_encode($data);

    Hope this helps!
  • Hi!

    I used the original example to create the details row. It contains a datatable with server side processing using JSON data. Everything is fine. I can use the add new data button and I customized the form to get all the data for the inner datatable. What I want to do when a new element added to the inner datatable is to refresh the original datatable to reflect the changes. I can refresh with the fnDraw(false) function inside the fnOnAdded event handler. What I cannot do is to leave the details row open. Not open again (although it would be a good workaround) but leave open.

    I tried to use
    fnOnAdded: function(status) { oTable.fnDraw(false); $(anOpen).each( function () { if ( this == nTr ) { $('td.control', this).click(); } }); return true; } but no luck.

    Any help welcome! Thanks!
  • I'm looking for help with this here if anyone can spare a few minutes please?

    http://www.datatables.net/forums/discussion/9255/help-with-drill-down-rows/p1

    Thanks
  • Posts: 2
    Hi,

    Can I use a nested data table as the original html from before intializing data tables?

    Something like:

    <table> <thead></thead> <tbody> <tr> <td>"parent" table</td> </tr> <tr> <td> <table> <tr> <td>nested table</td> </tr> </table> </td> </tr> </tbody> </table>
    Thanks!
  • Posts: 21,980
    Yes - what you would need to do is run a Javascript function over your table before initialising DataTables, reading your details rows, attaching them to their parents as a property (so there is a relationship between then) and then removing the details rows from the DOM. Then initialise DataTables and when you need to show a details row, just put the row you previous removed back in :-)

    Allan
  • Posts: 1
    Allen,
    Thanks for great plug in. my issue is displaying multiple rows of json data in the drill down display.

    My initial table displays a roll up of funding for a contract [total amount added to it] I want the drill down to display each increment that was part of the total. Can you point me to any good examples?
    {with detailed explanation if possible?}

    I currently have it working displaying only the first row, How do I loop through the others?


    <script type="text/javascript"> // the table containing the index data var oTable_2 = ""; // the select row var nTargetRow = ""; // // var aData = ""; // holds the text for selected row (column position is critical) /* ----------------------------------------------------------------------- Jquery Ajax action scrpts ----------------------------------------------------------------------- */ // Method retrieves the selected row index id and executes the ajax call function getModDetails() { //alert('getModDetails'); // retrieve the data from the selected row aData = oTable_2.fnGetData( nTargetRow ); // build the url for the ajax call to use var cfcUrl = '#application.ajax_url#'; cfcUrl +='RemoteModDetails.cfc?'; cfcUrl += 'method=getPos'; cfcUrl +='&porderid=' + aData[1] ; // make the ajax call $.ajax( { type: "GET", url: cfcUrl, cache: false, contentType: "application/json; charset=utf-8", data: "{}", dataType: "json", success: function (objResponse ) { ; if(objResponse.SUCCESS) { openModDetailRow(objResponse.DATA); } else { ShowErrors(objResponse.ERRORS); } }, error : function(jqXHR, textStatus, errorThrown ) { ajaxErrorHandler( jqXHR, textStatus, errorThrown ); } }); } // Function ajaxErrorHandler // DESCRITPION: Error Handler fir the error message for failed ajax call' // PARAMETERS: // jqXHR = The jqXHR (in jQuery 1.4.x, XMLHttpRequest) object, // textStatus = a string describing the type of error that occurred // and an optional exception object, if one occurred. // Possible values for the second argument (besides null) are // "timeout", "error", "abort", and "parsererror". // errorThrown = the textual portion of the HTTP status, such as "Not Found" // or "Internal Server Error." function ajaxErrorHandler( jqXHR, textStatus, errorThrown) { alert('Unable to retieve the requested information due to a server error'); } // Handle the failed response error function ShowErrors( statusMsg) { alert('Unable to retieve the requested information. Status: ' + statusMsg); } // create the indormation row based on the data retruned by the ajax call function openModDetailRow(indexInfo) { /* Assumes only one row returns; additional rows ignored */ var aReqNumID = indexInfo.DATA [0][0]; var aPorderID = indexInfo.DATA [0][1]; var aRequistionNum = indexInfo.DATA [0][2]; var aAwardDte = indexInfo.DATA [0][3]; var aAwardAmount = indexInfo.DATA [0][4]; var aDteEntered = indexInfo.DATA [0][5]; var aEnteredBy = indexInfo.DATA [0][6]; var aDteMod = indexInfo.DATA [0][7]; var aModBy = indexInfo.DATA [0][8]; var aValidFlag = indexInfo.DATA [0][9]; var aModNum = indexInfo.DATA [0][10]; var aDescription = indexInfo.DATA [0][11]; ; // create the additonal information table var sOut = '<table align="left" width="95%" cellpadding="2" cellspacing="0" border="1" class="hiddenTbl ui-widget-content">'; // row 1 sOut += '<tr><td><strong>Mod Number:</strong><br> <strong style="color:##FF0;">&nbsp;'+ aModNum +'</strong></td>'; sOut += '<td><strong>Requsition Numbeer:</strong><br> '+aRequistionNum + '</td><td><strong> Award Amount:</strong><br> <strong style="color:##FF0;">$' + aAwardAmount.toFixed(2) + '</strong></td></tr>'; // row 2 sOut += '<tr><td ><strong>Award Date:</strong><br>'+ aAwardDte + ' </td><td ><strong>Description:</strong><br> ' + aDescription + '</td><td> </td></tr>'; // end of table sOut += '</table>'; // display the row oTable_2.fnOpen( nTargetRow, sOut, 'details' ); } /* ----------------------------------------------------------------------- * Jquery Display script * ----------------------------------------------------------------------- */ $(document).ready(function() { /* * Insert a 'details' column to the table */ var nCloneTh = document.createElement( 'th' ); // for header var nCloneThF = document.createElement( 'th' ); // for footer var nCloneTd = document.createElement( 'td' ); // for body nCloneTd.innerHTML = '<img src="./images/details_open.png">'; nCloneTd.className = "center"; // add blank header $('##MVW thead tr').each( function () { this.insertBefore( nCloneTh, this.childNodes[0] ); } ); // add blank footer header $('##MVW tfoot tr').each( function () { this.insertBefore( nCloneThF, this.childNodes[0] ); } ); // add icon open/close info rowrow $('##MVW tbody tr').each( function () { this.insertBefore( nCloneTd.cloneNode( true ), this.childNodes[0] ); } ); // run the jquery data table script oTable_2 = $('##MVW'); oTable_2.dataTable({ "aoColumnDefs": [ { "bSortable": false, "aTargets": [ 0 ] } ], "aaSorting": [[1, 'asc']], "iDisplayLength": 5, "aLengthMenu": [[5,10, 25, 50, 100, -1], [5,10, 25, 50, 100, "All"]], "bJQueryUI": true });/* end data table */ /* Add event listener for opening and closing details * Note that the indicator for showing which row is open is not controlled by DataTables, * rather it is done here */ $('##MVW tbody td img').live('click', function () { nTargetRow = this.parentNode.parentNode; if ( this.src.match('details_close') ) { /* This row is already open - close it */ this.src = "./images/details_open.png"; oTable_2.fnClose( nTargetRow ); } else { this.src = "./images/details_close.png"; getModDetails(); } } ); }); /* end ready */ </script>
    thanks.
  • Posts: 1
    Thanks for great plugin Allen. I need to implement a drill down data table within the drill down data table. Hope you understood my question. Is that possible to expand the rows in inner table ? Thanks.
  • edited May 2012 Posts: 21,980
    Is that possible to expand the rows in inner table ?

    Yes it is - but you need to actually make the inner table 100% independent from the parent table. For example, at the moment the columns would not align between the parent and child. If that is fine then you just need to put your table into the 'details' row and initialise it like any other table.

    It is actually possible to get the columns to align 100%, but it isn't trivial at the moment and involves a bit of work.

    Allan
  • Posts: 26
    Allen,
    Do you know if anyone has successfully done a "expand/collapse all" for Drill-Downs?
    I've tried, and while it opens/expands just fine, it doesn't close/collapse past the first entry.
    Most likely my code on how I'm doing it, below is the code for it.

    $("#expandAllTR").click(function (){ $("#parentTable tbody tr").each(function() { if (oTable.fnIsOpen(this)){ oTable.fnClose(this); }else{ var position = oTable.fnGetPosition(this); var info = oTable.fnGetData(position)[11]; oTable.fnOpen( this, info, "info_row" ); } }); if($("#expandAllTR").html()=="Expand All"){ $("#expandAllTR").html("Collapse All"); }else{ $("#expandAllTR").html("Expand All"); } });
  • Posts: 21,980
    Hi,

    That looks really close to me - the only problem I can see with the code is that your loop will encompass all TR elements in the table - not just those which are 'open rows' (i.e. it is calling fnClose on the details row as well).

    This can be addressed by using the $ API method to get all TR elements under the table's control. Do do that, change:

    $("#parentTable tbody tr").each(function() {
    to:

    oTable.$('tr').each(function() {
    This is the result: http://live.datatables.net/eruhof/edit#javascript,html .

    Regards,
    Allan
  • Hi Allan,

    I am showing one row details at a time. Is there any way to pass DOM element (not string) as the content of the details.
    I have a hidden form in my page which I want to append (not clone) as the 'innerDetails'.
  • Posts: 1
    Hi Allan,

    Thanks for sharing the drill down rows example. I need help in implementing some specific requirement for drill down rows.

    I am getting all the parent-child data from server in one go (no ajax needed). Each row has an column indicator which has 'p' for parent and 'c' for children and they come in proper order like..

    + aaa, bbbb, cccc, dddd, eeee, ffff, p
    - aaa, bbb1, ccc1, ddd1, eeee1, ffff1, c
    - aaa, bbb2, ccc2, ddd2, eeee2, ffff2, c
    aaa, bbbb, cccc, dddd, eeee, ffff, p
    + aaa, bbbb, cccc, dddd, eeee, ffff, p
    + aaa, bbbb, cccc, dddd, eeee, ffff, p
    If a parent has children then only the expand/collapse should appear else it will be normal row.

    Please if you could help me with some pointers regarding the same.

    Thanks in anticipation,
    Kamlesh
Sign In or Register to comment.