Update running totals in column on filter event

Update running totals in column on filter event

STScI WASABI TeamSTScI WASABI Team Posts: 5Questions: 2Answers: 0
edited November 2018 in Free community support

I have a column in a table that calculates a running total in a table:

"render": function ( data, type, row, meta ) {  
    if (meta.row == 0) {
        requestedRunningTotal = Number(row[meta.col - 1]);
    } else {
        requestedRunningTotal = requestedRunningTotal + Number(row[meta.col - 1]);
    }

    return requestedRunningTotal;
}

It works fine but does not recompute if the table is filtered (values being filtered are in another column). Is there a way to cause the column or cells to recompute using the displayed filtered rows, i.e., show the running total of the filtered values?

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

This question has accepted answers - jump to:

Answers

  • colincolin Posts: 3,280Questions: 0Answers: 584
    edited November 2018

    Hi @STScI WASABI Team ,

    Yep, the columns.render is only called when the row is first drawn. You want to do running totals in footerCallback, as in this example here,

    Cheers,

    Colin

  • STScI WASABI TeamSTScI WASABI Team Posts: 5Questions: 2Answers: 0

    I don't think that is what I am looking for. The running total is not written in the footer, it is written in the column to the right of the values being totaled, so you can see the total at any given point in the row listing.

    I might be able to calculate/write those values with footerCallback (or headerCallback or rowCallback for that matter) but I don't see those functions being able to access the meta object (which I use to make the column reference dynamic). Unless the column reference is dynamic, hiding columns will break the running total I am trying to generate. Is there another way to get the column reference in those callbacks?

  • colincolin Posts: 3,280Questions: 0Answers: 584
    edited November 2018 Answer ✓

    Hi @STScI WASABI Team ,

    The meta data for all columns, visible or otherwise, is always available through the DataTables API so it shouldn't be a problem. You would use columns().data().

    This example here may be useful - it's not related to your problem, it shows which cells in a column are duplicated - but it will give you a taste of how you can access all columns in rowCallback.

    Hope that helps,

    Cheers,

    Colin

  • allanallan Posts: 48,657Questions: 1Answers: 7,068 Site admin
    Answer ✓

    A running total is actually a little bit tricky in DataTables at the moment, as each row is treated independently from the others (i.e. they can be rearranged by ordering, or removed by search). Using a rendering method is not going to be the way to do it I'm afraid.

    I think there are two options:

    1. Use rowCallback as Colin suggested
    2. Update the DOM directly like in this example.

    I'd use option 1 if you want a running total per page, but option 2 if you want to sum data across multiple pages. I can put together an example of that for you if you like?

    Allan

  • STScI WASABI TeamSTScI WASABI Team Posts: 5Questions: 2Answers: 0

    I got it working with rowCallback as suggested, still a noob with some of the DataTables functionality so sorry for that.

    I used external variables (requestedRunningTotal, allocatedRunningTotal) to hold the totals as the rows are generated. It seems to work pretty well, though certain filters on another column cause the running totals to calculate incorrectly. I need to debug that, I don't think that is related to rowCallback.

    "rowCallback": function( row, data, displayNum, displayIndex, dataIndex ) {
      if (!$(row).hasClass("triaged")) {
        if (dataIndex == 0) {
          requestedRunningTotal = Number(data["proposal-requested"]);
          allocatedRunningTotal = Number(data["proposal-allocation"]);
        } else {
          requestedRunningTotal = requestedRunningTotal + Number(data["proposal-requested"]);
          allocatedRunningTotal = allocatedRunningTotal + Number(data["proposal-allocation"]);                      
        }
    
        $('td.proposal-requested-total',row).html(requestedRunningTotal);
    
        if (allocatedRunningTotal > allocation) {
          $('td.proposal-allocation-total',row).html('<span class="hilitered">' 
          + allocatedRunningTotal + '&#160;<i class="fas fa-exclamation-triangle"></i></span>');
        } else {
          $('td.proposal-allocation-total',row).html(allocatedRunningTotal);
        }
    
      } else {
        $('td.proposal-requested-total',row).html("n/a");
        $('td.proposal-allocation',row).html("n/a");
        $('td.proposal-allocation-total',row).html("n/a");
      }
    }
    
Sign In or Register to comment.