jqgrid incorrect select drop down option values in edit box

I am using form edit. There are two select boxes in the form. One select box is the country, another select box is the state. The state select box depends on the country selected and will be populated dynamically. For example:

Country:

US (option value=1)
UK (option value=2)

State for US:

Alabama (option value=1)
California (option value=2)
Florida (option value=3)
Hawaii (option value=4)

State for UK:

London (option value=5)
Oxford (option value=6)

As you can see above the id of state for uk starts with 5. When I edit a record which contained Country id=2 (UK) and State id=6 (Oxford), the edit form will shows correctly - Country is UK and State is Oxford. But if you drop down the state select box the option text is correct (it shows London Oxford) but the option value will starts from 0. What should be correct is that the option value should starts from 5.

If you select and change the country drop down box to US then change back again to UK, the option value will be populated correct (starts from 5).

My question is, how can we populate the select box for the state with the correct option value based on the country in edit box when the edit form loads?


Solution 1:

The answer on your question depend a little from the source where you receive the information about displayed under "State for US" and "State for UK". The are two possibilities supported by jqGrid: 1) the usage of value parameter of editoptions 2) the usage of dataUrl and buildSelect parameter of the editoptions. The first way is the best one in case of local editing or in the case if the list of possible options is static. The second choose will be used in the case, that the information about states, countries and the states of some country will be get per AJAX request from the database. I describe the solution on the example the usage of value parameter to have no dependencies to the server components. The most parts on the implementation are the same in case of the usage of dataUrl and buildSelect.

I made the live example which demonstrate what you need.

The main problem is that the value of the editoptions are used only once at the time on the initialization. Inside of dataInit function one can overwrite the value, but after the changing of the value in the first select/drop-down box with countries the second select/drop-down box with states must be rebuild manually. To do so one have to understand, that the select HTML element has id constructed from row id '_' and the column name: rowId + "_State". Moreover it is important, that the the value of the editoptions must be reset to the initial value, so that any state id can be decoded to the state name.

Here is the code from the example:

var countries = { '1': 'US', '2': 'UK' };
var states = { '1': 'Alabama', '2': 'California', '3': 'Florida', '4': 'Hawaii', '5': 'London', '6': 'Oxford' };
var statesOfCountry = {
    1: { '1': 'Alabama', '2': 'California', '3': 'Florida', '4': 'Hawaii' },
    2: { '5': 'London', '6': 'Oxford' }
};
var mydata = [
    { id: '0', Country: '1', State: '1', Name: "Louise Fletcher" },
    { id: '1', Country: '1', State: '3', Name: "Jim Morrison" },
    { id: '2', Country: '2', State: '5', Name: "Sherlock Holmes" },
    { id: '3', Country: '2', State: '6', Name: "Oscar Wilde" }
];

