Archive | March 2013

jqGrid Implementation Using SpServices in SharePoint


Today’s SPServices Story comes from Prateek Kulkarni in Bengaluru, India. Prateek posted this originally on the C# Corner site as jqGrid Implementation Using SpServices in SharePoint.

As with several of the earlier SPServices Stories posts, Marc found this one interesting because it shows how to use SPServices with another framework to render the results obtained from the Web Services calls.

Marc have had several people telling him that there isn’t enough “story” to these SPServices Stories. Have a better one? Let him 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

Css file required

  • ui.jqgrid.css  //Style sheet of Grid

HTML controls for jqGrid are as mentioned below.

1 <div id='tblMain' style="float:left">
2   <table  id="list" ></table>
3   <div id="pager" style="text-align:center;"></div>
4 </div>

Loading JqGrid on the page load:

1 jQuery("#list").jqGrid({
2   datatype: GetMyData,
3   colNames:["Project ID","Project Name","Delivery Manager","ApprovalStatus"],
4   colModel:[{name:'ProjectId',index:'ProjectId',align:'left',sortable: true},
5   {name:'ProjectName',index:'ProjectName',align:'left',sortable: true },
6   {name:'DeliveryManager',index:'DeliveryManager',align:'left',sortable:true},
7   {name:'ApprovalStatus',index:'ApprovalStatus',align: 'left',sortable: true }
8     ],
9   pager: true,
10   pager: '#pager',
11   pageinput: true,
12   rowNum: 5,
13   rowList: [5, 10, 20, 50, 100],
14   sortname: 'ApprovalStatus',
15   sortorder: "asc",
16   viewrecords: true,
17   autowidth: true,
18   emptyrecords: "No records to view",
19   loadtext: "Loading..."
20 });

In the above jqGrid load function Marc has 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 Marc has 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 one 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.

The functions code is as follows:

1 function ForGettingUserName() {
2   var userName = $().SPServices.SPGetCurrentUser({
3       fieldName : "Title",
4       debug : false
5     });
6   return userName;
7 }
9 function GetMyData() {
10   sortIdexName = jQuery("#list").getGridParam("sortname"); //Maintaining Consitant SortName after the Sortcol event
11   sortOrderName = jQuery("#list").getGridParam("sortorder"); //Maintaining Consistant Sort Order
12   Query = GetTheOrderByType(sortIdexName, sortOrderName);
13   var CAMLViewFields = "<ViewFields>" +
14      "<FieldRef Name='projectName' /><FieldRef Name='projectID' />"
15      "<FieldRef Name='Title' /><FieldRef Name='deliveryManager' />"
16      "<FieldRef Name='projectSQA' /><FieldRef Name='approvalStatus' />"
17      "<FieldRef Name='projectStartDate' /><FieldRef Name='projectEndDate' />"
18      "<FieldRef Name='sqasiteurl' /><FieldRef Name='ID' />"
19      "</ViewFields>";
20   GetDataOnLoad(Query, CAMLViewFields);
21 }
22 Function for Getting the Query Type and the particular WHERE CLAUSE TO send the Query to GetListItem function
23 function GetTheOrderByType(index, sortOrder, userName) {
24   var OrderByType;
25   if (index == "ProjectName") {
26     if (sortOrder == "desc") {
27       OrderByType = "<Query>" +
28          "<Where><Or>" "<Eq>" "<FieldRef Name='deliveryManager'/><Value Type='Text'>"
29          + userName + "</Value>" "</Eq>"
30          "<Eq><FieldRef Name='projectManager'/><Value Type='Text'>"
31          + userName + "</Value></Eq>" "</Or></Where>" +
32          "<OrderBy><FieldRef  Name='projectName' Ascending='FALSE' /></OrderBy>" +
33          "</Query>";
34     else {
35       OrderByType = "<Query>" +
36          "<Where><Or>" "<Eq>" "<FieldRef Name='deliveryManager'/><Value Type='Text'>"
37          + userName + "</Value>" "</Eq>"
38          "<Eq><FieldRef Name='projectManager'/><Value Type='Text'>"
39          + userName + "</Value></Eq>" "</Or></Where>" +
40          "<OrderBy><FieldRef  Name='projectName' Ascending='FALSE' /></OrderBy>" +
41          "</Query>";
42     }
43   else if (index == "ApprovalStatus") {
44     if (sortOrder == "desc") {
45       OrderByType = "<Query>" +
46          "<Where><Or>" "<Eq>" "<FieldRef Name='deliveryManager'/><Value Type='Text'>"
47          + userName + "</Value>" "</Eq>"
48          "<Eq><FieldRef Name='projectManager'/><Value Type='Text'>"
49          + userName + "</Value></Eq>" "</Or></Where>" +
50          "<OrderBy><FieldRef  Name='approvalStatus' Ascending='FALSE' /></OrderBy>" +
51          "</Query>";
52     else {
53       OrderByType = "<Query>" +
54          "<Where><Or>" "<Eq>" "<FieldRef Name='deliveryManager'/><Value Type='Text'>"
55          + userName + "</Value>" "</Eq>"
56          "<Eq><FieldRef Name='projectManager'/><Value Type='Text'>"
57          + userName + "</Value></Eq>" "</Or></Where>" +
58          "<OrderBy><FieldRef  Name='approvalStatus' Ascending='FALSE' /></OrderBy>" +
59          "</Query>";
60     }
61     return OrderByType;
62   }
64   //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. Marc has implemented the same JqGrid in 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 application with the help for JsonHelper class which mainly formats the data returned from the DB

1 //Processing the XML result to formatted Json so that We can bind data to grid in Json format
2 function processResult(xData, status) {
3   var counter = 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 )
4   var newJqData = "";
6   $(xData.responseXML).SPFilterNode("z:row").each(function () {
8     var JqData;
9     if (counter == 0) {
10       JqData = "{id:'" + $(this).attr("ows_projectID") + "',"
11          "cell:[" "'" + $(this).attr("ows_projectID") + "','" +
12         $(this).attr("ows_projectName") + "','" +
13         $(this).attr("ows_deliveryManager") + "','," +
14         "]}";
15       newJqData = newJqData + JqData;
16       counter = counter + 1;
17     else {
18       var JqData = "{id:'" + $(this).attr("ows_projectID") + "',"
19          "cell:[" "'" + $(this).attr("ows_projectID") + "','" +
20         $(this).attr("ows_projectName") + "','" +
21         $(this).attr("ows_deliveryManager") + "','," +
22         "]}";
23       newJqData = newJqData + JqData;
24       counter = counter + 1;
25     }
27   });
28   FinalDataForGrid(newJqData, counter);
29 }

That’s it. Add the data to the grid with the div control and the other page number calculation is for showing the pager.

1 function FinalDataForGrid(jqData, resultCount) {
2   dataFromList = jqData.substring(0, jqData.length - 1);
3   var currentValue = jQuery("#list").getGridParam('rowNum');
4   var totalPages = Math.ceil(resultCount / currentValue);
5   var PageNumber = jQuery("#list").getGridParam("page"); // Current page number selected in the selection box of the JqGrid
6   //formatting rows
7   newStr = "{total:" '"' + totalPages + '"' "," "page:" '"' + PageNumber + '"'","
8      "records:" '"'
9      + resultCount + '"' + ","
10      "rows:"
11      "[" + dataFromList + "]}";
12   var thegrid = jQuery("#list")[0];
13   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 Marc will blog in the next part. Sample grid is as follows and this grid has some extra columns then the mentioned in above code.

This Post was originally posted at Marc D Anderson’s Blog


Displaying attachment link in a SharePoint custom list form – “DispForm.aspx”

Recently while working with SharePoint lists I required to customize the out-of-the-box list item display form “DispForm.aspx” to present a design-frenzy item view format.

During the modification process, I never assumed that the list item attachments would abruptly dissapear from the display form. All I was looking to do was to display the list item in a good looking format by modifying the XSLT, but the attachments were gone!

Lesson Learnt: Never modify the out-of-the-box list item display form “DispForm.aspx”. Create a new one if you need to and call it “DispForm_WhateverYouCallIt.aspx”

Now the attachments that were gone seemed quite easy to be brought back on the page. It needed a SharePoint control AttachmentsField that went missing from the picture. Below is the code that you need to add into the XSLT template dvt_1.rowview (into your custom form) that constructs theDataFormWebPart that SharePoint Designer creates for you when you insert a Custom List Form:

Infopath cannot save the following form: http://servername….. This document library was either renamed or deleted, or network problem are preventing the file from being saved. If this problem persists, contact your administrator.

This error started appearing every time I try to publish a particular template back to the library.  I’ve been working with this template all week without any issues until this.  The document library wasn’t renamed or deleted.  I can edit and publish changes to form library templates in other site collections without a problem.  This particular form library template does not contain any custom code.

Now in this case, there are couple of things you should try as suggested by Carl G – DC

  • Go to another PC and log-in into windows with your account, then open the template from the library and publish back to the library
  • Open IP Designer on your machine, open an old version of the form, and choose File | Publish | SharePoint Server and choose the same library
  • Clear the InfoPath cache (Start | Run | InfoPath /cache clearall)
  • From IP Designer, File | Publish | SharePoint Server, but this time enter full URL of file path (including the template.xsn)
  • Navigated to Library Settings | Re-link documents to this library, then selected the only submitted document and clicked Re-link All.  Then edit the template and try to publish back to the library
  • Open IP Designer on your machine, open an old version of the form, and choose File | Publish | SharePoint Server and choose to publish to a new library.  Then edit the template in that new library and published again
  • Also try cranking up monitoring for InfoPath Forms Services in Central Admin, repeat the same test of editing and publishing the template and then check the Windows Events Logs on all SharePoint servers in the farm.
%d bloggers like this: