jqGrid access cell data while it is being edited

Question

I'm currently using afterSaveCell to handle manually updating some cells in a grid. I have this working fine if the user uses enter to save the currently editing cell.

Unfortunately, if they click or tab out of the cell they are editing directly into another cell I can no longer grab the cell value of the newly edited cell as getCell will only return the html for the input control.

In summary, is there any way to access the value of the cell even while it is being edited?

jQuery(document).ready(function () {
    var mydata = [
        {id:"1", invdate:"2007-10-01",name:"test",  note:"note",  amount:"200.00",tax:"10.00",total:"210.00"},
        {id:"2", invdate:"2007-10-02",name:"test2", note:"note2", amount:"300.00",tax:"20.00",total:"320.00"},
        {id:"3", invdate:"2007-09-01",name:"test3", note:"note3", amount:"400.00",tax:"30.00",total:"430.00"},
        {id:"4", invdate:"2007-10-04",name:"test",  note:"note4", amount:"200.00",tax:"10.00",total:"210.00"},
        {id:"5", invdate:"2007-10-05",name:"test5", note:"note5", amount:"300.00",tax:"20.00",total:"320.00"},
        {id:"6", invdate:"2007-09-06",name:"test",  note:"note6", amount:"400.00",tax:"30.00",total:"430.00"},
        {id:"7", invdate:"2007-10-04",name:"test7", note:"note7", amount:"200.00",tax:"10.00",total:"210.00"},
        {id:"8", invdate:"2007-10-03",name:"test8", note:"note8", amount:"300.00",tax:"20.00",total:"320.00"},
        {id:"9", invdate:"2007-09-01",name:"test",  note:"note9", amount:"400.00",tax:"30.00",total:"430.00"},
        {id:"10",invdate:"2007-09-08",name:"test10",note:"note10",amount:"500.00",tax:"30.00",total:"530.00"},
        {id:"11",invdate:"2007-09-08",name:"test11",note:"note11",amount:"500.00",tax:"30.00",total:"530.00"},
        {id:"12",invdate:"",name:"TOTAL",  note:"",amount:"",tax:"",total:""}
    ];

    var grid = $("#list");

    grid.jqGrid({
        cellsubmit: 'remote',
        cellurl: '/Example/GridSave',
        datatype: "local",
        data: mydata,
        mtype: 'POST',
        colNames: ['Inv No', 'Date', 'Client', 'Amount', 'Tax', 'Total', 'Notes'],
        colModel: [
            { name: 'id', index: 'id', width: 65, sorttype: 'int', hidden: true },
            { name: 'invdate', index: 'invdate', width: 120, align: 'center', formatter: 'date', formatoptions: { newformat: 'd-M-Y' }, sortable: false },
            { name: 'name', index: 'name', editable: true, width: 90, sortable: false },
            { name: 'amount', index: 'amount', editable: true, width: 70, formatter: 'number', align: 'right', sortable: false },
            { name: 'tax', index: 'tax', editable: true, width: 60, formatter: 'number', align: 'right', sortable: false },
            { name: 'total', index: 'total', editable: true, width: 60, formatter: 'number', align: 'right', sortable: false },
            { name: 'note', index: 'note', width: 100, sortable: false }
        ],
        rowNum: 1000,
        pager: '#pager',
        viewrecords: true,
        sortorder: "desc",
        caption: "afterSaveCell Issue",
        height: "100%",
        cellEdit: true,
        gridComplete: function () {
            calculateTotal();
        },
        afterSaveCell: function (rowid, name, val, iRow, iCol) {
            calculateTotal();
        }
    });
});

function calculateTotal() {
    var totalAmount = 0;
    var totalTax = 0;

    var grid = jQuery("#list");

    var ids = grid.jqGrid('getDataIDs');
    for (var i = 0; i < ids.length; i++) {
        var id = ids[i];

        if (grid.jqGrid('getCell', id, 'name') === "TOTAL") {
            grid.jqGrid('setRowData', id, {
                'amount': totalAmount,
                'tax': totalTax,
                'total': totalAmount + totalTax
            });
        }
        else {
            totalAmount += Number(grid.jqGrid('getCell', id, 'amount'));
            totalTax += Number(grid.jqGrid('getCell', id, 'tax'));
        }
    }
}

Thanks in advance!

Wintertime