var lastSel = -1;
var grid = jQuery("#list");
var resetStatesValues = function () {
    grid.setColProp('State', { editoptions: { value: states} });
};
grid.jqGrid({
    data: mydata,
    datatype: 'local',
    colModel: [
        { name: 'Name', width: 200 },
        { name: 'Country', width: 100, editable: true, formatter: 'select',
            edittype: 'select', editoptions: {
                value: countries,
                dataInit: function (elem) {
                    var v = $(elem).val();
                    // to have short list of options which corresponds to the country
                    // from the row we have to change temporary the column property
                    grid.setColProp('State', { editoptions: { value: statesOfCountry[v]} });
                },
                dataEvents: [
                    {
                        type: 'change',
                        fn: function(e) {
                            // To be able to save the results of the current selection
                            // the value of the column property must contain at least
                            // the current selected 'State'. So we have to reset
                            // the column property to the following
                            //grid.setColProp('State', { editoptions:{value: statesOfCountry[v]} });
                            //grid.setColProp('State', { editoptions: { value: states} });
                            resetStatesValues();

                            // build 'State' options based on the selected 'Country' value
                            var v = parseInt($(e.target).val(), 10);
                            var sc = statesOfCountry[v];
                            var newOptions = '';
                            for (var stateId in sc) {
                                if (sc.hasOwnProperty(stateId)) {
                                    newOptions += '<option role="option" value="' +
                                                  stateId + '">' +
                                                  states[stateId] + '</option>';
                                }
                            }

                            // populate the new
                            if ($(e.target).is('.FormElement')) {
                                // form editing
                                var form = $(e.target).closest('form.FormGrid');
                                $("select#State.FormElement", form[0]).html(newOptions);
                            } else {
                                // inline editing
                                var row = $(e.target).closest('tr.jqgrow');
                                var rowId = row.attr('id');
                                $("select#" + rowId + "_State", row[0]).html(newOptions);
                            }
                        }
                    }
                ]
            }
        },
        {
            name: 'State', width: 100, editable: true, formatter: 'select',
            edittype: 'select', editoptions: { value: states }
        }
    ],
    onSelectRow: function (id) {
        if (id && id !== lastSel) {
            if (lastSel != -1) {
                resetStatesValues();
                grid.restoreRow(lastSel);
            }
            lastSel = id;
        }
    },
    ondblClickRow: function (id, ri, ci) {
        if (id && id !== lastSel) {
            grid.restoreRow(lastSel);
            lastSel = id;
        }
        resetStatesValues();
        grid.editRow(id, true, null, null, 'clientArray', null,
                        function (rowid, response) {  // aftersavefunc
                            grid.setColProp('State', { editoptions: { value: states} });
                        });
        return;
    },
    editurl: 'clientArray',
    sortname: 'Name',
    height: '100%',
    viewrecords: true,
    rownumbers: true,
    sortorder: "desc",
    pager: '#pager',
    caption: "Demonstrate dependend select/dropdown lists (edit on double-click)"
}).jqGrid('navGrid','#pager', 
          { edit: true, add: true, del: false, search: false, refresh: false },
          { // edit options
              recreateForm:true,
              onClose:function() {
                  resetStatesValues();
              }
          },
          { // add options
              recreateForm:true,
              onClose:function() {
                  resetStatesValues();
              }
          });

UPDATED: I updated the code above to make it working with in case of form editing too. You can see it live here. Because jqGrid not support local editing for form editing I could not tested the code. Nevertheless I hope that I made the most of required changes.

UPDATED 2: I extended the above code to support

  1. Inline editing, Form editing, Searching Toolbar and Advanced Searching
  2. The previous or next navigation buttons in the editing form
  3. Improving keyboard support in selects (problem with refreshing dependent select in some browsers are fixed)

The new version of the demo is here. The modified code of the demo you find below:

var countries = { '1': 'US', '2': 'UK' },
    //allCountries = {'': 'All', '1': 'US', '2': 'UK'},
    // we use string form of allCountries to have control on the order of items
    allCountries = ':All;1:US;2:UK',
    states = { '1': 'Alabama', '2': 'California', '3': 'Florida', '4': 'Hawaii', '5': 'London', '6': 'Oxford' },
    allStates = ':All;1:Alabama;2:California;3:Florida;4:Hawaii;5:London;6:Oxford',
    statesOfUS = { '1': 'Alabama', '2': 'California', '3': 'Florida', '4': 'Hawaii' },
    statesOfUK = { '5': 'London', '6': 'Oxford' },
    // the next maps contries by ids to states
    statesOfCountry = { '': states, '1': statesOfUS, '2': statesOfUK },
    mydata = [
        { id: '0', country: '1', state: '1', name: "Louise Fletcher" },
        { id: '1', country: '1', state: '3', name: "Jim Morrison" },
        { id: '2', country: '2', state: '5', name: "Sherlock Holmes" },
        { id: '3', country: '2', state: '6', name: "Oscar Wilde" }
    ],
    lastSel = -1,
    grid = $("#list"),
    removeAllOption = function (elem) {
        if (typeof elem === "object" && typeof elem.id === "string" && elem.id.substr(0, 3) !== "gs_") {
            // in the searching bar
            $(elem).find('option[value=""]').remove();
        }
    },
    resetStatesValues = function () {
        // set 'value' property of the editoptions to initial state
        grid.jqGrid('setColProp', 'state', { editoptions: { value: states} });
    },
    setStateValues = function (countryId) {
        // to have short list of options which corresponds to the country
        // from the row we have to change temporary the column property
        grid.jqGrid('setColProp', 'state', { editoptions: { value: statesOfCountry[countryId]} });
    },
    changeStateSelect = function (countryId, countryElem) {
        // build 'state' options based on the selected 'country' value
        var stateId, stateSelect, parentWidth, $row,
            $countryElem = $(countryElem),
            sc = statesOfCountry[countryId],
            isInSearchToolbar = $countryElem.parent().parent().parent().hasClass('ui-search-toolbar'),
                              //$(countryElem).parent().parent().hasClass('ui-th-column')
            newOptions = isInSearchToolbar ? '<option value="">All</option>' : '';

        for (stateId in sc) {
            if (sc.hasOwnProperty(stateId)) {
                newOptions += '<option role="option" value="' + stateId + '">' +
                    states[stateId] + '</option>';
            }
        }

        setStateValues(countryId);

        // populate the subset of contries
        if (isInSearchToolbar) {
            // searching toolbar
            $row = $countryElem.closest('tr.ui-search-toolbar');
            stateSelect = $row.find(">th.ui-th-column select#gs_state");
            parentWidth = stateSelect.parent().width();
            stateSelect.html(newOptions).css({width: parentWidth});
        } else if ($countryElem.is('.FormElement')) {
            // form editing
            $countryElem.closest('form.FormGrid').find("select#state.FormElement").html(newOptions);
        } else {
            // inline editing
            $row = $countryElem.closest('tr.jqgrow');
            $("select#" + $.jgrid.jqID($row.attr('id')) + "_state").html(newOptions);
        }
    },
    editGridRowOptions = {
        recreateForm: true,
        onclickPgButtons: function (whichButton, $form, rowid) {
            var $row = $('#' + $.jgrid.jqID(rowid)), countryId;
            if (whichButton === 'next') {
                $row = $row.next();
            } else if (whichButton === 'prev') {
                $row = $row.prev();
            }
            if ($row.length > 0) {
                countryId = grid.jqGrid('getCell', $row.attr('id'), 'country');
                changeStateSelect(countryId, $("#country")[0]);
            }
        },
        onClose: function () {
            resetStatesValues();
        }
    };

