Drill-down data

Drill-down data

allanallan Posts: 63,643Questions: 1Answers: 10,492 Site admin
edited June 2011 in Blog
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

  • ChytkamChytkam Posts: 10Questions: 0Answers: 0
    edited June 2011
    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


    $('#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 );
    } );

    }
    } );


    I have added a row_selected class to the 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
  • allanallan Posts: 63,643Questions: 1Answers: 10,492 Site admin
    Hi Mike,

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

    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:

    [code]
    $('#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 );
    } );
    }
    } );
    } );
    [/code]

    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
  • manentiamanentia Posts: 14Questions: 0Answers: 0
    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?
  • manentiamanentia Posts: 14Questions: 0Answers: 0
    I believe I figured out my own solution (using the same simulated click logic):

    [code]
    var oTable = $('#example').dataTable({
    "fnPreDrawCallback": function (oSettings) {
    $("td.control img[src$='details_close.png']").click();
    }
    });
    [/code]
  • dmolavidmolavi Posts: 65Questions: 0Answers: 0
    edited July 2011
    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:
    [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;
    } );[/code]
  • arcdegreearcdegree Posts: 1Questions: 0Answers: 0
    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.
  • MikeSMikeS Posts: 113Questions: 1Answers: 0
    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:

    [code]
    var aData = oTable.fnGetData( nTr );
    var id = aData[1];
    [/code]

    but that didn't work. id is always 'undefined'.

    Thanks in advance.
  • MikeSMikeS Posts: 113Questions: 1Answers: 0
    Never mind, I figured it out.
  • LuysLuys Posts: 15Questions: 0Answers: 0
    I wonder if in these cases the solution would not be of interest to others...
  • MikeSMikeS Posts: 113Questions: 1Answers: 0
    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:

    [code]
    var id = $(nTr.children[1]).html();
    [/code]

    where children[1] happens to be the column where I store the ID. If there's a better way, do tell...
  • dt8485dt8485 Posts: 7Questions: 0Answers: 0
    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]


    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": 'View Details'}]

    });

    });
    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 =
    '' +
    '' +
    'Full Name:' + oData.FullName + '' +
    'UserName:' + oData.UserName + '' +
    'Email:' + oData.Email + '' +
    '' +
    '';
    return sOut;
    }

    }
  • VirtualLifeVirtualLife Posts: 1Questions: 0Answers: 0
    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.
  • obi1obi1 Posts: 2Questions: 0Answers: 0
    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.
  • allanallan Posts: 63,643Questions: 1Answers: 10,492 Site admin
    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
  • obi1obi1 Posts: 2Questions: 0Answers: 0
    Thanks for the reply Allan. Appreciated.
  • jbrahyjbrahy Posts: 2Questions: 0Answers: 0
    What's the best way to get the details html from an external url?

    oTable.fnOpen( nTr, $(this).load('http://www.google.com/'), 'details' );
  • writermgbwritermgb Posts: 1Questions: 0Answers: 0
    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!
  • allanallan Posts: 63,643Questions: 1Answers: 10,492 Site admin
    @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
  • jbrahyjbrahy Posts: 2Questions: 0Answers: 0
    edited February 2012
    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'] = "hello world";

    return json_encode($data);

    Hope this helps!
  • MPeter1975MPeter1975 Posts: 31Questions: 0Answers: 0
    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
    [code]
    fnOnAdded: function(status)
    {
    oTable.fnDraw(false);
    $(anOpen).each( function () {
    if ( this == nTr ) {
    $('td.control', this).click();
    }
    });
    return true;
    }
    [/code]
    but no luck.

    Any help welcome! Thanks!
  • thewebgentsthewebgents Posts: 7Questions: 0Answers: 0
    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
  • anabelleanabelle Posts: 2Questions: 0Answers: 0
    Hi,

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

    Something like:

    [code]




    "parent" table





    nested table






    [/code]

    Thanks!
  • allanallan Posts: 63,643Questions: 1Answers: 10,492 Site admin
    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
  • jabird4kjabird4k Posts: 1Questions: 0Answers: 0
    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?


    [code]


    // 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 = '';
    // row 1
    sOut += 'Mod Number:
     '+ aModNum +'';
    sOut += 'Requsition Numbeer:
    '+aRequistionNum + ' Award Amount:
    $' + aAwardAmount.toFixed(2) + '';
    // row 2
    sOut += 'Award Date:
    '+ aAwardDte + ' Description:
    ' + aDescription + ' ';
    // end of table
    sOut += '';


    // 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 = '';
    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 */



    [/code]

    thanks.
  • yasithlyasithl Posts: 1Questions: 0Answers: 0
    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.
  • allanallan Posts: 63,643Questions: 1Answers: 10,492 Site admin
    edited May 2012
    > 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
  • vikinggoatvikinggoat Posts: 26Questions: 0Answers: 0
    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.

    [code]
    $("#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");
    }
    });
    [/code]
  • allanallan Posts: 63,643Questions: 1Answers: 10,492 Site admin
    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:

    [code]
    $("#parentTable tbody tr").each(function() {
    [/code]

    to:

    [code]
    oTable.$('tr').each(function() {
    [/code]

    This is the result: http://live.datatables.net/eruhof/edit#javascript,html .

    Regards,
    Allan
  • anupamsmauryaanupamsmaurya Posts: 8Questions: 0Answers: 0
    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'.
  • kknitpkknitp Posts: 1Questions: 0Answers: 0
    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
This discussion has been closed.