How to compute a column value while editing in jqgrid

Question

I have a supposedly common problem to solve (done easily with other grid controls I'm familiar with). In jqgrid, i'm quite stumped. I have a jqgrid with inline editing enabled. I would like to add some scripting so that when editing a row (or adding a new row), the value of ColumnC is automatically computed as ColumnA * ColumnB as default. The user can change the values in any column at any time. I want this value to be computed as soon as the user enters it and not wait till the data is saved.

My approach so far has been to attach a dataEvent of type "change" to ColumnA and ColumnB -

dataEvents: [
              { type: 'change', fn: function(e) {
                   var rowid = $("#myGrid").jqGrid('getGridParam','selrow');
                   var rowData = $("#myGrid").getRowData(rowid); 
                   var cell1 = rowData['ColumnA'];
                   var cell2 = rowData['ColumnB'];
                   var newValue = cell1 * cell2;
                   $("#myGrid").jqGrid('setCell', rowid, 'ColumnC', newValue);
                   } 
               },
          ]

Obviously, cell1 & cell2 don't actually return the value but the whole cell content as already discovered by other users here How to get a jqGrid cell value. The only way to get a cell value seems to be to use a custom regex user function that strips out that value. Apart from that, is there a better/alternate way to achieve what I need? Something as simple as jqGrid - How to calculated column to jqgrid? though obviously that won't cut it for me since it will only displaying data and user cannot update it.

Any help would be appreciated.

UPDATE: After guidance from Oleg, I was able to extend getTextFromCell to support what I need.

            function getTextFromCell(cellNode) {
            var cellValue;
            //check for all INPUT types
            if (cellNode.childNodes[0].nodeName == "INPUT") {              
                //special case for handling checkbox
                if (cellNode.childNodes[0].type == "checkbox") {            
                    cellValue = cellNode.childNodes[0].checked.toString();  
                }
                else {
                    //catch-all for all other inputs - text/integer/amount etc
                    cellValue = cellNode.childNodes[0].value;               
                }
            }
            //check for dropdown list
            else if (cellNode.childNodes[0].nodeName == "SELECT") {         
                var newCell = $("select option:selected", cellNode);
                cellValue = newCell[0].value;
            }
            return cellValue;             
        }

        function getCellNodeFromRow(grid,rowId,cellName) {
            var i = getColumnIndexByName(grid, cellName);
            var cellValue;
            //find the row first
           $("tbody > tr.jqgrow", grid[0]).each(function() {
                //The "Id" column in my grid is at index 2...
                var idcell = $("td:nth-child(2)", this); 
                var currRowId = getTextFromCell(idcell[0])
                if (currRowId == rowId) {
                    cellValue = getTextFromCell($("td:nth-child(" + (i + 1) + ")", this)[0]);
                    return false;
                }
            });
            return cellValue;
        }

The code in getCellNodeFromRow is not the most efficient. There is a .each() loop for find the matching row node. I can see this being slow when the grid has thousands of rows. Is there a better/more efficient way to find the row node? I have the row Id already.

if you solve this issue can you post the code, please. I tried to create row sum column based on Oleg sample but failed. How to invoke change event ? input text element does not have change, probably blur should used. Oleg code also fails if comma is used as decimal separator and spaces are used as thousand separator — Andrus, Sep 08 '11 at 08:18
@Andrus: I've updated to add more code based on Oleg's idea. For comma and decimals, you can parse them out as [shown in this example](http://stackoverflow.com/questions/559112/how-to-convert-a-currency-string-to-a-double-with-jquery-or-javascript). I just needed string comparison for my stuff. Hope this helps. — EclecticMo, Sep 28 '11 at 17:22
thank you. I dont understand how you set column value (eq. quantity*price in readonly total column) in row being edited as asked in your question. Your update shows getting value, not updating column value. http://stackoverflow.com/questions/3768906/jqgrid-how-to-calculated-column-to-jqgrid shows simple way to set value but this does not work: formatter is not evaluated if quentity or price in row has changed — Andrus, Sep 28 '11 at 18:25

score 1 · Accepted Answer · edited May 23 '17 at 12:15

1

Look at the demo from the answer. It uses cell editing, but the same technique work for inline editing too. Just click on any cell in the "Amount" column and modify the numerical value. You will see that the value in the "Total" row (footer) will be changed dynamically during the cell is still in the editing mode. I think it is what you need.

edited May 23 '17 at 12:15

Community

1
1

answered Sep 06 '11 at 21:40

Oleg

220,925
34
403
798

Thanks for the direction. I made some progress but I got stumped on some datatypes. Using 'getCell' only works for text input type fields. Fields like drop-downs (editType:'select') or currency (formatter:'currency') return an empty value. Any ideas? – EclecticMo Sep 26 '11 at 22:12
@Mo Sachdeva: You should expand the code of `getTextFromCell` method. [The answer](http://stackoverflow.com/questions/5121350/jqgrid-access-cell-data-while-it-is-being-edited/5129908#5129908) needs only "INPUT" node type. You can examine `nodeName` for another values like "SELECT" and get the current selected option. You can of course use unformater to get the value of currency. My main advice was: **you have to get the data manually** with respect of DOM or jQuery. The methods like `getCell` could not help you. – Oleg Sep 26 '11 at 22:15
Thanks for the help! I think I got it working for myself now. The row node lookup I'm using .each() is inefficient I think though. Any alternatives to that? – EclecticMo Sep 28 '11 at 14:23
@Mo Sachdeva: From the performance point of view the way to enumerate the rows which I describe [here](http://stackoverflow.com/questions/5664587/jqgrid-load-large-data-set-without-pagination/5690583#5690583) or [here](http://stackoverflow.com/questions/7080859/jqgrid-iterate-over-the-grid-data-in-a-subgrid/7084920#7084920) is a little better. Moreover you can access the data from the column with respect of `cells` property of `$('#grid')[0].rows[iRow].cells[iCol]`. – Oleg Sep 28 '11 at 14:40

score 0 · Answer 2 · answered Sep 29 '11 at 06:02

    you can achieve this using onCellSelect event of jqgrid as below



//global section
var columnA;
var ColumnB;
var ColumnC;
var currentRow;

//

onCellSelect: function (rowid, iCol, aData) {

                currentRow = rowid;
                var ColumnA = $('#grid').getCell(rowid, 'MyCol');
                var ColumnB = $('#grid').getCell(rowid, 'MyCol');

                 $("#grid").jqGrid('editRow', rowid, true );
                $("#myMenu").hide();
                if (rowid && rowid !== lastsel) {
                    if (lastsel) jQuery('#grid').jqGrid('restoreRow', lastsel);
$("#grid").jqGrid('editRow', rowid, true );
                    lastsel = rowid;
                }
                else if (rowid && rowid === lastsel)
                { $("#grid").jqGrid('editRow', rowid, true ); }

               //if it is in editable mode 
               // when you view the html using firebug it will be like the cell id change to                       
               //format like "rowid_ColumnName"
               $('#' + currentRow + '_ColumnC').val(ColumnA*ColumnB);

               // or you can use achieve this using following jqgrid method at 
               //appropriate  place
               $("#myGrid").jqGrid('setCell', rowid, 'ColumnC', ColumnA*ColumnB);



}

onCellSelect is not available when the cell is in edit mode. I need this feature since I need to compute when editing a row. — EclecticMo, Sep 29 '11 at 14:34
you can achieve this if row is already in editable mode using code like below: dataEvents: [{ type: 'blur', fn: function (e) { var cap, percent, pduThreshhold; cap = $('#' + currentRow + '_UpsCapacity').val(); percent = $('#' + currentRow + '_ThresholdPercent'); pduThreshhold = Math.round(cap * (percent / 100)); $('#' + currentRow + '_UpsThreshold').empty().val(pduThreshhold); } } — aamir sajjad, Sep 30 '11 at 06:37
and if you are using form editing all the code will remain same except change the id in dataevent to like $("ColumnA").Val() — aamir sajjad, Sep 30 '11 at 06:46

How to compute a column value while editing in jqgrid

2 Answers2