How to display indirect data in Jqgrid
I answered already on the closed question before (see here). Nevertheless I decide to answer on your question in detail because the problem which you describe is really very common.
I start with reminding that jqGrid provides formatter: "select" which uses formatoptions.value or editoptions.value to decode ids to texts. The formatter: "select" uses value and optional separator, delimiter and defaultValue properties, but it can't uses editoptions.dataUrl to get required data from the server instead of usage static value. The problem is very easy: processing dataUrl works asynchronous, but during formatting of the column of grid body one don't support delayed filling. So to use formatter: "select" one have to set formatoptions.value or editoptions.value before the server response will be processed by jqGrid.
In the old answer I suggested to extend JSON response returned from the server with additional data for editoptions.value of the columns having formatter: "select". I suggest to set the beforeProcessing. For example one can generate the server response in the following format:
{
"cityMap": {"11": "Chennai", "12": "Mumbai", "13": "Delhi"},
"rows": [
{ "SID": "1", "SNAME": "ABC", "CITY": "11" },
{ "SID": "2", "SNAME": "XYZ", "CITY": "12" },
{ "SID": "3", "SNAME": "ACX", "CITY": "13" },
{ "SID": "4", "SNAME": "KHG", "CITY": "13" },
{ "SID": "5", "SNAME": "ADF", "CITY": "12" },
{ "SID": "6", "SNAME": "KKR", "CITY": "11" }
]
}
and uses the following jqGrid options
colModel: [
{name: "SNAME", width: 250},
{name: "CITY", width: 180, align: "center"}
],
beforeProcessing: function (response) {
var $self = $(this);
$self.jqGrid("setColProp", "CITY", {
formatter: "select",
edittype: "select",
editoptions: {
value: $.isPlainObject(response.cityMap) ? response.cityMap : []
}
});
},
jsonReader: { id: "SID"}
The demo demonstrates the approach. It displays
One can use the same approach to set any column options dynamically. For example one can use
{
"colModelOptions": {
"CITY": {
"formatter": "select",
"edittype": "select",
"editoptions": {
"value": "11:Chennai;13:Delhi;12:Mumbai"
},
"stype": "select",
"searchoptions": {
"sopt": [ "eq", "ne" ],
"value": ":Any;11:Chennai;13:Delhi;12:Mumbai"
}
}
},
"rows": [
{ "SID": "1", "SNAME": "ABC", "CITY": "11" },
{ "SID": "2", "SNAME": "XYZ", "CITY": "12" },
{ "SID": "3", "SNAME": "ACX", "CITY": "13" },
{ "SID": "4", "SNAME": "KHG", "CITY": "13" },
{ "SID": "5", "SNAME": "ADF", "CITY": "12" },
{ "SID": "6", "SNAME": "KKR", "CITY": "11" }
]
}
and the following JavaScript code
var filterToolbarOptions = {defaultSearch: "cn", stringResult: true, searchOperators: true},
removeAnyOption = function ($form) {
var $self = $(this), $selects = $form.find("select.input-elm");
$selects.each(function () {
$(this).find("option[value='']").remove();
});
return true; // for beforeShowSearch only
},
$grid = $("#list");
$.extend($.jgrid.search, {
closeAfterSearch: true,
closeAfterReset: true,
overlay: 0,
recreateForm: true,
closeOnEscape: true,
afterChange: removeAnyOption,
beforeShowSearch: removeAnyOption
});
$grid.jqGrid({
colModel: [
{name: "SNAME", width: 250},
{name: "CITY", width: 180, align: "center"}
],
beforeProcessing: function (response) {
var $self = $(this), options = response.colModelOptions, p,
needRecreateSearchingToolbar = false;
if (options != null) {
for (p in options) {
if (options.hasOwnProperty(p)) {
$self.jqGrid("setColProp", p, options[p]);
if (this.ftoolbar) { // filter toolbar exist
needRecreateSearchingToolbar = true;
}
}
}
if (needRecreateSearchingToolbar) {
$self.jqGrid("destroyFilterToolbar");
$self.jqGrid("filterToolbar", filterToolbarOptions);
}
}
},
jsonReader: { id: "SID"}
});
$grid.jqGrid("navGrid", "#pager", {add: false, edit: false, del: false})
$grid.jqGrid("filterToolbar", filterToolbarOptions);
The demo uses the above code.
We recreate the searching filter if any option are changed dynamically. The way allows implement more flexible solutions. For example the server can detect the language preferences of the client (of the web browser) and return formatting options for numbers, dates and so on based on the options. I'm sure that everyone can suggest other interesting scenarios.
One more remark. If you have too many items in select in (searchoptions.value and editoptions.value) I would recommend you don't use strings instead of objects as the value of searchoptions.value and editoptions.value. It allows you to specify the order of items in the select element.
If you will have too many items in select (for example all cities of your country) then you can consider to use select2 plugin which usage I demonstrate in the answer. It simplify selection of options because it convert select in element which is very close to jQuery UI Autocomplete.
The next demo demonstrate the usage of select2 plugin. If one click on the dropdown arrow of "select" element of the searching toolbar or the searching dialog one get additional input filed which can be used for quick searching. If one starts to type some text in the input box (for example "e" on an example on the picture below) the list of options will be reduced to the options having the typed text as substring:
I personally find such "select-searching" control very practical.
By the way I described in the another answer how to set colNames dynamically. In can be used to manage more information from the server side.
UPDATED: The corresponding controller action Students can be about the following
public class Student {
public long SID { get; set; }
public string SNAME { get; set; }
public long CITY { get; set; }
}
public class City {
public long CID { get; set; }
public string CNAME { get; set; }
}
...
public class HomeController : Controller {
...
public JsonResult Students () {
var students = new List<Student> {
new Student { SID = 1, SNAME = "ABC", CITY = 11 },
new Student { SID = 2, SNAME = "ABC", CITY = 12 },
new Student { SID = 3, SNAME = "ABC", CITY = 13 },
new Student { SID = 4, SNAME = "ABC", CITY = 13 },
new Student { SID = 5, SNAME = "ABC", CITY = 12 },
new Student { SID = 6, SNAME = "ABC", CITY = 11 }
};
var locations = new List<City> {
new City { CID = 11, CNAME = "Chennai"},
new City { CID = 12, CNAME = "Mumbai"},
new City { CID = 13, CNAME = "Delhi"}
};
// sort and concatinate location corresponds to jqGrid editoptions.value format
var sortedLocations = locations.OrderBy(location => location.CNAME);
var sbLocations = new StringBuilder();
foreach (var sortedLocation in sortedLocations) {
sbLocations.Append(sortedLocation.CID);
sbLocations.Append(':');
sbLocations.Append(sortedLocation.CNAME);
sbLocations.Append(';');
}
if (sbLocations.Length > 0)
sbLocations.Length -= 1; // remove last ';'
return Json(new {
colModelOptions = new {
CITY = new {
formatter = "select",
edittype = "select",
editoptions = new {
value = sbLocations.ToString()
},
stype = "select",
searchoptions = new {
sopt = new[] { "eq", "ne" },
value = ":Any;" + sbLocations
}
}
},
rows = students
},
JsonRequestBehavior.AllowGet);
}
}
How to make jqGrid dynamically populate options list based on row data
You don't wrote which version of jqGrid you use currently, but dataUrl can be defined as callback function with (rowid, value, name) parameters, which have to return the URL which you can build dynamically based on the information. The feature exist starting with v4.5.3 (see the line). You can use getCell, getRowData or getLocalRow inside of the callback to get the data from another columns of the row. Thus you can solve your first problem relatively easy.
You second question seems to me absolutely independent from the first one. It's better to separate such questions in different posts to allow the searching engine better to index the information and so to help other people to find it.
There are no simple way how to solve the second problem, but one can sure suggest a solution, but one have to know much more details what you do and how you do. How you start inline editing (do you use inlineNav, formatter: "actions" or you call editRow directly)? Which version of jqGrid (till version 4.7), free jqGrid or Guriddo jqGrid JS you use? How the columns with selects are defined in colModel? Which datatype you use and whether loadonce: true you use? I recommend you to post separate question with the information.
UPDATE: If you have to use old version of jqGrid then you can't generate dataUrl full dynamically, but because you need to add only SpecialValue=100" part to the URL you can follow the trick which I described in many my old answers (the first one was probably here, but the choice on property names which asked the user could be misunderstood). You can use ajaxSelectOptions.data which will define the data parameters of jQuery.ajax request. The problem only that you can define only one ajaxSelectOptions.data property. So you can add the following jqGrid option:
ajaxSelectOptions: {
data: {
SpecialValue: function () {
var rowid = $myGrid.jqGrid("getGridParam", "selrow");
return $myGrid.jqGrid("getCell", rowid, "SpecialValue");
}
}
}
($myGrid is something like $("#grid"))
UPDATED: You used unknown functions getSelectValuesFromJSON, getLookupValuesFromJSON in the updated part of your question. Both of there seems to use synchronous Ajax request which is not good. Moreover you set editoptions.value for only one Field2 instead of setting all selects.
onSelectRow: function (rowid) {
var $myGrid = $(this);
$.ajax({
url: "someUrl",
dataType: "json";
data: {
specialValue: $myGrid.jqGrid("getCell", rowid, "Field1")
},
success: function (data) {
// for example response data have format:
// { "Field2": ["v1", "v2", ...], "Field3": ["v3", "v4", ...] }
var filed, str;
for (filed in data) {
if (data.hasOwnProperty(filed)) {
str = $.map(data[filed], function (item) {
return item + ":" + item
}).join(";");
$myGrid.jqGrid("setColProp", filed, {
editoptions: {
value: str
}
});
}
}
$myGrid.jqGrid("editRow", rowid, true);
}
});
}
Nevertheless the "Solution 2" is more close to what I would recommend you. It's not really important whether to use onSelectRow or beforeSelectRow. You can make asynchronous Ajax request to the server which returns information for all select which you need. After you get the response from the server (inside of success callback) you can set editoptions.value for all selects and only then you can start editRow. In the way you will be sure that editing of the line will use row specific options in all select.
Some additional remarks. I recommend you to verify gridview: true option in the grid. Additionally I suspect that you fill the grid in not full correct way because you have hidden PK column and you use index instead of rowid as the first parameter of beforeSelectRow and onSelectRow. It's very important to understand that the current implementation of jqGrid always assign id attribute on every row (<tr> element) of the grid. So you have to provide id information in every item of input data. If you want to display the id information to the user (and so to have column in colModel with the primary key) then you should just include key: true property in the column definition. For example you can add key: true to the definition of PK column and so you will have rowid (or index in your case) with the same value like PK. It simplify many parts of code. For example jqGrid send id parameter in the editing request to the server. It's practical to have PK in the request. Moreover if you use repeatitems: false format of jsonReader the you can include id: "PK" in the jsonReader instead of having hidden PK column. It informs jqGrid to get rowid from PK. jqGrid will save PK in id attribute of <tr> and you will don't need to have additional <td style="display:none"> with the same information in the grid.
The last remark. I would strictly recommend you to update the retro version jqGrid 3.8.1 to some more recent version, for example to free jqGrid. Even if you would use no features (like Font Awesome for example) you will have performance advantages, and the look of modern web browsers will looks much better. You should understand the jqGrid 3.8.1 was tested with old (and slow jQuery 1.4.2). The version used with Internet Explorer 8 as the latest IE version (IE9 was published later in March 2011) and it's more oriented on IE6/IE7. The look in modern Chrome/Firefox/Safari can be bad. Is it what you want?
free-jqGrid - The numeric value is displayed instead of the text for select cell
You use datatype: "local", but mtype: "POST", url: restURL + "orders/getCustomerOrders" and beforeProcessing options/callbacks, which shows that you do load the data from the server. The column adresa have the following properties
{
name: 'adresa', index:'adresa',label:'Adresa',
formatter:'select',
stype:'select',searchoptions: {sopt: ['eq','ne'],value: getAdresseSelectValues()},
editable:true,edittype:'select',editoptions:{value: getAdresseSelectValues()}
}
with formatter:'select'. It means that the data for adresa column return from the server are numbers like 26 and 27 on your first picture and you want to display there as texts Str. sadasdsd Nr. 34Bl. asdas Etaj asda Ap. asda and Str. asdasdada Nr. 123Bl. 123 Etaj 123 Ap. 123 because of usage editoptions.value defined as
"26:Str. sadasdsd Nr. 34Bl. asdas Etaj asda Ap. asda;27:Str. asdasdada Nr. 123Bl. 123 Etaj 123 Ap. 123"
The problem only is: you want to load the data from the server too. The data will be filled in the grid using formatter:'select'. Thus the value editoptions.value of the column adresa have to be set/modified before the formatter start working.
I suggest you to use beforeProcessing callback, where you process the server response and set/modify editoptions.value of the column adresa. You can use setColProp, for example, for setting editoptions.value. See the answer and this one for more implementation details.
I'd recommend you to use column template additionally (see the old answer) to reduce code duplicates in your code. It will simplify later modification/maintain of your code.
Some additional advices: you use hidden column name: "orderId" with key:true property. The value will be saved in <td> of the column and as the value of id attribute of the row (<tr>). I suggest you to remove the column and to use jsonReader: { id: "orderId" } instead (or localReader: {id: "orderId"} if you load the data not from the server). It will inform jqGrid to assign rowids based on the value of orderId property of input data. Additionally jqGrid will use orderId as the name of rowid during editing (set to editurl) instead of default name id.
You can consider to remove other hidden columns too (custId and createdBy). You need just add the option additionalProperties: ["orderId", "custId", "createdBy"] to inform jqGrid to read the properties from input data. The properties will be seen in custom formatter, rowattr, cellattr and be saved in the local data (accessible by getLocalRow method). In other words, the data will be save in JavaScript objects, but not in the DOM. It makes HTML/DOM cleaner and improve the performance of the page.
jqGrid filterToolbar with local data
All the features are enabled by default if I understand you correctly. The server just have to return all data instead of one page of data to make loadonce: true property work correctly. You need just call filterToolbar after creating the grid. All will work like with local data. You should consider to set sorttype property for correct local sorting and stype and searchoptions for correct filtering of data.
To have "autocomplete" and "excel like filtering options" you need additionally to follow the answer which set autocomplete or stype: "select", searchoptions: { value: ...} properties based on different values of input data. You can do this inside of beforeProcessing callback. The code from the answer use this.jqGrid("getCol", columnName) which get the data from the grid. Instead of that one have access to data returned from the server inside of beforeProcessing callback. So one can scan the data to get the lists with unique values in every column and to set either autocomplete or stype: "select", searchoptions: { value: ...} properties.
UPDATED: I created JSFiddle demo which demonstrates what one can do: http://jsfiddle.net/OlegKi/vgznxru6/1/. It uses the following code (I changed just echo URL to your URL):
$("#grid").jqGrid({
url: "/WebTest/MainAction.do",
datatype: "json",
colNames: ["Label", "Value"],
colModel: [
{name: "label", width: 70, template: "integer" },
{name: "value", width: 200 }
],
loadonce: true,
pager: true,
rowNum: 10,
rowList: [5, 10, "10000:All"],
iconSet: "fontAwesome",
cmTemplate: { autoResizable: true },
shrinkToFit: false,
autoResizing: { compact: true },
beforeProcessing: function (data) {
var labelMap = {}, valueMap = {}, i, item, labels = ":All", values = [],
$self = $(this);
for (i = 0; i < data.length; i++) {
item = data[i];
if (!labelMap[item.label]) {
labelMap[item.label] = true;
labels += ";" + item.label + ":" + item.label;
}
if (!valueMap[item.value]) {
valueMap[item.value] = true;
values.push(item.value);
}
}
$self.jqGrid("setColProp", "label", {
stype: "select",
searchoptions: {
value: labels,
sopt: ["eq"]
}
});
$self.jqGrid("setColProp", "value", {
searchoptions: {
sopt: ["cn"],
dataInit: function (elem) {
$(elem).autocomplete({
source: values,
delay: 0,
minLength: 0,
select: function (event, ui) {
var grid;
$(elem).val(ui.item.value);
if (typeof elem.id === "string" && elem.id.substr(0, 3) === "gs_") {
grid = $self[0];
if ($.isFunction(grid.triggerToolbar)) {
grid.triggerToolbar();
}
} else {
// to refresh the filter
$(elem).trigger("change");
}
}
});
}
}
});
// one should use stringResult:true option additionally because
// datatype: "json" at the moment, but one need use local filtreing later
$self.jqGrid("filterToolbar", {stringResult: true });
}
});
is there anyway to have multiple columns in jqgrid, read the same source data for dropdowns?
Any implementation of what you want will mean some kind of caching of the data for "/Person/GetSelectData". One way which I would prefer myself is the usage of value instead of dataUrl. The list of select values can be included in the main response to the server which fill the grid. In the case the action used in url can returns additional data. You can use the returned data inside of value defined as a function or you can set the value inside of beforeProcessing alternatively. To make my suggestion more clear I explain it on an example.
The first way: usage value as function. One can include the data which you returns typically in "/Person/GetSelectData" inside of main JSON response. For example you can use userdata (or any other extensions of the input data):
{
"rows": [
...
],
"userdata": {
"Persons": "Bill:Bill;Oleg:Oleg;Leora:Leora"
}
}
Then one could use
beforeProcessing: function (data) {
var $self = $(this), userData = data.userdata, persons, selectOptions;
if (userData && userData.Persons) {
persons = userData.Persons;
selectOptions = {
searchoptions: { value: ":All;" + persons }, // for toolbar search
stype: "select",
editoptions: { value: persons },
edittype: "select"
};
$self.jqGrid("setColProp", "Manager", selectOptions);
$self.jqGrid("setColProp", "Delegate", selectOptions);
}
}
By the way one can even use formatter: "select" for "Manager" and "Delegate" columns. It allows to use ids instead of names. For example
"Persons": "3:Bill;1:Oleg;2:Leora"
One should add formatter: "select" to selectOptions too. It allows to use ids 3, 1 and 2 inside of the main data (rows part of JSON data). The standard way with the usage of dataUrl don't allow to use formatter: "select".
I recommend you to read the answer, this one and this one for more information about usage beforeProcessing for dynamic modification of the grid.
jqgrid dynamic columns and data by json
In comments to my answer on your previous answer I described shortly the idea how you can change the column headers based on the data returned from the server. To make all more clear I prepared a demo for you.
I tried to make the demo mostly short and clear, so it has some restrictions:
- number of columns not changed in different responses from the server
- the formatters and the
widthof the column will be not changed in different responses from the server.
All the restrictions can be reduced or removed, but in your case the above restrictions are suffused. Moreover I wanted first describe the main idea of the implementation.
The demo has tree buttons above the grid which allows to reload data from the server, but from different URLs. After clicking on "Load Russian headers" button the headers on the grid will be dynamically changed with the texts from the server response and one will see the following picture
The format of the data is like below:
{
"model": {
"c1": { "label": "Client" },
"c2": { "label": "Date" },
"c3": { "label": "Amount" },
"c4": { "label": "Tax" },
"c5": { "label": "Total" },
"c6": { "label": "Paid" },
"c7": { "label": "Shipped via" },
"c8": { "label": "Notes" }
},
"data": [
{"id": "10", "cell": ["test", "2007-10-01", "200.00", "10.00", "210.00", "true", "TN", "note" ] },
{"id": "20", "cell": ["test2", "2007-10-02", "300.00", "20.00", "320.00", "false", "FE", "note2" ] },
{"id": "30", "cell": ["test3", "2007-09-01", "400.00", "30.00", "430.00", "false", "FE", "note3" ] },
{"id": "40", "cell": ["test4", "2007-10-04", "200.00", "10.00", "210.00", "true", "TN", "note4" ] },
{"id": "50", "cell": ["test5", "2007-10-31", "300.00", "20.00", "320.00", "false", "FE", "note5" ] },
{"id": "60", "cell": ["test6", "2007-09-06", "400.00", "30.00", "430.00", "false", "FE", "note6" ] },
{"id": "70", "cell": ["test7", "2007-10-04", "200.00", "10.00", "210.00", "true", "TN", "note7" ] },
{"id": "80", "cell": ["test8", "2007-10-03", "300.00", "20.00", "320.00", "false", "FE", "note8" ] },
{"id": "90", "cell": ["test9", "2007-09-01", "400.00", "30.00", "430.00", "false", "TN", "note9" ] },
{"id": "100", "cell": ["test10", "2007-09-08", "500.00", "30.00", "530.00", "true", "TN", "note10"] },
{"id": "110", "cell": ["test11", "2007-09-08", "500.00", "30.00", "530.00", "false", "FE", "note11"] },
{"id": "120", "cell": ["test12", "2007-09-10", "500.00", "30.00", "530.00", "false", "FE", "note12"] }
]
}
The most important part of the JavaScript code is
jsonReader: { root: "data" },
beforeProcessing: function (data) {
var $self = $(this), model = data.model, name, $colHeader, $sortingIcons;
if (model) {
for (name in model) {
if (model.hasOwnProperty(name)) {
$colHeader = $("#jqgh_" + $.jgrid.jqID(this.id + "_" + name));
$sortingIcons = $colHeader.find(">span.s-ico");
$colHeader.text(model[name].label);
$colHeader.append($sortingIcons);
}
}
}
}
Full JavaScript used in the demo is below
var $grid = $("#list");
$grid.jqGrid({
url: "DynamicHeaderProperties.json",
datatype: "json",
colModel: [
{ name: "c1", width: 70 },
{ name: "c2", width: 80, align: "center", sorttype: "date",
formatter: "date", formatoptions: {newformat: "m/d/Y"}, datefmt: "m/d/Y"},
{ name: "c3", width: 70, formatter: "number", align: "right",
editrules: {required: true, number: true}, editable: true},
{ name: "c4", width: 60, formatter:"number", align: "right", editable: true,
editrules:{required: true, number: true}},
{ name: "c5", width: 110, formatter: "number", align:"right",
editrules:{required:true,number: true}, editable: true},
{ name: "c6", width: 80, align: "center", editable: true,
formatter:"checkbox",edittype: "checkbox", editoptions: {value: "Yes:No", defaultValue: "Yes"}},
{ name: "c7", width: 110, align: "center", formatter: "select", editable: true,
edittype: "select", editoptions: {value: "FE:FedEx;TN:TNT;IN:Intim", defaultValue: "Intime"}},
{ name: "c8", width: 90, sortable: false, editable:true}
],
rowNum: 10,
rowList: [5,10,20],
pager: "#pager",
gridview: true,
rownumbers: true,
sortname: "c2",
viewrecords: true,
sortorder: "desc",
caption: "Setting coloumn headers dynamicaly",
jsonReader: { root: "data" },
beforeProcessing: function (data) {
var $self = $(this), model = data.model, name, $colHeader, $sortingIcons;
}}Related Topics
How to Build Splash Screen in Windows Forms Application
Get Output Parameter Value in Ado.Net
Use Linq to Xml With Xml Namespaces
Translucent Circular Control With Text
Use a Variable from Another Method in C#
Fluent and Query Expression - Is There Any Benefit(S) of One Over Other
How to Get the Color from a Hexadecimal Color Code Using .Net
How to Increase the Max Upload File Size in Asp.Net
Json.Net Serialization of Type With Polymorphic Child Object
Using Parameters Inserting Data into Access Database
Cast Generic≪Derived≫ to Generic≪Base≫
How to Deserialize an Array of Values With a Fixed Schema to a Strongly Typed Data Class
Simple and Tested Online Regex Containing Regex Delimiters Does Not Work in C# Code
Double to String Conversion Without Scientific Notation