javascript - jqgrid change values of editable cell on change of another cell (dependent columns)

Question

This is the grid.

My discount column has an autocomplete which has values coming from another json variable.

This is my json array where the data for discount is coming from.

[
  {"id":0.56,"label":"Adams Rite Less 50/12","value":"Adams Rite Less 50/12"},
  {"id":0.44,"label":"ASSA Less 44","value":"ASSA Less 44"},
  {"id":0.603,"label":"BARON ACCESSORIES AND STICKS Less 60.3","value":"BARON ACCESSORIES AND STICKS Less 60.3"},
  {"id":0.704,"label":"BARON STD AND CUSTOM DOORS Less 70.4","value":"BARON STD AND CUSTOM DOORS Less 70.4"},
  {"id":0.617,"label":"BARON STD AND CUSTOM FRAMES Less 61.7","value":"BARON STD AND CUSTOM FRAMES Less 61.7"},
  {"id":0.704,"label":"BARON STD PALLET DOORS Less 70.4","value":"BARON STD PALLET DOORS Less 70.4"},
  {"id":0.145,"label":"Bobrick 10/5","value":"Bobrick 10/5"},
  {"id":0.6175,"label":"BREMNER HMD","value":"BREMNER HMD"},
  {"id":0.6073,"label":"BREMNER HMF","value":"BREMNER HMF"},
  {"id":0.44,"label":"Eff Eff Less 44","value":"Eff Eff Less 44"}
]

What I would like to do is

(1)Upon changing the value of discount the Cost column should change according to this formula

new Cost = (1-0.145)*List Price Cell's value (in this case 999)

The value 0.145 is coming from the id column of the json.

(2)Upon changing the value of discount the ExtCost column should change according to this.

New Ext Cost = Quantity * new Cost

Quantity is the cell on the extreme left.

