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.

Finding the Distribution of Column Types in a SharePoint List with SPServices

I just went through a little exercise that was simple, but fun, and I thought I’d share. As part of building my new SPXmlToJson function for SPServices, I wanted to spin through some of my lists to see what the various types of columns were and count the occurrences. Sure, I could have probably just looked in the SDK somewhere for the exhaustive list, but I though it would be a useful exercise to code something. The idea was to call GetList on a particular list, go through all of the columns in the results, and list out the number of times each column type shows up.

Since I am building a function to emit JSON, knowing what type of objects I need to create is important. Each column has a type, of course, which you select when you create the column. In the UI, the column types are things like “Single line of text”, “Choice”, or “Lookup”, as you’re probably used to seeing:

Create a List Column - Column TypesHowever, those aren’t the values that SharePoint stores in the list settings. If you work with the SharePoint Object Model with managed code, you’re used to seeing SPFieldType (yup, that link is where I could have gone to see the full list without resorting to my code).

SPFieldType in the SharePoint SDK

In the Web Services (at least GetList), we see an attribute simply named “Type” for each “Field”. Yes, fields are columns. Isn’t it grand how inconsistent some of the naming in SharePoint is?

Here’s the script I ended up with.  Since I’m focused on JSON, I wanted to play around with the various options for storing the data. I ended up with an array of typeName/typeCount values, which I could then sort in various ways. My first cut was to sort the types alphabetically and show them as simple list bullets.

$(divId).html(waitMessage).SPServices({
  operation: "GetList",
  listName: "Sales Opportunities",
  completefunc: function (xData, Status) {

    var types = [];
    $(xData.responseXML).find("Fields > Field").each(function() {

      // If we already have this type in the array, increment the count...
      var match = false;
        for(i = 0; i < types.length; i++) {
          if(types[i].typeName === $(this).attr("Type")) {
            types[i].typeCount++;
            // No need to loop further
            match = true;
          }
        }

      // ...otherwise, add it to the array with a count of 1
      if(!match) {
        var thisType = {typeName: $(this).attr("Type"), typeCount: 1};
        types.push(thisType);
      }
    });

    types.sort(function(a, b){
      var typeA = a.typeName.toLowerCase(), typeB = b.typeName.toLowerCase();
      // We want to sort the type strings, ascending
      if (typeA < typeB) return -1;
      if (typeA > typeB) return 1;
      // If they are equal, return 0 (no sorting)
      return 0;
    });

    var out = "<ul>";
    for(i=0; i < types.length; i++) {
      out += "<li>" + types[i].typeName + " = " + types[i].typeCount + "</li>";
    }
    out += "</ul>";

    $(divId).html("").append("<b>This is the output from the GetList operation:</b>" + out);
  }
});

What I ended up with is something like this:

Column Types Sorted AlphabeticallyTo see the column type sorted by number of occurrences, I simply changed the sort function to this:

types.sort(function(a, b){
  // To sort descending, subtract a from b; ascending would be the reverse
  return b.typeCount - a.typeCount;
});

This gave me output like this:

Column Types Sorted by Number of OccurencesSince I’ve found the SDK page for SPFieldType, this is throwaway code, but it’s not throwaway learning. It’s a little snippet of script that taught me a few things about formatting data for working storage on the client in script and also showed me what the internal types were for the columns which I use most often in my testing for SPServices. Note that while I’m using SPServices, and therefore jQuery, most of the heavy lifting in my script is plain old JavaScript.

Tip for Using SPServices with GetListItems

As is often the case, one of the threads on the SPServices Discussions seemed worth bringing over as a blog post. Here’s the initial question, with the script cleaned up a little for clarity:

Any help on this would be greatly appreciated.  When I first completed this project last month it was pulling all of the data from the list.  Now when this script runs it’s only pulling back 4 out of 16 records.  Can you help me adjust the code to force it to pull all list items?

<script type="text/javascript" language="javascript" src="../../Style Library/jquery_library/jquery-1.5.2.min.js"></script>
<script type="text/javascript" language="javascript" src= "../../Style Library/jquery_library/jquery.SPServices-0.6.1.min.js"></script>
<script language="javascript" type="text/javascript">
  $(function () {//-----------SPSERVICES GET ALL LIST ITEMS FROM HR KALENDAR
    $().SPServices({
      operation: "GetListItems",
      async: false,
      listName: "HR Kalendar",
      completefunc: function (xData, Status) {
        $(xData.responseXML).find("[nodeName='z:row']").each(function() { //--------FIND EACH RECORD FROM HR KALENDAR
          ...
        });
      }
    });
  });

Matt Bramer (@iOnline247) was nice enough to reply with this suggestion:

What happens if you put a CAML query in there that says ID != 0

<Query><Where><Neq><FieldRef Name='ID' /><Value Type='Counter'>0</Value></Neq></Where></Query>

I just wrote this query out by hand, so it may need some tweaking.  Throw that into your call and see what happens.

Generally speaking Matt’s suggestion may not be necessary. However, if you don’t specify any CAML options at all, GetListItems uses the default view for the list. That view may or may not return what you think you’ve asked for. By specifying *something* for the options, you’re asking SharePoint to “step out of” the default view.

In SPServices itself, I generally do this by specifying:

// Override the default view rowlimit and get all appropriate rows
CAMLRowLimit: 0,

The nice thing about setting the CAMLRowLimit is that you only need to pass zero as a parameter to make SharePoint stop thinking in terms of the default view. It’s simple, but effective, and doesn’t require any knowledge of CAML at all.

Updating a Lookup Column Using SharePoint’s Lists Web Service

Updating a Lookup Column Using SharePoint’s Lists Web ServiceThe SPServices discussions are a fount of interesting information. I find myself answering all sorts of questions which, while connected to SPServices, are often just as much about how SharePoint works in general. I had two questions recently about how to update Lookup columns with UpdateListItems, and I thought they would be of general interest.

Lookup column values are stored in the format “n;#Text Value”, where n is the ID of the value in the reference list and “Text Value” is, well, the text value of the Lookup column. There are probably some good historical reasons for this having to do with Office compatibility, if you think about it. Just storing the text value means that you lose the connection to the original item in the reference list, and just storing the ID for the item would mean that you’d need expensive calls to the database every time you wanted to display the value. As for the ‘;#’ delimiter, you see that sprinkled throughout how SharePoint stores data values. It’s probably related to some very old standard of some sort, but in any case it’s highly likely to be a unique string which no one will use in their text values, so it’s a reasonable delimiter.

Update a Lookup Column Value

The first question was how to update a Lookup column value. The code for this in SPServices is pretty straightforward; the tricky bit is knowing how to structure your value.

In this example, the State column in Sales Opportunities is a Lookup column to the Title column in a list called States.

  $().SPServices({
    operation: "UpdateListItems",
    async: false,
    debug:true,
    listName: "Sales Opportunities",
    ID: 5,
    valuepairs: [["State", "2;#Rhode Island"]],
    completefunc: function (xData, Status) {
      alert(xData.responseText);
    }
  });

It also works fine to just specify the index:

    valuepairs: [["State", "2"]],

The trick is knowing what the right index is. You’ll need to figure that out by calling GetListItems on the reference list or by using some other method.

“Emptying” a Lookup Column Value

The second question was how to “empty”, or remove, a previously set value in a Lookup column.

  $().SPServices({
    operation: "UpdateListItems",
    async: false,
    debug:true,
    listName: "Sales Opportunities",
    ID: 5,
    valuepairs: [["State", ""]],
    completefunc: function (xData, Status) {
      alert(xData.responseText);
    }
  });

It’s important to keep in mind that when you are interacting with SharePoint’s XML (SOAP) Web Services, *all* of the traffic back and forth is text. There’s no such thing as a null or some of the other datatypes you might be used to in other ways of interating with the API. An empty string [“”] in text essentially *is* a null; it represents “no value”.

I wish that I could say that this little tip was foolproof in interacting with all of SharePoint’s Web Services, but I can’t. There is a *lot* of internal inconsistency across the Web Services, and in some cases, even among operations in the same Web Service. If you want to set other values to “empty” or “null”, you may need to experiment to see what works. I didn’t write ’em; I just wrapped ’em! Note that the line:

      alert(xData.responseText);

can be an absolute lifesaver. It will alert what has come back from the server as a response to the Web Service call. Even if the AJAX request is successful (Status=”success”), there may be useful (or totally indecipherable) error information in the response.

Getting “ows_MetaInfo” Nicely with SPServices

I know that I’m going to look for this tip again, so this post is as much as a “note to self” as anything else, though I expect that others will use it, too.

One of the streams I watch with HootSuite is a plain old search for “SPServices”. It’s useful for me to see what people are saying about SPServices (this one and not that one) and I occasionally retweet the good stuff.

I spotted a great tip from Steve Ottenad (@sottenad) yesterday about displaying the MetaInfo from list items nicely. It’s so simple, yet as far as I know, undocumented.

http://twitter.com/#!/sottenad/status/86135314728501248

Simply add Properties="True" to your CAMLViewFields, like so:

CAMLViewFields: "<ViewFields Properties='True' />",

Thanks, Steve!