Finding a Task’s Status Using GetListItems and SPServices in SharePoint 2013

I get interesting questions all this time. This one came from a client who was trying to use GetListItems with SPServices in SharePoint .

Hey Marc, I’m trying to do a GetListItems operation on tasks list and I’m having trouble with the “Task Status” column. This is SP2013.

var thisTaskStatus = $.trim($(this).attr("Status"));
var thisTaskStatus = $.trim($(this).attr("ows_Status"));
var thisTaskStatus = $.trim($(this).attr("ows_Task_x0020_Status"));

None of the above work and I went into the edit column settings of the task list and clicked on the status column and it says the field name is “Status”. When I look at the XML returned using Firebug, a lot of other fields show up like Title, Due Date, Created By, etc. but I don’t see the status column showing up at all in the responseXML.

Many of the out of the box columns have rather obscure StaticNames. In a Tasks list, the column which has the “Status” DisplayName has a StaticName of “Status” as well, so that’s pretty simple. Here’s the field definition, which I grabbed by calling GetList on my own Tasks list:

<Field Type="Choice" ID="{c15b34c3-ce7d-490a-b133-3f4de8801b76}" Name="Status" DisplayName="Task Status" SourceID="http://schemas.microsoft.com/sharepoint/v3" StaticName="Status" ColName="nvarchar4">
  <CHOICES>
   <CHOICE>Not Started</CHOICE>
   <CHOICE>In Progress</CHOICE>
   <CHOICE>Completed</CHOICE>
   <CHOICE>Deferred</CHOICE>
   <CHOICE>Waiting on someone else</CHOICE>
  </CHOICES>
  <MAPPINGS>
    <MAPPING Value="1">Not Started</MAPPING>
    <MAPPING Value="2">In Progress</MAPPING>
    <MAPPING Value="3">Completed</MAPPING>
    <MAPPING Value="4">Deferred</MAPPING>
    <MAPPING Value="5">Waiting on someone else</MAPPING>
  </MAPPINGS>
  <Default>Not Started</Default>
</Field>

Calling GetList to take a look at the list schema is a good way to keep yourself from going crazy.

Now that said, you don’t always get all of the columns for a list when you call GetListItems. By default, you get the columns which are shown in the default view.

In an out of the box Tasks list that I just created, when I make this call:

$().SPServices({
  operation: "GetListItems",
    listName: "Tasks"
});

Status isn’t returned because it’s not in the default view.

<z:row
  ows_Checkmark="boolean;#0"
  ows_LinkTitle="boo"
  ows_AssignedTo=""
  ows_PercentComplete="0.500000000000000"
  ows__ModerationStatus="0"
  ows__Level="1"
  ows_Title="boo"
  ows_ID="1"
  ows_UniqueId="1;#{CC125F6A-F329-46D7-8140-F9353F1AC7EC}"
  ows_owshiddenversion="1"
  ows_FSObjType="1;#0"
  ows_Created_x0020_Date="1;#2014-01-29 13:04:43"
  ows_Created="2014-01-29 13:04:43"
  ows_FileLeafRef="1;#1_.000"
  ows_PermMask="0x7fffffffffffffff"
  ows_Modified="2014-01-29 13:04:43"
  ows_FileRef="1;#sites/Demos2013/jquerylib/Lists/Tasks/1_.000">
</z:row>

Instead, you can add the CAMLViewFields option to request it:

$().SPServices({
  operation: "GetListItems",
    listName: "Tasks",
    CAMLViewFields: "<ViewFields><FieldRef Name='Status'/></ViewFields>"
});

Then I get:

<z:row
  ows_Status="Deferred" 
  ows_MetaInfo="1;#" 
  ows__ModerationStatus="0" 
  ows__Level="1"
  ows_Title="boo" 
  ows_ID="1" 
  ows_UniqueId="1;#{CC125F6A-F329-46D7-8140-F9353F1AC7EC}" 
  ows_owshiddenversion="2" 
  ows_FSObjType="1;#0" 
  ows_Created="2014-01-29 13:04:43" 
  ows_PermMask="0x7fffffffffffffff" 
  ows_Modified="2014-01-29 13:11:21" 
  ows_FileRef="1;#sites/Demos2013/jquerylib/Lists/Tasks/1_.000">