If you need save current cell on TAB you will be overwrite the default implementation of nextCell and prevCell which will be called on TAB or Shift-TAB. See the code of the functions here: github.com/tonytomov/jqGrid/blob/master/js/… – Sherburne

Thanks @Oleg, that would definitely help the TAB issue, but still leaves the issue when blurring out onto another cell in the same grid. – Wintertime

You are right about the blurring. Probably the way to bind editing element to 'blur' or even 'focusout' event could solve your problem. You can use dataEvents (see #4407773) in the editoptions to make binding to the 'focusout' event and call "saveCell". You can additionally use beforeEditCell to save last iRow,iCol needed as parameters in the saveCell. – Sherburne

I reread your question one more time and I am not more sure that I understood you correct before. It seems me more that you want just have access to the cell contain inside of the special event. Without having the code which describes more clear your problem I will probably unable to help you. – Sherburne

I tried to create an example using a clientArray but it will not reproduce the error. It seems that the delay when it sends the new cell data to the server is causing the input box to be created before the afterSaveCell is called. In the clientArray example, with no post to server, the afterSaveCell is called before the newly edited cell data converts to an input. I'll need a bit more time to create a proper example. – Wintertime

@Oleg, I've created an example that you can see Here. If you try to edit Amount or Tax columns and then click into the other after editing, the newly switched to total will switch to 0.00 instead of the proper total. This is happening because the cell you are now editing is returning the input control to getCell instead of the cell value. This is happening due to the delay with a remote cellsubmit. Using a local clientArray does not cause this problem. – Wintertime

@Shawn: Now I understand your problem good. I hope I'll suggest the solution to the problem which I'll write as an answer on your question. – Sherburne

@Shawn: It seems to me that the problem of accessing of cell which are in the editing mode is common problem which can has other people. It would be nice if you modify the text of your question with the information from your comments to make other people easier to read your question. – Sherburne

@Shawn: I made some small changes in the code of demos to use no global functions. As known the usage of global function is slower and can follow to potential naming conflicts. – Sherburne

Answer 1

I see two problems in your code. The first one is more cosmetic, but the correct solution can simplify many things in the future.

The first problem is that you add manual the "TOTAL" row as a part of grid data and calculate the values in the row inside calculateTotal function. The better way is to use footerrow:true option, which add additional row at the bottom of grid which will be not mixed with the grid data. For server based data you can use userdata part of JSON or XML response from the server and use userDataOnFooter:true additionally to till the data from the userData jqGrid parameter to the footer row. In case of "local" datatype one can use footerData method to set (or get) the data in the footer. Additionally the method getCol can be used co calculate the sum of elements in the column. So your version of calculateTotal function can be rewritten as

var grid = $("#list");
var calculateTotal = function() {
    var totalAmount = grid.jqGrid('getCol','amount',false,'sum'),
        totalTax = grid.jqGrid('getCol','tax',false,'sum');
    grid.jqGrid('footerData','set',{name:'TOTAL',amount:totalAmount,tax:totalTax});
}

Now to your main problem. You use cell edit mode. If the function calculateTotal (your original or my simplified version) will be called at the time when one from the cells of the 'amount' or 'tax' are in the editing mode, the calculateTotal will be read HTML fragment with <input> element instead of the string with the number and the calculation will failed.

I created the small demo which call calculateTotal every second. So if you click on any cell from the 'amount' or 'tax' column you will see that in the footer row 0 will be displayed as the sum. So the demo having cellsubmit:'clientArray' has the same problem as in your original code with cellsubmit:'remote'.

To solve the problem one can use data parameter of jqGrid during the sum calculation:

var grid = $("#list");
var calculateTotal = function() {
    var gridData = grid.jqGrid('getGridParam','data'),
        i=0,totalAmount=0,totalTax=0;
    for (;i<gridData.length;i++) {
        var rowData = gridData[i];
        totalAmount += Number(rowData.amount);
        totalTax += Number(rowData.tax);
    }
    grid.jqGrid('footerData','set',{name:'TOTAL',amount:totalAmount,tax:totalTax});
}

The corresponding fixed demo you will find here. In your final code you can remove

setInterval(calculateTotal, 1000);

which I used for demonstration purpose only and refresh the footer in the afterSaveCell event handler only.

UPDATED: If you work with remote data you can not use data parameter. So one have to get data from the <input> element if needed. I created one more demo which demonstrate how one can do this. The code of calculateTotal will be longer:

var getColumnIndexByName = function(grid,columnName) {
    var cm = grid.jqGrid('getGridParam','colModel');
    for (var i=0,l=cm.length; i<l; i++) {
        if (cm[i].name===columnName) {
            return i; // return the index
        }
    }
    return -1;
},
getTextFromCell = function(cellNode) {
    return cellNode.childNodes[0].nodeName === "INPUT"?
           cellNode.childNodes[0].value:
           cellNode.textContent || cellNode.innerText;
},
calculateTotal = function() {
    var totalAmount = 0, totalTax = 0,
        i=getColumnIndexByName(grid,'amount');
    $("tbody > tr.jqgrow > td:nth-child("+(i+1)+")",grid[0]).each(function() {
        totalAmount += Number(getTextFromCell(this));
    });

    i=getColumnIndexByName(grid,'tax');
    $("tbody > tr.jqgrow > td:nth-child("+(i+1)+")",grid[0]).each(function() {
        totalTax += Number(getTextFromCell(this));
    });

    grid.jqGrid('footerData','set',{name:'TOTAL',amount:totalAmount,tax:totalTax});
};

Sherburne

It appears that the jqGrid('getGridParam','data') only works with local data. Using it with my remote json data is returning an empty string. – Wintertime

@Shawn: Yes it is correct, but your demo has used local data. If you need use remote data, you have to examine whether the data from getCell are HTML fragment with <input> or not. Instead of enumeration of though row ids you can use more quick jQuery way which I described #5011261. To make you easy I created one more demo ok-soft-gmbh.com/jqGrid/CellEditAfterSaveCellProblem1.htm which do what you need. – Sherburne

@Oleg: great answer! I have one more question - I have inisde my row a select type cell - how can I sum up the total amount only when the select value is the first value inside of it? I mean that I want to calculate the sum depending on the value inside of the select cell. thank's.. – Longsuffering

@user590586: You will have to extend the code of getTextFromCell. I don't really understand how you want to calculate the sum of select elements because select contains typically texts and not integers. – Sherburne

@Oleg: the amount is not in the select cell, It is in another cell. what I want to do is sum this amount only if I have a specific value inside the select cell. do you understand what I'm trying to do? Is It possible? – Longsuffering

@user590586: It would be better if you provide the grid with the corresponding data and formulate the question where you descripde exactly which filter criterium you need use. – Sherburne

@Oleg: This is the select cell: {name:'status',index:'status',width:90,sorttype:"int",editable:true,edittype:"select",formatter:'select',editoptions:{value:"1:Confirmed ;2:Open ; 3:Rejected",dataInit: function(elem){$(elem).widt(90);}}}, I want to calculate total amount cell only if the value inside the select is "Confirmed". (the amount cell is like in the question). Do you need any more data? – Longsuffering

@user590586: I can only repeat, that you should open new question and post all the information. Then I would have enough place to write the answer and other people with the close requirements will be able to find my answer. – Sherburne

@user590586: OK! I will try to make the new demo and post it as the answer on the question. – Sherburne

Answer 2

Thanks oleg the solution for me was applying and changing what you put

var ListTabla="ListFormatos";
var request="../../tabla_general/tabla_general_mantenimiento.php";

var getColumnIndexByName = function(grid,columnName) {
var cm = $("#"+grid).jqGrid('getGridParam','colModel');
for (var i=0,l=cm.length; i<l; i++) {
    if (cm[i].name===columnName) {
        return i; // return the index
    }
}
return -1;
},

getTextFromCell = function(cellNode) {
return cellNode.childNodes[0].nodeName === "INPUT"?
       cellNode.childNodes[0].value:
       cellNode.textContent || cellNode.innerText;
},

calculateTotal = function(grid) {   

total_hpr_suebas = retorna_suma('hpr_suebas',grid);
total_hpr_asifam = retorna_suma('hpr_asifam',grid);
total_hpr_bashpr = retorna_suma('hpr_bashpr',grid);
total_hpr_remcom = retorna_suma('hpr_remcom',grid);
total_hpr_basmes = retorna_suma('hpr_basmes',grid);
total_hpr_provcts = retorna_suma('hpr_provcts',grid);
total_hpr_provgrat = retorna_suma('hpr_provgrat',grid);
total_hpr_provvac=retorna_suma('hpr_provvac',grid);

    $("#"+grid).jqGrid('footerData','set',{sec_detsec:'TOTAL',hpr_suebas:total_hpr_suebas,hpr_asifam:total_hpr_asifam,hpr_bashpr:total_hpr_bashpr,hpr_remcom:total_hpr_remcom,hpr_basmes:total_hpr_basmes,hpr_provcts:total_hpr_provcts,hpr_provgrat:total_hpr_provgrat,hpr_provvac:total_hpr_provvac});
};

retorna_suma=function(campo,grid)
{
    total=0;
    i=getColumnIndexByName(grid,campo);
    $("tbody > tr.jqgrow > td:nth-child("+(i+1)+")",$("#"+grid)[0]).each(function() {
        total+= Number(getTextFromCell(this));
    });
    return total;
}

function fn_jqgrid_history_provisiones_trabajador(tra_idtra,fecha_inicio,fecha_fin)
{
    jQuery("#"+ListTabla).jqGrid({
                    url:request+"?action=search_history_provisiones_trabajador&tra_idtra="+tra_idtra+"&fecha_inicio="+fecha_inicio+"&fecha_fin="+fecha_fin,
                    async: false,
                    datatype: 'json',
                    colNames:['','ID','SECTOR','BASICO','ASIG. FAM','DIAS','BASE','REM. COMP.','BASE MES','P.CTS','P.GRAT.','P.VAC.','MES','ANIO','PORC.','SAL.VAC.','SAL.GRAT.'],
                    colModel:[
                         {name:'act', index:'act', width:50, resizable:true, align:"center",hidden:true},
                        {name:'id', index:'id', width:50, resizable:true, align:"center",hidden:true},
                        {name:'sec_detsec', index:'sec_detsec', width:80},
                        {name:'hpr_suebas', index:'hpr_suebas', width:60},
                        {name:'hpr_asifam', index:'hpr_asifam', width:50},
                        {name:'hpr_numdia', index:'hpr_numdia', width:30},
                        {name:'hpr_bashpr',index:'hpr_bashpr', width:60},
                        {name:'hpr_remcom,',index:'hpr_remcom', width:60},
                        {name:'hpr_basmes', index:'hpr_basmes', width:60},
                        {name:'hpr_provcts', index:'hpr_provcts', width:60},
                        {name:'hpr_provgrat', index:'hpr_provgrat', width:60},
                        {name:'hpr_provvac', index:'hpr_provvac', width:60},
                        {name:'hpr_meshpr', index:'hpr_meshpr', width:30},
                        {name:'hpr_aniohpr,',index:'hpr_aniohpr', width:30},
                        {name:'hpr_salpor', index:'hpr_salpor', width:50},
                        {name:'hpr_salval_vac', index:'hpr_salval_vac', width:50},
                        {name:'hpr_salval_grat', index:'hpr_salval_grat', width:50}
                    ],
                    pager: '#paginacion',
                    rowNum:10,
                    rowList:[10,20,30],
                    sortname: 'ID',
                    ondblClickRow:function(rowid, iRow, iCol, e)
                    {
                                obj=jQuery("#"+ListTabla).jqGrid('getRowData',rowid);
                    }
                    ,
                    sortorder: 'desc',
                    editurl:request,
                    viewrecords: true,
                    caption: 'Provisiones',
                    //rownumbers: true,
                     height: 250,
                    rowheight: 280,
                    footerrow : true,
                    gridComplete: function () {
                        calculateTotal(ListTabla);
                },
                    afterSaveCell: function (rowid, name, val, iRow, iCol) {
                        //calculateTotal(ListTabla);
                    }
                    }).navGrid('#paginacion',{add:false,edit:false, del:false});

                        jQuery("#"+ListTabla).jqGrid('bindKeys', {"onEnter":function( rowid ) {
                                                                    obj=jQuery("#"+ListTabla).jqGrid('getRowData',rowid);
                                                                    } } );  

                 }

Garboard

Answer 3

If you just want to recalculate total number, you can use trigger to reload the grid in afterSaveCell event. Like this:

afterSaveCell: function (rowid, name, val, iRow, iCol) 

        {
          jQuery("#list11").jqGrid('setGridParam',{datatype:'local',loadonce:true}).trigger('reloadGrid');
        }

Anthologize