As with several of the earlier SPServices Stories posts, I found this one interesting because it shows how to use SPServices with another framework to render the results obtained from the Web Services calls.
I’ve had several people tell me that there isn’t enough “story” to these SPServices Stories. Have a better one? Let me know!
jqGrid with SPServices
This article is regarding implementation of jqGrid(demo) using SPService(CodePlex)
SPService is a jQuery library which abstracts SharePoint’s Web Services and makes them easier to use. It also includes functions which use the various Web Service operations to provide more useful (and cool) capabilities. It works entirely client side and requires no server installation.
Use of SPService and jQuery is best explained at Marc’s blog.
Following Js files are needed for JqGrid Implementation with SPService
jquery-1.8.2.js
grid.locale-en.js
jquery.jqGrid.min.js // Structuring jqGrid
json2-min.js // Parsing data to json format
jquery.SPServices-0.7.2.js // For getting the list items
In the above jqGrid load function I have mentioned the datatype for the grid as GetMyData() which is a function that gets triggerred first.
The GetMyData method has function GetDataOnLoad which uses the SpServices which has the basic operation of getting the list items i.e. GetListItems, which need optional CAML Query property which will fetch the data from list with some WHERE clause.
In the code I have a list called ProjectDetailsList which will contain details of some projects which are inserted by some Project Manager or delivery manager. So the requirement was when a user log in to the system I should get the current login user name and pass the same user name to the “where” clause of query so the grid will contain data of projects to which the current logged in user is assigned as PM or DM.
To get the current login user am using SpServices Operation SpGetCurrentUser.
The method GetTheOrderByType function will make the query part for SpServices.
//This function gets the data from List using SpServices
65
Function GetDataOnLoad(Query, CAMLViewFields) {
66
$().SPServices({
67
operation : "GetListItems",
68
async : false,
69
listName : "ProjectDetailsList",
70
CAMLQuery : Query,
71
CAMLViewFields : CAMLViewFields,
72
completefunc : processResult
73
});
74
}
The processResult is the function which formats the data which can be converted to Json and adds to the JqGrid.
The reason of formatting of data in the following particular format is to make it readable by the Json parser which is json2.js file. I had implemented the same JqGrid in asp.net application with AJAX calls where it was returning the data in this format and some other bloggers also used the same data format in the MVC or asp.net application with the help for JsonHelper class which mainly formats the data returned from the DB
//Processing the XML result to formatted Json so that We can bind data to grid in Json format
2
functionprocessResult(xData, status) {
3
varcounter = 0; // Gets the total number of records retrieved from the list (We can also use xData.ItemCount method for counting the number of rows in the data )
thegrid.addJSONData(JSON.parse(newStr)); //Binding data to the grid which is of JSON Format
14
}
And the grid works fine and fast on paging, sorting and also even search, we can make the particular column as hyperlink which I will blog in the next part. Sample grid is as follows and this grid has some extra columns then the mentioned in above code
Hi Marc,
I’m using an array as datasource for my jqgrid, i’m also implementing groupby on my grid. This groupby is working fine in Firefox and Chrome, but in IE it’s not working. In IE grouping is there, but when i click on ‘+’ icon to expand my items,it’s showing no responce. Any help…
Please share me the exact code and the way how you are using the Group By.
I have implemented the jqGrid, with sorting and paging function. So let me try to help you.
I have one requirement
I have 1 list with key | Exchange Rate two columns
List 1:
key Exchange Rate
A 0.5
B 2
C 2
D 3
>>>>>>>>>
List 2: another list Amount List Key Amount
key Amount
A 1000 1000
A 2000 2000
B 3000 3000
whenever users open any particular view (example test.apsx) of second list ( customized in SPD)
I have to get below results depending upon the exchange rate defined in configuration list 1
I,e exchange rate * Amount .. note: exchange rate always changes every 2 days. so runtime it has to multiple and show it in the any customized view
Hi Marc,
I like these spservices-stories. But one thing that would make it easier for me, a visual learner, is to see the application in action. When I see it, I can follow the code better.
thx
That’s a great idea. In many cases, the stories are boiled down from in-place production systems. Those systems are behind firewalls and not readily visible to anyone. That said, I could try to include short videos in the future, where possible.
I have not tried jqGrid but I have used jQuery Datatables extensively https://datatables.net/. In some cases I will just built the html table with the results from SPServices, other cases I will build a javascript object for the table.
I have not looked in detail but it looks like you have a trailing comma in your JSON object. This will work in Chrome and FF but it will not work in IE <10. IE will throw a cryptic error (can't remember at this time).
You want your output to be like:
{id:'10',cell:['10','Name','Manager']}
Finally, a snippet of code I have been using (and I can't remember where I got it, maybe even you) was to map the entire result-set of attributes to a JavaScript array, this loops through all of the attributes and applies it to a name value pair and strips the ows_ from the name. The result is an array of results which you can call using the javascript dot notation.
var navOptions = new Array();
jQuery(xData.responseXML).SPFilterNode("z:row").each(function() {
var item = jQuery(this);
jQuery.each(this.attributes, function(i, attrib) {
var name = attrib.name;
var value = attrib.value;
navigation[name.replace('ows_', '')] = value;
});
navOptions.push(navigation);
navigation = {};
});
Hi Marc,
I’m using an array as datasource for my jqgrid, i’m also implementing groupby on my grid. This groupby is working fine in Firefox and Chrome, but in IE it’s not working. In IE grouping is there, but when i click on ‘+’ icon to expand my items,it’s showing no responce. Any help…
kartik:
It’s very hard to tall from here. I’d do some debugging to see whether there’s an error.
M.
Hi Karthik
Please share me the exact code and the way how you are using the Group By.
I have implemented the jqGrid, with sorting and paging function. So let me try to help you.
Thanks
Prateek
Hi Marc,
I have one requirement
I have 1 list with key | Exchange Rate two columns
List 1:
key Exchange Rate
A 0.5
B 2
C 2
D 3
>>>>>>>>>
List 2: another list Amount List Key Amount
key Amount
A 1000 1000
A 2000 2000
B 3000 3000
whenever users open any particular view (example test.apsx) of second list ( customized in SPD)
I have to get below results depending upon the exchange rate defined in configuration list 1
I,e exchange rate * Amount .. note: exchange rate always changes every 2 days. so runtime it has to multiple and show it in the any customized view
key Amount
A 500
A 1000
B 6000
how can I do it through OOTB
hi Marc,
i achieved that requirement
http://maratheguruprasad.blogspot.in/2013/10/showing-different-values-in-sharepoint.html
Hi Marc,
I like these spservices-stories. But one thing that would make it easier for me, a visual learner, is to see the application in action. When I see it, I can follow the code better.
thx
Rich:
That’s a great idea. In many cases, the stories are boiled down from in-place production systems. Those systems are behind firewalls and not readily visible to anyone. That said, I could try to include short videos in the future, where possible.
M.
I have not tried jqGrid but I have used jQuery Datatables extensively https://datatables.net/. In some cases I will just built the html table with the results from SPServices, other cases I will build a javascript object for the table.
I have not looked in detail but it looks like you have a trailing comma in your JSON object. This will work in Chrome and FF but it will not work in IE <10. IE will throw a cryptic error (can't remember at this time).
You want your output to be like:
{id:'10',cell:['10','Name','Manager']}
Finally, a snippet of code I have been using (and I can't remember where I got it, maybe even you) was to map the entire result-set of attributes to a JavaScript array, this loops through all of the attributes and applies it to a name value pair and strips the ows_ from the name. The result is an array of results which you can call using the javascript dot notation.
var navOptions = new Array();
jQuery(xData.responseXML).SPFilterNode("z:row").each(function() {
var item = jQuery(this);
jQuery.each(this.attributes, function(i, attrib) {
var name = attrib.name;
var value = attrib.value;
navigation[name.replace('ows_', '')] = value;
});
navOptions.push(navigation);
navigation = {};
});
Hi sir, Am getting this error Cannot read property ‘addJSONData’ of undefined
I know this is a very late response. But, im stuck with this
thegrid.addJSONData(JSON.parse(newStr));
its not binding the newStr value to the Grid. i’ve loaded the Json.min file correctly. But still, i’m not getting the result. Any suggestions ?
Even I have the same problem, Cannot bind data to the grid.thegrid.addJSONData(JSON.parse(newStr));