</z:row>

Being explicit about the columns you want to retrieve is always a good practice. People can change the default view easily.

SPServices Stories #19 – Folders in SharePoint are as necessary as evil. Make the best of it using jQuery and SPServices.

Introduction

Ever on the hunt for good SPServices Stories, I spotted this cool one a few weeks back when Patrick Penn (@nfpenn) posted a screenshot of something he had done on Twitter. I encouraged him to do a post about it and this is the result: Folders in SharePoint are as necessary as evil. Make the best of it using jQuery and SPServices.

I liked this post for several reasons. First, it’s a great example of how we can improve the user experience (UX) with SPServices and client side scripting. Second, Patrick tells us in some depth what he was trying to accomplish from a non-technical perspective and how he made it work. Some have accused me of posting purely technical pieces in this series without enough story to them; this post has both.
A few notes from my end:

  • SPServices has a function to parse the query string called SPGetQueryString, so Patrick’s function getQueryStrings isn’t really needed.
  • Using the .find(“z\\:row, row”) syntax will cause issues in some browsers due to the z namespace. I’ve got a function in SPServices called SPFilterNode which you should use instead: .SPFilterNode(“z:row”). The function will work with any selectors in the XML (at least all that I’ve tried), but you should definitely use it for z:row and rs:data. In other words, use SPFilterNode anywhere you see namespacing in an XML node.