grid.jqGrid({
    data: mydata,
    datatype: 'local',
    colModel: [
        { name: 'name', width: 200, editable: true },
        { name: 'country', width: 100, editable: true, formatter: 'select', stype: 'select', edittype: 'select',
            searchoptions: {
                value: allCountries,
                dataInit: function (elem) { removeAllOption(elem); },
                dataEvents: [
                    { type: 'change', fn: function (e) { changeStateSelect($(e.target).val(), e.target); } },
                    { type: 'keyup', fn: function (e) { $(e.target).trigger('change'); } }
                ]
            },
            editoptions: {
                value: countries,
                dataInit: function (elem) { setStateValues($(elem).val()); },
                dataEvents: [
                    { type: 'change', fn: function (e) { changeStateSelect($(e.target).val(), e.target); } },
                    { type: 'keyup', fn: function (e) { $(e.target).trigger('change'); } }
                ]
            }},
        { name: 'state', width: 100, formatter: 'select', stype: 'select',
            editable: true, edittype: 'select', editoptions: { value: states },
            searchoptions: { value: allStates, dataInit: function (elem) { removeAllOption(elem); } } }
    ],
    onSelectRow: function (id) {
        if (id && id !== lastSel) {
            if (lastSel !== -1) {
                $(this).jqGrid('restoreRow', lastSel);
                resetStatesValues();
            }
            lastSel = id;
        }
    },
    ondblClickRow: function (id) {
        if (id && id !== lastSel) {
            $(this).jqGrid('restoreRow', lastSel);
            lastSel = id;
        }
        resetStatesValues();
        $(this).jqGrid('editRow', id, {
            keys: true,
            aftersavefunc: function () {
                resetStatesValues();
            },
            afterrestorefunc: function () {
                resetStatesValues();
            }
        });
        return;
    },
    editurl: 'clientArray',
    sortname: 'name',
    ignoreCase: true,
    height: '100%',
    viewrecords: true,
    rownumbers: true,
    sortorder: "desc",
    pager: '#pager',
    caption: "Demonstrate dependend select/dropdown lists (inline editing on double-click)"
});
grid.jqGrid('navGrid', '#pager', { del: false }, editGridRowOptions, editGridRowOptions);
grid.jqGrid('filterToolbar', {stringResult: true, searchOnEnter: true, defaultSearch : "cn"});

UPDATED 3: The last version of the code of the demo you will find here.