Sum of selected rows at footer

Sum of selected rows at footer

shatrughanshatrughan Posts: 85Questions: 15Answers: 0

Hi, Using the Editor API, I'm doing sum of a column say Total_Allocation but for selected rows of the table only. Without selected rows, I used following code and which works perfectly ::

        Total_Allocation = api.column(4 , {filter:'applied'}).data()
                            .reduce( function (a, b) {
                              return intVal(a) + intVal(b);
                            }, 0 );

But, it does not filter the selected rows. Anyone may please suggest necessary chnages in code.

Regards,
Shatrughan

Answers

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

    There are lots of threads asking the same question, like this thread. You will need to change from using column() to rows() to get the selected rows.

    Kevin

  • shatrughanshatrughan Posts: 85Questions: 15Answers: 0

    As per your suggestion, i used following code

    $( api.column( 4 ).footer() ).html(
                                      api.rows( {selected: true} ).data().pluck(4).sum()
                                    );
    

    But, to no avail as it does not sum the selected row's data. Output attached.

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736
    edited July 2021 Answer ✓

    It could be pluck(4). Is your row data arrays or objects ( objects use columns.data )? If objects then change 4 to the correct data property. Your code works in this example:
    http://live.datatables.net/camiwune/1/edit

    If you still need help please provide a link to your page or a test case replicating the issue. You can update my test case.
    https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

    Kevin

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736
    edited July 2021 Answer ✓

    I realized that the problem might be that you aren't redrawing the table when the row is selected. See my example using the select event to call draw().

    Kevin

  • shatrughanshatrughan Posts: 85Questions: 15Answers: 0

    Thanks, it works !

  • shatrughanshatrughan Posts: 85Questions: 15Answers: 0

    @kthorngren
    Still having the same issue... I have sent you a PM. Please see.
    Regards,
    Shatrughan

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

    I looked at your web page and it doesn't look like you followed my suggestions. You are using columns.data so your data structure is objects. You have .pluck(6) which is array notation. You need to use the property name of the column. For example .pluck("Total_Allocation.Total_Allocation").

    It doesn't look like you added the select event, like I suggested, to keep the footerCallback updated by calling draw().

    Kevin

  • shatrughanshatrughan Posts: 85Questions: 15Answers: 0

    I have now used

      select: true,
      "footerCallback": function ( row, data, start, end, display ) {
       var api = this.api(), data;
         $( api.column( 6 ).footer() ).html(
           api.rows( {selected: true} ).data().pluck("Total_Allocation.Total_Allocation").sum()
         );
       }
    

    and also included

    table.on('select', function () {
                         table.draw();
                       });
    

    But to no avail as I am still getting no value on row select.

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

    Looking at the pluck() examples you will need to use pluck() twice to get nested data. For example:

    api.rows( {selected: true} ).data().pluck("Total_Allocation").pluck("Total_Allocation").sum()
    
    

    Kevin

  • shatrughanshatrughan Posts: 85Questions: 15Answers: 0

    Cheers..It works ! You are a life saver.
    Heartedly Thanks.

  • shatrughanshatrughan Posts: 85Questions: 15Answers: 0

    Seeking for a minor suggestion also...The footer is somewhat displaced vis-a-vis header columns i.e. The footer remains slighly right to the columns and table. Please help to overcome this.

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

    Not sure what the problem is. Your table is defined like this:

                  <table id="rtc" class="display table-bordered nowrap" align="left" >
                    <thead>
                      <tr id="table_header" class="hidden tbl_head">
                        <th >Sr</th>
    ....
    <th >ID</th>
    
                      </tr>
                    </thead>
                    <tfoot>
                      <tr class="tbl_foot">
                        <th colspan="6">Total</th>
                         <th ></th>
     <th ></th>
    ....
     <th ></th>
    
    
                      </tr>
                    </tfoot>
                  </table>
    

    I'm not sure how Datatables handles footers with colspan. You can try removing that and having one th per column.

    You can try adding style="width:100%" to your table tag as shown in this example.

    You can PM your link to one of the developers, @allan or @colin, to see if they can debug the alignment.

    Kevin

  • shatrughanshatrughan Posts: 85Questions: 15Answers: 0

    The sum of selected rows is not subtracted upon de-selecting a row. What needs to be changed in above code. Please guide.

    Shatrughan

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736
    edited August 2021

    Use the deselect event like you are using the select event.

    Kevin

  • shatrughanshatrughan Posts: 85Questions: 15Answers: 0

    I can't figure out how to use deselect with API. Used the following code with no luck.

    var sum= api.rows().data().pluck("Total_Allocation").pluck("Total_Allocation").sum()
           var selected= api.rows({selected: true} ).data().pluck("Total_Allocation").pluck("Total_Allocation").sum()
            var deselected= api.rows({deselected: true} ).data().pluck("Total_Allocation").pluck("Total_Allocation").sum()
             $( api.column( 6 ).footer() ).html(
                            sum-deselected  
                             );
    

    Please look into and advise necessary changes.

    Regards
    Shatrughan

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

    In the example we have the select event, like this:

      table.on('select', function () {
        table.draw();
      })
    

    All you need to do is do the same using the deselect event, like this:

      table.on('deselect', function () {
        table.draw();
      })
    

    You don't need line 3 in your last code snippet.

    Kevin

  • shatrughanshatrughan Posts: 85Questions: 15Answers: 0
    edited August 2021

    Works Perfectly ! Thanks a lot.

  • renzosilvrenzosilv Posts: 11Questions: 0Answers: 0

    I know this is a bit outdated but it was a conversation on exactly what I needed - one question. When I select on a page other than page 1, the draw brings me back to page 1 which is a bit of an annoyance. Is there a trigger to keep the page where it is?

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

    @renzosilv The draw() docs explain all the available options for redrawing the table. You will want to use false, like this table.draw( false );.

    Kevin

Sign In or Register to comment.