Patrick PennPatrick Penn (@nfpenn) is a SharePoint Architect at netflower (http://netflower.de) in Germany. He loves SharePoint but also knows its weak points. Based on this knowledge he gives advice to clients and builds custom-made solutions to improve efficiency and usability within SharePoint.

Folders in SharePoint are as necessary as evil. Make the best of it using jQuery and SPServices.

Let me say right upfront, this post is not about Folders vs Metadata. If you’re searching for that, you will find a rather good one here.

Hopefully you know about the benefits of SharePoint and its features like, enterprise keywords, taxonomy and metadata navigation. But sometimes you or your client need a good old folder hierarchy. If you’re a valuable consultant you will neither roll your eyes nor surrender but assure him with a smile that you will build a solution that will absolutely meet his needs.

Some Reasons Why Folders are Necessary

Some logical arguments for using folders in SharePoint are:

  • If you have many document types which need different permissions within a single document library. Sure, you can configure dedicated permissions for each single document, but this may be hard to maintain.
  • If your customer needs a quick solution to share documents without manually setting managed metadata for each single document, because they often upload documents in a bulk.
  • If you need to logically group different document types and provide a dynamically generated status based on documents or its metadata, which needs to be displayed on a higher hierarchy level to provide an consolidated overview about the content. Sounds complicated? Practically speaking it may be needed to show a completeness status about documents to deliver, which brought me to the solution dealt with in this post.
  • Another reason is to simply not to overstrain the users, if they’re new to SharePoint. They quite likely know the folder structure and you as a consultant have the possibility to provide a solution which include the best of both worlds. To work future-oriented, be creative, for example you’re able to automatically define metadata predefined by a documents name or its parent folder or even better by its content. There’s an outdated solution on Codeplex, that may give you an idea. Maybe I dedicate a post to this topic in the near future.

While you’re reading this, you may think: “Why the hell didn’t he use Document Sets?”.
We involved this feature in our planning, but there is no metadata navigation within Document Sets, which could have been an advantage. Furthermore the customer needed a multi-level hierarchical structure. As we had no significant arguments for it, the latter was a show stopper for Document Sets.

How to Pep Up Folders

As you can imagine, you have many possibilities to get more out of old and boring folders. For example JSLink may be your favorite approach, but I didn’t use it, because this is a migrated solution.

Something like the following is a simple approach to provide much more value to folders.

peppedup_folders_1-1The presumably simplest way may be consulting SharePoint Designer and add conditional formatting to change the presentation of a document library view.
But if you think further and consider a more professional deployment you may realize, that there must be a better way. Also using XSLT will not be fun to handle the content of multiple subfolders and I’m sure the result will not be a smooth experience either.

To keep things simple and manageable I decided to use jQuery and a powerful javascript library from SharePoint MVP Marc D. Anderson, author of SPServices.

I’m not allowed to publish the whole source code regarding this solution, due to the NDA with our client, but I will hopefully give you enough information to build a solution like this by yourself. Sorry for that!

Let’s Begin

I used the following javascript libraries for SharePoint 2013:
jQuery 1.10.2 and SPServices 2013.02a

Because I use jQuery and SPServices in multiple places I decided to place the script references within the custom masterpage. You can do it manually or use a more professional approach like this, by using a custom delegate control.

For testing purpose you can simply add a reference within the content editor webpart. But be sure to implement this before you call the functions.

<script src="/Style%20Library/scripts/jquery/jquery-1.10.2.min.js" type=text/javascript></script>
<script src="/Style%20Library/scripts/jquery/jquery.SPServices-201302a.js" type=text/javascript></script>

Congrats! Now you’re able to use the full power of jQuery and SPServices!

I want to keep things as simple as possible. So to implement the pepped up folder (PUF) functionality within a document library, I just added a content editor webpart (CEWP) below the list view, which is invisible for users.

webpart_placholders_1-1Then I added a reference (Content Link) for the PUF-script.

webpart_contentlink_1-1To load a .js file as Content Link you should put the whole code between these tags. The alternative is to use a simple .txt file.

<script type="text/javascript">
<!--
  //your code here
-->
</script>

I like the way using .js files, because of reusability outside a CEWP Content Link.

Logic

  • folder-2default folder: Containing files within the folder or in any of its subfolders. The user knows that there will be content and the click will not be for nothing
  • folder_empty-2empty folder: No files are contained within the folder and each of its subfolders. So the user doesn’t have to look for any content and knows that there is still something to deliver.
  • folder_not_reviewed-2not reviewed folder: No files are contained within the folder and each of its subfolders and the status is “not reviewed”.
  • folder_reviewed-2reviewed folder: The folder status is set to “reviewed” or all child folders are set to “reviewed”. Sometimes there are no files to deliver for a specific folder, so it can directly be marked as “reviewed”.

Optionally you can notify the user that pepping up begins and load the pepUpFolders function with a short delay.

$(document).ready(function(){
  var loadingNotifyId = SP.UI.Notify.addNotification('Pepping up folders ...', false);
  setTimeout('pepUpFolders();',1100);
});

It may happen, that folders are already updated before the user is able to read the notification, so the delay helps.
You can decide which approach is the best for your users. Our customer wanted to notify users about what’s going to happen.

Use this function to retrieve the document libraries root folder from the url parameter if you’re currently in any subfolder.

function getQueryStrings() {
  var assoc  = {};
  var decode = function (s) { return decodeURIComponent(s.replace(/\+/g, " ")); };
  var queryString = location.search.substring(1);
  var keyValues = queryString.split('&');

  for(var i in keyValues) {
    var key = keyValues[i].split('=');
    if (key.length > 1) {
      assoc[decode(key[0])] = decode(key[1]);
    }
  }

  return assoc;
}

Last but not least the more exciting part. This is where the magic happens.

As I mentioned before this function is a bit truncated, but for the result you’ll see a difference regarding folders with and without content. The nice part is, that the most functionality loads asynchronously so the user feels nearly no delay, when navigating through the folder structure.

function pepUpFolders() {
    $(document).ready(function() {
        var sitecollectionUrl = _spPageContextInfo.siteServerRelativeUrl;
        if (sitecollectionUrl == "/") {
            sitecollectionUrl = "";
        }
        var emptyFolderIconPath = sitecollectionUrl + "/Style Library/scripts/images/folder_empty.gif";
        var folderIconPath = sitecollectionUrl + "/_layouts/15/images/folder.gif?rev=23";
        var loaderIconPath = sitecollectionUrl + "/Style Library/scripts/images/loading.gif";
        var folderPrefix = "";

        /* You need this prefix for SharePoint 2010 to replace folder icons

        switch(_spPageContextInfo.currentLanguage)
        {
          case 1031:
          folderPrefix = "";//"Ordner: ";
          break;

          case 1033:
            folderPrefix = "";//"Folder: ";
            break;
        }*/

        var folderStatusReviewedString = "reviewed";

        var listName = $().SPServices.SPListNameFromUrl();
        var siteUrl = $().SPServices.SPGetCurrentSite();

        //Parsing the server relative url of the current web
        if (siteUrl.startsWith("http")) {
            var siteServerRelativeUrl = siteUrl.match(/\/[^\/]+(.+)?/)[1] + "/";
        } else {
            var siteServerRelativeUrl = siteUrl;
        }

        var currentFolderPath;
        var qs = getQueryStrings();
        var rootFolder = decodeURI(qs["RootFolder"]);

        //If the rootFolder is not set, then we are currently in the root folder
        if (rootFolder != null) {
            rootFolder = rootFolder.replace(siteServerRelativeUrl, "");
            currentFolderPath = rootFolder;
        } else {
            //The root folder is not set, then we need to get the rootFolder from list
            $().SPServices({
                operation: "GetList",
                async: false,
                listName: listName,
                completefunc: function(xData, Status) {
                    $(xData.responseXML).find("List").each(function() {
                        currentFolderPath = $(this).attr("RootFolder");
                    });
                }
            });
        }

        currentFolderPath = currentFolderPath.replace(siteServerRelativeUrl, "");
        parentFolderPath = currentFolderPath.substring(0, currentFolderPath.lastIndexOf('/'));
        parentFolderName = currentFolderPath.substring(currentFolderPath.lastIndexOf('/') + 1);

        //We request only a list of folders as we don't need to inspect files from current folder
        var query = "<Query><Where><Eq><FieldRef Name='FSObjType'></FieldRef><Value Type='Lookup'>1</Value></Eq></Where></Query>";
        var queryOptions = '<QueryOptions><Folder><![CDATA[' + currentFolderPath.substring(1) + ']]></Folder></QueryOptions>';
        var viewFields = "<ViewFields Properties='true'><FieldRef Name='Level' /></ViewFields>";

        var promFolders = [];
        promFolders[0] = $().SPServices({
            operation: "GetListItems",
            listName: listName,
            CAMLQuery: query,
            CAMLQueryOptions: queryOptions,
            CAMLViewFields: viewFields
        });

        var promSubFolders = [];
        $.when.apply($, promFolders).done(function() {
            $(promFolders[0].responseXML).SPFilterNode("z:row").each(function() {
                var subFolderPath = $(this).attr("ows_FileRef").split(";#")[1];
                subFolderPath = subFolderPath.replace(siteServerRelativeUrl.substring(1), "").substring(1);
                var subFolderName = $(this).attr("ows_FileLeafRef").split(";#")[1];
                $("[title='" + folderPrefix + subFolderName + "']").attr("src", loaderIconPath);

                //Search for Files in any subfolder and return 1 item per Folder max.
                var query = "<Query><Where><Eq><FieldRef Name='FSObjType'></FieldRef><Value Type='Lookup'>0</Value></Eq></Where></Query>";
                var queryOptions = "<QueryOptions><Folder><![CDATA[" + subFolderPath + "]]></Folder><ViewAttributes Scope='Recursive' /></QueryOptions>";

                $().SPServices({
                    operation: "GetListItems",
                    async: true,
                    listName: listName,
                    CAMLRowLimit: 1,
                    CAMLQuery: query,
                    CAMLQueryOptions: queryOptions,
                    completefunc: function(xData, Status) {
                        //Replace Folder Icon with empty Folder icon
                        if ($(xData.responseXML).find("z\\:row, row").length == 0) {
                            $("[title='" + folderPrefix + subFolderName + "']").attr("src", emptyFolderIconPath);
                        } else {
                            $("[title='" + folderPrefix + subFolderName + "']").attr("src", folderIconPath);
                        }
                    }
                });
            });
        });
    });
}

function getQueryStrings() {
    var assoc = {};
    var decode = function(s) {
        return decodeURIComponent(s.replace(/\+/g, " "));
    };
    var queryString = location.search.substring(1);
    var keyValues = queryString.split('&');

    for (var i in keyValues) {
        var key = keyValues[i].split('=');
        if (key.length > 1) {
            assoc[decode(key[0])] = decode(key[1]);
        }
    }
    return assoc;
}

I’m sure there is something to optimize. But the intention of this post was to show a solution to pep up boring folders and make them more valuable.

I was struggling with async calls somehow, so Marc D. Anderson recommended me to use promises instead of regular async calls, because of a better controllable program flow.

So, I updated my code and combined both approaches, because of depending calls. Now everything seems to work as expected and it’s clear what javascript promises are and how they can help to improve program flow.

I hope this post gives you some fresh ideas how to gain more value out of folders, because they are still necessary, albeit evil.

If you need some advice feel free to contact me.

What Does It Take to Become Part of a Community Effort?

Mark Miller (@EUSP) and I sat down virtually yesterday to talk about being a member of a community. The context was the SharePoint community, of course, but we tried to keep it general enough to apply to pretty much any type of community. IMO, the same basic ideas apply to being in charge of your neighborhood social committee, a member of Congress, a speaker on a circuit, or a contributor to the SharePoint community.

All it takes great content, and the willingness to realize that you will act like a moron from time to time. Oh, and a love of bacon, but that’s probably optional.

The video is part of a series Mark is doing called Community Building – Real World Stories. He’s already interviewed a few others, with more to come.

This is part of a series of talks on how community leaders became engaged with their communities: how they found the community, the process they went through to become part of the community and insights into how you can become more credible and visible in your industry.

Next time I should probably set up my Webcam a little less slanty.

Office 365 Update Changes ‘Display Name’ on Required Fields

Observant SPServices user GregRT noticed something on Office365 today that I figured couldn’t be true. He posted the following in the discussions on the SPServices Codeplex site:

FYI – I had left my debug on.
Users were getting errors on there forms that a field could not be found by SPServices.
MSFT has changed the display name of required fields when it lands in the DOM. I had a cascade going from parentColumn ‘Division’ to childColumn ‘SalesCenter’ (both required fields) – working last week with the display names as ‘Divison’ and ‘SalesCenter’ … now when the form renders in the DOM the display names are ‘Division Required Field’ and ‘SalesCenter Required Field’ – no indication in the UI that this is modified.
Hope this helps someone!

My first reaction was to question whether this could be right.

For years, we’ve been able to reliably select a regular dropdown (select) easily by using the column’s Title. The markup has looked something like this (depending on SharePoint version):

&lt;select id=&quot;Region_59566f6f-1c3b-4efb-9b7b-6dbc35fe3b0a_$LookupField&quot; title=&quot;Region&quot;&gt;

2014-01-23_14-39-44so we could use a jQuery selector like this:

var regionSelect = $(&quot;select[Title='Region']&quot;);

However, GregT is right. In one of my demo sites on Office365, when I set a the Region column to required I’m seeing this markup instead:

&lt;select id=&quot;Region_59566f6f-1c3b-4efb-9b7b-6dbc35fe3b0a_$LookupField&quot; title=&quot;Region Required Field&quot;&gt;

2014-01-23_14-40-50I expect this will be a problem not just for everyone using SPServices, but for many custom scripts as well.

Not only does this become a breaking change, it’s just plain bad practice.

If you run into this issue, a quick fix solution would be:

var regionSelect = $(&quot;select[Title='Region'], select[Title='Region Required Field']&quot;);

It’s not enough for your selector to look for a Title which *starts* with ‘Region’; you might have a column called ‘Region2′ or ‘Regions’ as well, so you’d get false positives.

I’ll see if I can find out any more details on why this may have happened and whether it’s permanent. Ugh.

[notice]2014-01-24 8pm EST – Based on info from Christophe below and several others, the issue seems to arise somewhere between vti_buildversion:SR|16.0.2308.1208 and vti_buildversion:SR|16.0.2510.1204. You can check your version by going to  /_vti_pvt/buildversion.cnf in your Offic365 tenant. See: http://corypeters.net/2013/07/getting-the-product-version-for-sharepoint-online/[/notice]

[notice]2014-01-24 11pm EST – The PG reached out to me and they are investigating the issue. I’ll post more info as I get it.[/notice]

[notice]2014-01-31 9am EST – Based on the information I’ve gotten from the PG so far (not a lot), I’m going to assume this is a permanent change. The ostensible reason, as suggested by several people in the comments below, is to improve accessibility using screen readers. It’s hard to argue with that, but it’s still an unfortunate change.

I’ve also checked with a few people running non-English tenants, and the titles are localized, like so:

  • Swedish: “Namn Obligatoriskt fält” or “Name Required Field”.
  • Russion: “RegSelect Обязательное поле” or “RegSelect Required Field”
  • etc.

I’ve also received reports (several below in the comments) that this issue is popping up with SharePoint 2010 after applying the December CU. More on that as I get further details.

Based on this, I’m getting a fix ready in the 2014.01 release and will try to get it out there shortly. Methinks it’s going to become a game of catch up from now on.
[/notice]

[notice]2014-02-03 12:40m EST - SPServices 2014.01ALPHA2 posted with a fix for the issue on Office365. As soon as I can get an example of how the issues arises in SharePoint 2010, I hope to have that fix as well. [/notice]

[notice]2014-02-19 SPServices 2014.01 released with fixes for every implication of these changes of which I am aware. If you’ve run into this issue, please upgrade!
[/notice]

Getting User Information with the SharePoint 2013 REST API

Sometimes the tiniest little throw-away comment on an article out there somewhere can prove useful to someone. Sometimes, it’s even a comment I’ve made.

2014-01-21_16-43-35That’s Andrew “AC” Clark (@bitterac) who tweeted. I may get a club soda out of it, but at Andrew’s suggestion, I figured I’d put up a post as well.

There’s far less documentation about SharePoint 2013′s REST capabilities than most of us would like. As has happened so many times in the past, the blogosphere fills in the gaps.

The post where Andrew Clark found my comment was a great one by Andrew Connell (@andrewconnell) about Applying Filters to Lookup Fields with the SharePoint 2013 REST API.

Andrew Connell (this post is a bit of an Andrew festival – an AC festival, at that) gave extremely useful (and hard to find) info about how to filter based on the values of lookup columns in SharePoint lists using REST. He covered regular lookup columns as well as Managed Metadata columns.

What it comes down to is using the projection to the source of the lookup or Managed Metadata column and then filtering base on the original, underlying value. We do this in REST for SharePoint using the $expand operator.

My comment was about needing to figure out the projection for an Author (Created By) column. The columns Created By and Modified By are populated by SharePoint automagically and the data used for that population is stored in the User Information List. This is a semi-hidden list which exists in the root of each Site Collection. I say semi-hidden because it’s actually the list you’re looking at when you go into the People and Groups view in Site Settings.

If you need to retrieve the name of the Author in a REST call, you can make it work by adding the projection for the Author column.

/_api/web/lists/getbytitle(listname)/items?$select=Title,Author/ID,Author/Title&$expand=Author/ID,Author/Title

Going a little further, you can request any of the data you’re used to seeing on the userdisp.aspx page (_layouts/15/userdisp.aspx?Force=true).

/_api/web/lists/getbytitle(listname)/items?$select=Title,Author/ID,Author/FirstName,Author/LastName,Author/Title,Author/Department,Author/SipAddress&$expand=Author/ID

It doesn’t really make a lot of sense when you look at it (at least not to me), but by adding the $expand clause, you can retrieve the corresponding info the the User Information List. It feel like you’d need to specify where that list is or something, but under the covers that happens for you.

It seems that by providing each of the values you want in both the $select and the $expand operators, it works. I couldn’t find any documentation on this anywhere. AC2 (the Connell one) has suggested to me several times that it makes sense to simply look at the OData standard. As he said in a reply to me on the same post:

For me, there’s simply nothing better than the raw SDKs & specs on the www.odata.org site. Aside from that, I’ll query www.StackOverflow.com. Then, as a SharePoint guy, you then need to look at WCF Data Services and see what it does not support in the OData v3 spec & the same is true for SharePoint 2013.

I think this stuff is about as clear as mud, but the main reason is that I haven’t spent a lot of time with it yet. REST calls are just a different flavor of how we ask for data in other languages. It’s simply a matter of getting the accent right.