Here is my grid related code.

    var lastsel2;
    jQuery(document).ready(function(){
        var autocompleteSource;
        $.getJSON("{{ asset('app_dev.php/GetDiscount') }}", function(json){
            autocompleteSource = json;
        });
        var cont;
        jQuery("#list").jqGrid({
            url: "{{ asset('/app_dev.php/_thrace-datagrid/data/view_PO_details') }}",
            postData: {
                masterGridRowId: {{ editid }}
            },
            datatype: "json",
            mtype: 'POST',
            colNames: ['', 'Released', 'Attachments', 'Quantity', 'Received', 'Item #', 'Vendor Item #', 'List Price', 'Discount', 'Cost', 'ExtCost', 'Lead Time', 'System Key', 'PO Item Note', 'PO Required Date', 'Confirm #','Confirm Date', 'VQ #', 'Reference', 'VQ Ref', 'UOM', 'Type', 'Last Update', 'Updated By', 'FSC', 'Door #', 'Queue', 'RTE#'],
            colModel: [
                {
                    name: "POD_UISelected",
                    index: "o.POD_UISelected",
                    editable: true,
                    edittype: 'checkbox',
                    editoptions: {value: "True:False"},
                    formatter: "checkbox",
                    formatoptions: {disabled: false},
                    align: 'center',
                    jsonmap: "cell.0",
                    width: '70'
                },
                {
                    name: "POD_Released",
                    index: "o.POD_Released",
                    editable: true,
                    edittype: 'checkbox',
                    editoptions: {value: "Yes:No"},
                    formatter: "checkbox",
                    formatoptions: {disabled: false},
                    align: 'center',
                    jsonmap: "cell.1",
                    width: '70'
                },
                {
                    name: "I_LinkHasFiles",
                    index: "o.I_LinkHasFiles",
                    editable: false,
                    edittype: 'checkbox',
                    editoptions: {value: "True:False"},
                    formatter: "checkbox",
                    formatoptions: {disabled: true},
                    align: 'center',
                    jsonmap: "cell.2",
                    width: '70'
                },
                {
                    name: "POD_OrderQty",
                    index: "o.POD_OrderQty",
                    editable: true,
                    align: 'center',
                    jsonmap: "cell.3",
                    width: '100'
                },
                {
                    name: "POD_QtyReceived",
                    index: "o.POD_QtyReceived",
                    editable: true,
                    align: 'center',
                    jsonmap: "cell.4",
                    width: '100'
                },
                {name: "POD_ItemNumID #", index: "o.POD_ItemNumID", editable: false, align: 'center', jsonmap: "cell.5"},
                {
                    name: "POD_VendorItemNum",
                    index: "o.POD_VendorItemNum",
                    editable: false,
                    align: 'center',
                    jsonmap: "cell.6"
                },
                {
                    name: "POD_VendorListPrice",
                    index: "o.POD_VendorListPrice",
                    editable: true,
                    align: 'center',
                    formatter: 'currency',
                    formatoptions: {prefix: '$', suffix: '', thousandsSeparator: ','},
                    jsonmap: "cell.7"
                },
                {
                    name: "POD_VendorDiscount",
                    index: "o.POD_VendorDiscount",
                    editable: true,
                    jsonmap: "cell.8",
                    editoptions: {
                       dataInit: function(elem) {
                           var $self = $(this), // save the reference to the grid
                                   $elem = $(elem); // save the reference to <input>
                            $(elem).autocomplete({
                                source: autocompleteSource,
                                select: function (event, ui) {
                                    var $tr = $elem.closest("tr.jqgrow"), newCost, rowid = $tr.attr("id"),
                                            orderQty = parseFloat($tr.find("input[name=POD_OrderQty]").val()),
                                            listPrice = parseFloat($tr.find("input[name=POD_VendorListPrice]").val());
                                    if (ui.item) {
                                        console.log(orderQty);
                                        console.log(listPrice);
                                        newCost = (1 - parseFloat(ui.item.id)) * listPrice;
                                        $self.jqGrid("setRowData", rowid, {
                                            POD_UnitCost: newCost,
                                            POD_ExtCost: orderQty * newCost
                                        });
                                    }
                                },
                                minLength: 0,
                                minChars: 0,
                                autoFill: true,
                                mustMatch: true,
                                matchContains: false,
                                scrollHeight: 220


                            }).on('focus', function(event) {
                                var self = this;
                                $(self).autocomplete( "search", "");
                            });
                        }
                    }
                },
                {
                    name: "POD_UnitCost",
                    index: "o.POD_UnitCost",
                    editable: false,
                    align: 'center',
                    formatter: 'currency',
                    formatoptions: {prefix: '$', suffix: '', thousandsSeparator: ','},
                    jsonmap: "cell.9"
                },
                {
                    name: "POD_ExtCost",
                    index: "o.POD_ExtCost",
                    editable: false,
                    align: 'center',
                    formatter: 'currency',
                    formatoptions: {prefix: '$', suffix: '', thousandsSeparator: ','},
                    jsonmap: "cell.10"
                },
                {name: "POD_Leadtime", index: "o.POD_Leadtime", editable: false, jsonmap: "cell.11"},
                {
                    name: "POD_Stocked",
                    index: "o.POD_Stocked",
                    editable: false,
                    edittype: 'checkbox',
                    editoptions: {value: "True:False"},
                    formatter: "checkbox",
                    formatoptions: {disabled: true},
                    align: 'center',
                    jsonmap: "cell.12"
                },
                {name: "POD_Note", index: "o.POD_Note", editable: true, jsonmap: "cell.13"},
                {
                    name: "POD_ReqDate",
                    index: "o.POD_ReqDate",
                    editable: true,
                    jsonmap: "cell.14.date",
                    editoptions:{size:20,
                        dataInit:function(el){
                            $(el).datepicker({dateFormat:'yy-mm-dd'});
                        },
                        defaultValue: function(){
                            var currentTime = new Date();
                            var month = parseInt(currentTime.getMonth() + 1);
                            month = month <= 9 ? "0"+month : month;
                            var day = currentTime.getDate();
                            day = day <= 9 ? "0"+day : day;
                            var year = currentTime.getFullYear();
                            return year+"-"+month + "-"+day;
                        }
                    }
                },
                {name: "POD_ConfrmNum", index: "o.POD_ConfrmNum", editable: true, jsonmap: "cell.15"},
                {
                    name: "POD_PromDate",
                    index: "o.POD_PromDate",
                    editable: true,
                    jsonmap: "cell.16.date",
                    editoptions:{size:20,
                        dataInit:function(el){
                            $(el).datepicker({dateFormat:'yy-mm-dd'});
                        },
                        defaultValue: function(){
                            var currentTime = new Date();
                            var month = parseInt(currentTime.getMonth() + 1);
                            month = month <= 9 ? "0"+month : month;
                            var day = currentTime.getDate();
                            day = day <= 9 ? "0"+day : day;
                            var year = currentTime.getFullYear();
                            return year+"-"+month + "-"+day;
                        }
                    }
                },
                {name: "VQ_PK", index: "o.VQ_PK", editable: false, jsonmap: "cell.17"},
                {name: "pod_reftext", index: "o.pod_reftext", editable: false, jsonmap: "cell.18"},
                {name: "VQ_VRef", index: "o.VQ_VRef", editable: false, jsonmap: "cell.19"},
                {name: "POD_UOM", index: "o.POD_UOM", editable: false, jsonmap: "cell.20"},
                {name: "POD_ItemType", index: "o.POD_ItemType", editable: false, jsonmap: "cell.21"},
                {name: "POD_DateUpdated", index: "o.POD_DateUpdated", editable: false, jsonmap: "cell.22.date"},
                {name: "POD_UpdatedSysUser", index: "o.POD_UpdatedSysUser", editable: false, jsonmap: "cell.23"},
                {name: "I_FSC", index: "o.I_FSC", editable: false, jsonmap: "cell.24"},
                {name: "PjD_NumID", index: "o.PjD_NumID", editable: false, jsonmap: "cell.25"},
                {name: "POD_QueueSourceText", index: "o.POD_QueueSourceText", editable: false, jso

Answer 1

You don't provided a working demo, but I hope that I still understand you correctly.

I think that you want to change the value of not-editable columns POD_UnitCost and POD_ExtCost on selection of the value in the jQuery UI Autocomplete control in editable POD_VendorDiscount. The value of another editable columns POD_OrderQty and POD_VendorListPrice will be used during calculations. The current code from select callback of autocomplete

var rowData = $('#list').jqGrid('getRowData', rowid);
cont = rowData.POD_VendorListPrice;
console.log(cont);
rowData.POD_ExtCost = (1-ui.item.id)*cont;

is incorrect. The first problem is the usage of getRowData to access of columns which are at the moment in inline editing mode. It's wrong. getRowData will get you HTML fragment from the cells instead of value of the corresponding <input> element. The second error: you just set POD_ExtCost of rowData object without any additional actions. It's not changes the value in POD_ExtCost column. What you should do instead: you can use getRowData and setRowData to get/set values of not editing columns and you have to get <input> elements of editing element and get its value to get the current editing value.

I can't test the below code, but the correct way could be for example the following

dataInit: function(elem) {
    var $self = $(this), // save the reference to the grid
        $elem = $(elem); // save the reference to <input>
    $elem.autocomplete({
        source: autocompleteSource,
        select: function (event, ui) {
            var $tr = $elem.closest("tr.jqgrow"), newCost, rowid = $tr.attr("id"),
                orderQty = parseFloat($tr.find("input[name=POD_OrderQty]").val()),
                listPrice = parseFloat($tr.find("input[name=POD_VendorListPrice]").val());
            if (ui.item) {
                console.log(orderQty);
                console.log(listPrice);
                newCost = (1 - parseFloat(ui.item.id)) * listPrice;
                $self.jqGrid("setRowData", rowid, {
                    POD_UnitCost: newCost,
                    POD_ExtCost: orderQty * newCost
                });
            }
        },
        minLength: 0,
        minChars: 0,
        autoFill: true,
        mustMatch: true,
        matchContains: false,
        scrollHeight: 220
    }).on("focus", function(event) {
        $(this).autocomplete("search", "");
    });
}

I should remark that the above code works only for inline editing.