Drop Down in JQGrid

Question

I am new to JQgrid and we are using Perl Catalyst to build the application. I need to have a drop down for the Operating system field

Please find the code for JQgrid

<title>Server Details </title>
<link rel="stylesheet" type="text/css" media="screen" href="[%         c.uri_for('/static/css/cupertino/jquery-ui-1.10.3.custom.css') %]" />
<link rel="stylesheet" type="text/css" media="screen" href="[% c.uri_for('/static/plugins/jqGrid/css/ui.jqgrid.css') %]" /> 
<link rel="stylesheet" type="text/css" media="screen" href="[% c.uri_for('/static/plugins/jqGrid/css/print-container.css') %]" />

<script src="[% c.uri_for('/static/plugins/jqGrid/js/i18n/grid.locale-en.js')%]" type="text/javascript"></script>
<script src="[% c.uri_for('/static/plugins/jqGrid/js/jquery.printElement.js')%]" type="text/javascript"></script>
<script src="[% c.uri_for('/static/plugins/jqGrid/js/printing.js')%]" type="text/javascript"></script>
<script src="[% c.uri_for('/static/plugins/jqGrid/js/export_to_excel.js')%]" type="text/javascript"></script>
<script src="[% c.uri_for('/static/plugins/jqGrid/js/jquery.jqGrid.src.js') %]" type="text/javascript"></script>
<script type="text/javascript">
$(function(){
    $("#list").jqGrid({ 
        url:"[% c.uri_for("server_details_json") %]",
        datatype:'json',
        mtype:'GET',
    colNames:['Server Id' , 'Host Name', 'IP Address','Operating System','Operating  System Version', 'Network Domain','Business Unit'],
        colModel:[ 

        {name:'server_id', index:'server_id', align:'centre',editable:false},
            {name:'server_name', index:'server_name', align:'left',editable:true},
            {name:'ip_address', index:'ip_address', align:'left',editable:true}, 
            {name:'operating_system', index:'operating_system', align:'left',editable:true, edittype: 'select', 
                searchoptions: {value:getOptionsList(),
                                sopt:['eq']}},

            {name:'operating_system_version', index:'operating_system_version', align:'left',editable:true},
            {name:'domain', index:'domain', align:'left',editable:true},
            {name:'business_unit', index:'business_unit', align:'left',editable:true},

 ],
pager:'#pager',
rowNum:10,
autowidth:true,
autoheight:true,
rowList:[10,20,30,1000000000000000000],
loadComplete: function() {
    $("option[value=1000000000000000000]").text('All');
},
sortname:'server_id',
sortorder:'asec',
shrinkToFit:true,
viewrecords:true,
gridview:true,
height:'auto',
editurl:"[% c.uri_for("postrow") %]",
caption:'Server List '
}); 

$("#list").jqGrid('navGrid', '#pager',{
        view:false, 
        del:true, 
        add:true, 
        edit:true, 
        search: true, 
        refresh: true,
        print:true 
        },

{height:250,width:500,reloadAfterSubmit:true}, // edit options
{height:480,reloadAfterSubmit:false}, // add options
{reloadAfterSubmit:false}, // del options
{} // search options
)
// setup grid print capability. Add print button to navigation bar and bind to click.
setPrintGrid('list','pager','Server Details');
setExcelGrid('list','pager','/tams/Server_Details_CSV','Server Details1');
}); 

</script>

 <script>
 function getOptionsList(){
 $.ajax({
type: "POST",
url:"[% c.uri_for("OS_json") %]",
async:false,
dataType: 'json',
success: function(data){
    options=data.value;
},
failure :function(xhr,status,msg) {
    alert("Unexpected error occured. !!!"+msg);
}
});
return options;
}
</script>

