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!

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: https://github.com/tonytomov/jqGrid/blob/master/js/grid.celledit.js#L305 — Oleg, Feb 25 '11 at 20:37
Thanks @Oleg, that would definitely help the TAB issue, but still leaves the issue when blurring out onto another cell in the same grid. — Shawn, Feb 25 '11 at 21:00
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 http://stackoverflow.com/questions/4407273/jqgrid-retrieve-data-of-cell-and-manipulate-it/4407958#4407958) 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`. — Oleg, Feb 25 '11 at 22:11
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. — Oleg, Feb 25 '11 at 23:21
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. — Shawn, Feb 26 '11 at 19:53
@Oleg, I've created an example that you can see [Here](http://bit.ly/hBub2M). 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. — Shawn, Feb 26 '11 at 20:14
@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. — Oleg, Feb 26 '11 at 20:33
@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. — Oleg, Feb 26 '11 at 22:15
@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. — Oleg, Feb 27 '11 at 00:03

Oleg · Accepted Answer · 2011-02-27T01:01:17.633

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});
};

It appears that the `jqGrid('getGridParam','data')` only works with local data. Using it with my remote json data is returning an empty string. — Shawn, Feb 27 '11 at 00:13
@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 `` or not. Instead of enumeration of though row ids you can use more quick jQuery way which I described http://stackoverflow.com/questions/5010761/linking-from-a-column-value-in-jqgrid-to-a-new-page-using-get/5017528#5017528. To make you easy I created one more demo http://www.ok-soft-gmbh.com/jqGrid/CellEditAfterSaveCellProblem1.htm which do what you need. — Oleg, Feb 27 '11 at 00:55
@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.. — user590586, May 15 '11 at 09:40
@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. — Oleg, May 15 '11 at 09:59
@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? — user590586, May 15 '11 at 10:09
@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. — Oleg, May 15 '11 at 10:27
@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? — user590586, May 15 '11 at 10:34
@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**. — Oleg, May 15 '11 at 11:09
@user590586: OK! I will try to make the new demo and post it as the answer on the question. — Oleg, May 15 '11 at 11:49

score 1 · Answer 2 · answered May 23 '12 at 17:54

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);
                                                                    } } );  

                 }

score 1 · Answer 3 · edited Nov 06 '12 at 07:48

1

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

edited Nov 06 '12 at 07:48

Andro Selva

53,910
52
193
240

answered Nov 06 '12 at 07:31

Julia

11
1

jqGrid access cell data while it is being edited

3 Answers3

Linked