CODEWITHSUNDEEP
jqueryjqgridsum
jeffery_the_wind
jeffery_the_wind

Reputation: 18188

jqGrid sum a column in JS when column as null and "" values

I found a nice way to sum a jqGrid column in javascript in this post, like this:

var total = $('#grid_id').jqGrid('getCol','col_name',false,'sum');

This is great, but the problem I am having is that the column that I want to sum sometimes has white space instead of a number, and the variable total in the above example returns NaN.

Does anyone know how to tweak this function so that white space just acts like a zero?

Thanks!

** UPDATE **

as Oleg pointed out in the comments, you need to use a formatter in the colModel. Once I put the formatter: 'number' in my colModel, all the whitespace is filled in as zeros and it works! See thi example code:

$("#grid_id").jqGrid({
    url:'ajax_script.php?sql=' + sql1,
    height: 275,
    shrinkToFit: true,
    width: 800,
    datatype: 'xml',
    mtype: 'POST',
    colNames:["Customer","Job", "Sched QNTY Sell","Sched Total Sell"],
    colModel:[
        {name:"Customer",index:"Customer",width:"16"},
        {name:"JobNum",index:"JobNum",width:"16"},
        {name:"Sched Qnty Sell",index:"Sched Qnty Sell",width:"20", formatter: 'number'},
        {name:"Sched Total Sell",index:"Sched Total Sell",width:"20", formatter: 'number'}
    ],
    rowNum:10000,
    sortname: 'Customer',
    sortorder: 'asc',
    viewrecords: true,
    gridview: true,
    caption: 'Scheduled to Bill',
    footerrow : true,
    altRows : true,
    gridComplete: function(){
        $(this).jqGrid('footerData','set',{'Customer':'TOTALS:'});
        var colnames = $(this).jqGrid('getGridParam','colModel');
        for (var i = 2; i < colnames.length; i ++){
            var tot = $(this).jqGrid('getCol',colnames[i]['name'],false,'sum');
            var ob = [];
            ob[colnames[i]['name']] = tot;
            $(this).jqGrid('footerData','set',ob);
        }
    }
})

Upvotes: 3

Views: 5541

Answers (2)

Sergiu
Sergiu

Reputation: 1396

If you don't want to display those zeros on null columns a unformat option can be used:

{ name:"Column Name", index:"Column Index", width:"20", unformat: unformatNullColumn, formatter: 'number' }

function unformatNullColumn(cellvalue, options) {
    return (parseFloat(cellvalue) || 0);
}

It is explained very well in this answer.

Upvotes: 7

Oleg
Oleg

Reputation: 221997

jqGrid first get the data from the cells of the column using either unformatter (if you define a formatter for the column) or just get innerHTML and decode the HTML (the characters "<" can be encoded in the form "<"). Then jqGrid use parseFloat to convert the data to the number. At the end the numbers will be sum up.

I think the bast way will be to trim the data before filling in the grid. If you can't do this on the server side you can use beforeProcessing callback.

Upvotes: 2

Related Questions