<body>
<table id="list"><tr><td/></tr></table> 
<div id="pager"></div> 
</body>`

The Json Data is like this

[{"value":"Windows","id":"86"},{"value":"AIX","id":"87"}]

Can some one help me Thanks in advance for your precious time

score 1 · Answer 1 · edited May 23 '17 at 11:50

First of all you defined searchoptions.value for operating_system column which will be used during searching and not during editing. Moreover the property will work in Searching dialog only if you would add additional property stype: "select". So you should add editoptions: {value: getOptionsList() } to have <select> during editing.

The format of value for editoptions.value and searchoptions.value can be either the string like

"86:Windows;87:AIX"

or an object like

{"86": "Windows", "87": "AIX"}

and not [{"value":"Windows","id":"86"},{"value":"AIX","id":"87"}] which you currently use.

You should change the code of getOptionsList to construct the corresponding results. By the way I prefer to use String form instead of Object form because it allows to specify the exact order of <option> elements in the <select>. The order of options in case of usage object form can be different in different web browsers.

I would recommend you to change your code so that you don't use synchronous Ajax request. Instead of that you can use editoptions {dataUrl: "[% c.uri_for("OS_json") %]", buildSelect: function (data) {...}}. You should additionally define ajaxSelectOptions: {dataType: "json"}. The callback function buildSelect get the server response (data) and it should return the HTML fragment of <select> with all <option> elements. You can find some examples here, here and here.

UPDATED: The code of buildSelect can be something like

buildSelect: function (data) {
    var html = "<select>", length = data.length, i, item;
    for (i = 0; i < length; i++) {
        item = data[i];
        html += '<option value=' + item.id + '>' + item.value + '</option>';
    }
    return html + "/<select>";
}

if you want that results of editing of the select will be sent as select id (like 86 for "Windows") to the server (see the demo). If you want that server get the name (like "Windows") then you need fill value of <option> elements using value property and ignore the id value:

buildSelect: function (data) {
    var html = "<select>", length = data.length, i, item;
    for (i = 0; i < length; i++) {
        item = data[i];
        html += '<option value=' + item.value + '>' + item.value + '</option>';
    }
    return html + "/<select>";
}

see the demo. You can use Fiddler, Developer Tools of IE or other free tools to trace the exact HTTP traffic during editing.

Hi Oleg,Thanks for your explanation will this Json Data work [{"86":"Windows"},{"87":"AIX"}] I am not sure how to remove the square brackets from the json data in catalyst. — Pravish Jayasundar, Oct 29 '13 at 12:38
@PravishJayasundar: You are welcome! `[{"86":"Windows"},{"87":"AIX"}]` will **not** work. As I wrote the data can be *object* with properties like options in the select `{"86": "Windows", "87": "AIX"}` and not array of objects. Additionally the object form can not hold the order of items. If you use `dataUrl` the you can easy convert inside of `buildSelect` *any* server response to the HTML fragment. — Oleg, Oct 29 '13 at 12:44
,thanks again for your prompt response this is driving me nuts....Is there a way to directly get the data from table? I am using MYSQL sorry to be a pain. — Pravish Jayasundar, Oct 29 '13 at 14:28
@PravishJayasundar: No, jqGrid is pure jQuery plugin and so it can't directly access MYSQL database. I'm not sure which problem you have currently. I described shortly many ways to solve your problem. The simplest one is modifying `success` callback to produce `"86:Windows;87:AIX"` from `[{"value":"Windows","id":"86"},{"value":"AIX","id":"87"}]` object. You need just write simple `for` loop. — Oleg, Oct 29 '13 at 15:24
@PravishJayasundar: Another (better way) is usage of `editoptions {dataUrl: "[% c.uri_for("OS_json") %]", buildSelect: function (data) {...}}` in `colModel` for `operating_system` column. You need add jqGrid option `ajaxSelectOptions: {dataType: "json"}` additionally. The callback function `buildSelect` is very simple too. You need convert the same input `data` (`[{"value":"Windows","id":"86"},{"value":"AIX","id":"87"}]`) to the string ``. So I don't understand where you have the problem. — Oleg, Oct 29 '13 at 15:28

AthibaN · Answer 2 · 2013-10-29T11:24:56.677

0

Your colModel must be like,

{ name: 'Decision', width: 200, editable: true, formatter: 'select', edittype: 'select',
editoptions: {
                        value: {
                            '1': 'Option 1',
                            '2': 'Option 2',
                            '3': 'Option 3'
                        }
               }
},

I guess, it must be editoptions instead of searchoptions. Here is an example grid, thanks to Oleg

edited Oct 29 '13 at 11:24

answered Oct 29 '13 at 11:19

AthibaN

2,087
1
15
22

Hi,When I hard code the values then with editoption it is working fine but i need the data to be displayed from table. – Pravish Jayasundar Oct 29 '13 at 11:43
Follow Oleg's solution and take time to check his links, his explanation is more deep and more clear than mine! :) – AthibaN Oct 29 '13 at 12:06
Thanks AthibaN, I am following is explanation...:-)Thanks for your help – Pravish Jayasundar Oct 29 '13 at 12:09

Drop Down in JQGrid

2 Answers2