jqGrid - dynamically load different drop down values for different rows depending on another column value
- by Renso
Goal:
As we all know the jqGrid examples in the demo and the Wiki always refer to static values for drop down boxes. This of course is a personal preference but in dynamic design these values should be populated from the database/xml file, etc, ideally JSON formatted.
Can you do this in jqGrid, yes, but with some custom coding which we will briefly show below (refer to some of my other blog entries for a more detailed discussion on this topic).
What you CANNOT do in jqGrid, referrign here up and to version 3.8.x, is to load different drop down values for different rows in the jqGrid. Well, not without some trickery, which is what this discussion is about.
Issue:
Of course the issue is that jqGrid has been designed for high performance and thus I have no issue with them loading a reference to a single drop down values list for every column. This way if you have 500 rows or one, each row only refers to a single list for that particuolar column. Nice!
SO how easy would it be to simply traverse the grid once loaded on gridComplete or loadComplete and simply load the select tag's options from scratch, via ajax, from memory variable, hard coded etc? Impossible! Since their is no embedded SELECT tag within each cell containing the drop down values (remeber it only has a reference to that list in memory), all you will see when you inspect the cell prior to clicking on it, or even before and on beforeEditCell, is an empty <TD></TD>.
When trying to load that list via a click event on that cell will temporarily load the list but jqGrid's last internal callback event will remove it and replace it with the old one, and you are back to square one.
Solution:
Yes, after spending a few hours on this found a solution to the problem that does not require any updates to jqGrid source code, thank GOD!
Before we get into the coding details, the solution here can of course be customized to suite your specific needs, this one loads the entire drop down list that would be needed across all rows once into global variable. I then parse this object that contains all the properties I need to filter the rows depending on which ones I want the user to see based off of another cell value in that row. This only happens when clicking the cell, so no performance penalty. You may of course to load it via ajax when the user clicks the cell, but I found it more effecient to load the entire list as part of jqGrid's normal editoptions: { multiple: false, value: listingStatus } colModel options which again keeps only a reference to the sinlge list, no duplciation.
Lets get into the meat and potatoes of it.
var acctId = $('#Id').val();
var data = $.ajax({ url: $('#ajaxGetAllMaterialsTrackingLookupDataUrl').val(), data: { accountId: acctId }, dataType: 'json', async: false, success: function(data, result) { if (!result) alert('Failure to retrieve the Alert related lookup data.'); } }).responseText;
var lookupData = eval('(' + data + ')');
var listingCategory = lookupData.ListingCategory;
var listingStatus = lookupData.ListingStatus;
var catList = '{';
$(lookupData.ListingCategory).each(function() {
catList += this.Id + ':"' + this.Name + '",';
});
catList += '}';
var lastsel;
var ignoreAlert = true;
$(item)
.jqGrid({
url: listURL,
postData: '',
datatype: "local",
colNames: ['Id', 'Name', 'Commission<br />Rep', 'Business<br />Group', 'Order<br />Date', 'Edit', 'TBD', 'Month', 'Year', 'Week', 'Product', 'Product<br />Type', 'Online/<br />Magazine', 'Materials', 'Special<br />Placement', 'Logo', 'Image', 'Text', 'Contact<br />Info', 'Everthing<br />In', 'Category', 'Status'],
colModel: [
{ name: 'Id', index: 'Id', hidden: true, hidedlg: true },
{ name: 'AccountName', index: 'AccountName', align: "left", resizable: true, search: true, width: 100 },
{ name: 'OnlineName', index: 'OnlineName', align: 'left', sortable: false, width: 80 },
{ name: 'ListingCategoryName', index: 'ListingCategoryName', width: 85, editable: true, hidden: false, edittype: "select", editoptions: { multiple: false, value: eval('(' + catList + ')') }, editrules: { required: false }, formatoptions: { disabled: false} }
],
jsonReader: {
root: "List",
page: "CurrentPage",
total: "TotalPages",
records: "TotalRecords",
userdata: "Errors",
repeatitems: false,
id: "0"
},
rowNum: $rows,
rowList: [10, 20, 50, 200, 500, 1000, 2000],
imgpath: jQueryImageRoot,
pager: $(item + 'Pager'),
shrinkToFit: true,
width: 1455,
recordtext: 'Traffic lines',
sortname: 'OrderDate',
viewrecords: true,
sortorder: "asc",
altRows: true,
cellEdit: true,
cellsubmit: "remote",
cellurl: editURL + '?rows=' + $rows + '&page=1',
loadComplete: function() {
},
gridComplete: function() {
},
loadError: function(xhr, st, err) {
},
afterEditCell: function(rowid, cellname, value, iRow, iCol) {
var select = $(item).find('td.edit-cell select');
$(item).find('td.edit-cell select option').each(function() {
var option = $(this);
var optionId = $(this).val();
$(lookupData.ListingCategory).each(function() {
if (this.Id == optionId) {
if (this.OnlineName != $(item).getCell(rowid, 'OnlineName')) {
option.remove();
return false;
}
}
});
});
},
search: true,
searchdata: {},
caption: "List of all Traffic lines",
editurl: editURL + '?rows=' + $rows + '&page=1',
hiddengrid: hideGrid
Here is the JSON data returned via the ajax call during the jqGrid function call above (NOTE it must be { async: false}:
{"ListingCategory":[{"Id":29,"Name":"Document Imaging & Management","OnlineName":"RF Globalnet"} ,{"Id":1,"Name":"Ancillary Department Hardware","OnlineName":"Healthcare Technology Online"} ,{"Id":2,"Name":"Asset Tracking","OnlineName":"Healthcare Technology Online"} ,{"Id":3,"Name":"Asset Tracking","OnlineName":"Healthcare Technology Online"} ,{"Id":4,"Name":"Asset Tracking","OnlineName":"Healthcare Technology Online"} ,{"Id":5,"Name":"Document Imaging & Management","OnlineName":"Healthcare Technology Online"} ,{"Id":6,"Name":"Document Imaging & Management","OnlineName":"Healthcare Technology Online"} ,{"Id":7,"Name":"EMR/EHR Software","OnlineName":"Healthcare Technology Online"}]}
I only need the Id and Name for the drop down list, but the third column in the JSON object is important, it is the only that I match up with the OnlineName in the jqGrid column, and then in the loop during afterEditCell simply remove the ones I don't want the user to see. That's it!
