Large Lists, BCS, Excel REST, JSOM, CSVs and Office 365 OH MY!

An interesting challenge came across my desk last week:

"How can we import a CSV into Office 365 and use that data to tag other items within the site.  The CSV currently has ~7600 rows and is expected to grow.  We'll also need to re-import the CSV on an ad-hoc basis when the data changes."

The last part of that was the real issue.  There was simply no easy way of doing that.  After trying a few things, falling flat on my face, I'm prepared to share my exploration into the different options.

• BCS
• Custom List - JSOM
• Excel REST API
• CSV

Each have their own merits and pitfalls.  This post will examine each and try to shed some light on the pitfalls I've found using each of these.

BCS

Wiring up an ECT on Office 365 can be a little finicky. I initially had some issues due to 2 BDC Models that were created for the same ECT.  After calling in the eagle-eyed BCS guru, +Fabian Williams, I got squared away.

Immediately after that, I could tell that I was not going to be able to use BCS for what I needed.  In Office 365, there is a hard limit of 2000 items that can be retrieved.  Ironically, the error message that is displayed is not a supported cmdlet for Office 365.

Office 365 BCS Throttle Error

Adding insult to injury, I decided to run a simple test using JSOM.  I wanted to clarify if JSOM would provide me PagingInfo with a BCS List.  Using the code below, you'd expect line 68 to produce a value instead of nothing at all.


Since BCS will not work and due to the limitation of the API, I had to look for another solution.

Custom List

Using a custom list initially worked great.  I'm able to use JSOM, query the list for 5000 items per trip to the server, AND get PagingInfo.  Using the code below works great for this scenario.

Importing the Excel into Office 365 is relatively straightforward and will work for most needs.  The file I used had about ~7600 rows of data.  After importing the file, I noticed the Server Resource Quota was tapped.

Office 365 Server Utilization

So using this approach has 2 problems.  I will not be able to do a mass import again of my data (the list already exists) and the Server Resource Quota points are exhausted.

Excel REST

This seemed like a cool way of getting around the limitations above, so I dove in to find out if this will work for my needs.  After all, I'm allowed to have a *lot* of rows in Excel and I'll be able to easily update the file, since it's in a document library.  Using the code below, I ran into a showstopper though.


There is a hard limit in the API set at 500 rows.  That would be painfully slow to get all of the items or even worse; a user may try to use the form control while this is still querying for data.

Excel REST API - 500 row limit

So that leaves us with our raw data that was exported from SQL and given to us to use.

CSV

Updating the CSV will be easy, since it will be stored in a document library.  Now all we need to be able to do is make sense of it.  Using the code below, I'm able to parse the CSV and create an array of objects that I need to pass off to another library.  Also note the use of localStorage.  This is a nice way to cache the data and prevent the retrieval/processing of the data client-side on every page load.  If the CSV is updated, simply clear the browser cache and you'll get the latest and greatest.

Conclusion

All approaches have their merits and pitfalls... BCS and PagingInfo, I'm looking at you!  If the ad-hoc mass-import wasn't needed, then using list driven data would have been my choice.  If I used that approach, I would have still used localStorage though.  It makes sense to cache the processed data since it'll not change very much.  Since my solution works client-side, I'll have to take into consideration the amount of time this takes to render.  I'm getting good performance out of the CSV approach, so I'm going to stick with it for the time being.

Fire Workflows with Initiation Parameters using #SPServices

Firing Workflows using Javascript, I've never had to pass in Initiation Parameters.  This post takes a look at how to do just that and provides some code that will allow easy use of Workflows in Javascript.

Setup Workflow

The workflow has to be set for Manual starting, otherwise this will not work.  Also, to pass in parameters to the workflow, you'll need to have Initiation Variable(s) within the workflow. I've only fiddled with Number and Single Line of Text fields, so if you use other column types, feel free to share your experience.

Workflow Initiation Parameters

This workflow is simply logging the variables out to the Workflow History.  Easy peazy...

Workflow Parameters

Looking over the documentation for SPServices and StartWorkflow, I found some examples that were a great starting point.  After fiddling a bit with 1 field, I decided to test this a little more.  I created a column with spaces in the Name field *gasp*.  I only did this to see how to handle this programmatically, so a word to the wise: Friends don't let friends create columns with spaces...

Reading over the examples, if you have multiple parameters, it says you have to change from passing the column name to this weird pattern: 

<Data><Parameter1>" + parameter1 + "</Parameter1><Parameter2>" + parameter2 + "</Parameter2></Data>

I've found this to not work at all for me at all [sad_panda]... Back to the drawing board, I guess. Then an idea came to me.  Since I'm targeting a column with a space in it, I tried what normally happens to spaces in Static Names: _x0020_.  So, I tried this next:

<Data><TextField>Will it blend?</TextField><With_x0020_Spaces>42</With_x0020_Spaces></Data>

However, this didn't work either!  Very curious to find a resolution, I set out to find out why this didn't work...  Using SPD (SharePoint Designer), you are able to view the files generated by the workflow. Opening up the XML file as text, you can clearly see that SPD removed the space in the Static Name.

Workflow wfconfig.xml

Within this file, all of the Initiation Parameters are visible and it's now easy to tell what's exactly going on.

Workflow Parameter Names

For all of this to work while using multiple parameters, you have to use the exact Static Name as defined in the XML.  The workflow parameters below work just fine for me now.

<Data><TextField>Will it blend?</TextField><WithSpaces>42</WithSpaces></Data>

Code to Fire the Workflow

This function will handle the pain of getting a workflow to fire.  All you need to know is the correct URL, the workflow name, and the workflow parameters ( if any ).

*** Update *** I took my original idea and made it more or less a plug-in for SPServices. Add this function to the SPServices source and it'll work without any issues. Original function:

Simple[r] jQuery Content Filter

A post titled: Simple jQuery Content Filter for Office 365 Public Website by Doug Hemminger came by my twitter stream that caught my eye.  A real quick win and all around goodness for everyone.  Reading through, I noticed some things I could tidy up.  So I asked him if he'd be okay if I re-factor the code that he blogged about and he said absolutely!

UX

It's almost expected these days to be able to click the text next to a checkbox and have it just work.  Doug is using <span> for the text, so changing this to use <label> and wrap the <input> is a real quick win.

As I was writing this up, I also had another idea...  Why not have all of the filters show no matter how far down you scroll?  This is also a quick and easy win, so it's in there too.

DRY

DO NOT REPEAT YOURSELF! 

I'm totally guilty of this, but I try to do my best every time.  I checked the source of the page where the code was added and found:

        $(document).ready(function () {
            function SPSToggleView() {
                if (!$("#SPSBusinessCheckbox").prop("checked")) {
                    $(".SPSBusiness").hide();
                } else {
                    $(".SPSBusiness").show();
                }

                if (!$("#SPSInformationWorkerCheckbox").prop("checked")) {
                    $(".SPSInformationWorker").hide();
                } else {
                    $(".SPSInformationWorker").show();
                }

                if (!$("#SPSCertificationCheckbox").prop("checked")) {
                    $(".SPSCertification").hide();
                } else {
                    $(".SPSCertification").show();
                }

                if (!$("#SPSBusinessIntelligenceCheckbox").prop("checked")) {
                    $(".SPSBusinessIntelligence").hide();
                } else {
                    $(".SPSBusinessIntelligence").show();
                }

                if (!$("#SPSDeveloperCheckbox").prop("checked")) {
                    $(".SPSDeveloper").hide();
                } else {
                    $(".SPSDeveloper").show();
                }
                if (!$("#SPSITProCheckbox").prop("checked")) {
                    $(".SPSITPro").hide();
                } else {
                    $(".SPSITPro").show();
                }
                if (!$("#SPSCloudCheckbox").prop("checked")) {
                    $(".SPSCloud").hide();
                } else {
                    $(".SPSCloud").show();
                }
                if (!$("#SPSSocialCheckbox").prop("checked")) {
                    $(".SPSSocial").hide();
                } else {
                    $(".SPSSocial").show();
                }
                if (!$("#SPSGeneralCheckbox").prop("checked")) {
                    $(".SPSGeneral").hide();
                } else {
                    $(".SPSGeneral").show();
                }
            };
            $("#SPSInformationWorkerCheckbox").attr("checked", true);
            $("#SPSBusinessCheckbox").attr("checked", true);
            $("#SPSCertificationCheckbox").attr("checked", true);
            $("#SPSBusinessIntelligenceCheckbox").attr("checked", true);
            $("#SPSDeveloperCheckbox").attr("checked", true);
            $("#SPSITProCheckbox").attr("checked", true);
            $("#SPSCloudCheckbox").attr("checked", true);
            $("#SPSSocialCheckbox").attr("checked", true);
            $("#SPSGeneralCheckbox").attr("checked", true);

            $("#SPSInformationWorkerCheckbox").click(SPSToggleView);
            $("#SPSBusinessCheckbox").click(SPSToggleView);
            $("#SPSCertificationCheckbox").click(SPSToggleView);
            $("#SPSBusinessIntelligenceCheckbox").click(SPSToggleView);
            $("#SPSDeveloperCheckbox").click(SPSToggleView);
            $("#SPSITProCheckbox").click(SPSToggleView);
            $("#SPSCloudCheckbox").click(SPSToggleView);
            $("#SPSSocialCheckbox").click(SPSToggleView);
            $("#SPSGeneralCheckbox").click(SPSToggleView);
        });

Even though this is a simple solution, I knew it could be made simpler. :) I got it down to this:

    
$(document).ready(function () {
        var $wrapper = $("#wrapper");

        $wrapper.on("change", "input[data-filter]", function (event) {
            var $this = $(this),
                filter = $this.data("filter");

            $wrapper.find("div[data-filter='" + filter + "']").slideToggle();
        });
    });

The magic is done by hiding the value(s) we are going to hide/show directly onto the <input> as a `data-filter` attribute.  When the change event is fired, that value is surfaced.  Then a simple query of the DOM to find the correct <div>'s that this filter relates to.  The jQuery#slideToggle method knows whether or not the elements are hidden or displayed, so there's no need to check the state of the <input>.

Results

jQueryUI Autocomplete with SharePoint Drop Downs

SharePoint Drop Downs Suck!

I can't go anywhere without having this UX nightmare on my hands.  How many times have you started typing in these drop downs, then tab away expecting the same UX you have, LIKE EVERYWHERE ELSE ON THE INTERNET?  The problem is, these drop downs are inconsistent in their behavior and even render completely different in Internet Explorer. If there are 20+ items within the list, you can guarantee calls to your helpdesk.  I've spent enough time working on this problem; enough to offer up some code so you can handle this yourself too.  Hopefully, you find it useful.

Search by Type UX

This has been blogged about a bunch... Consider this horse completely beaten and dead at this point, but nothing I've seen out there handles SharePoint drop downs the way I do with this code.  Without further adieu, the codez:

<link type="text/css" rel="stylesheet" href="//code.jquery.com/ui/1.10.2/themes/smoothness/jquery-ui.min.css">

<script src="//code.jquery.com/jquery-1.8.3.min.js"></script>

<script src="//code.jquery.com/ui/1.10.2/jquery-ui.min.js"></script>

<script>

function getFormDDL( columnName ) {

var $ddl = $("select[title='" + columnName + "']"),

choices,

choiceArray,

index = 1

; //local vars

if ( $ddl.length > 0 ) {

$ddl.SPOptions = $ddl.find("option").map(function() {

var $el = $(this);

return {

label: $el.text(),

id: $el.val()

}; 

}).get();

$ddl.SPComplexDdl = false;

} else {

$ddl = $("input[title='" + columnName + "']");

choices = $ddl.attr("choices");

choiceArray = choices.split("|");

$ddl.SPOptions = [];

for ( ; index < choiceArray.length; index = index + 2 ) {

$ddl.SPOptions.push({

label: choiceArray[ index - 1 ],

id: choiceArray[ index ]

});

}

$ddl.SPComplexDdl = true;

}

return $ddl;

}

function setFormDdl( $ddl, lookupVal ) {

var choices,

choiceArray,

hiddenInput,

index

;




if ( $ddl.SPComplexDdl ) {

choices = $ddl.attr("choices");

hiddenInput = $ddl.attr("optHid");

$("input[id='" + hiddenInput + "']").val( lookupVal );




choiceArray = choices.split("|");

for ( index = 1; index < choiceArray.length; index = index + 2 ) {

if ( choiceArray[ index ] == lookupVal ) {

$ddl.val( choiceArray[ index - 1 ] );

}

}

} else {

$ddl.val( lookupVal );

}

}

/*****

*

* Main processing

*

******/




$( document ).ready(function() {
                // To use this, just add as many Display Names you want that are Drop Downs on your form!

var columnNames = [ "Full Name", "City" ]

;

// Don't touch... It works as is. :)

$.each( columnNames, function( index, columnName ) {

var $ddl = getFormDDL( columnName ),

$wrapper = $("<div class='sp-planet-autocomplete'>"),

idName = columnName.replace(/ /g, "-"),

$input = $("<input class='ms-long' id='" + idName + "' />")

; //local vars

// debugger;

// When ddl is complex, we need to hide the whole shebang.  Might as well do it for a simple ddl too.

$ddl.closest("span[dir='none']").hide();

$wrapper.append( $input );

$ddl.closest("td").prepend( $wrapper );

// Find autocomplete options here.

// http://jqueryui.com/autocomplete/

$input.autocomplete({

source: $ddl.SPOptions,

        minLength: 0,

          select: function( event, ui ) {

setFormDdl( $ddl, ui.item.id );

}

});

// If selection is made and then text is deleted, this will prevent that. This ain't my first rodeo...

$input.on("blur", function( el ) {

// debugger;

if( el.currentTarget.value.trim() === "" ) {

setFormDdl( $ddl, 0 );

}

});

});

});




</script>

SharePoint Drop Downs = Search by Type Now!

Now this is a cool alternative to the standard ho-hum.  It's safe to say that users know exactly what they are looking for when there are 20+ options to choose from.  With that in mind, providing a search by type UX feels natural.  Also as an added bonus, jQueryUI gives you: up and down keyboard navigation and enter/tab selection.  Having this enabled just FEELS good!

Search by Type Usage

The columNames variable is the only variable you have to change.  Everything else is handled by jQuery/jQueryUI magic under the hood.  The only caveat to my code is that it currently doesn't handle multiple selections.  My current needs haven't forced me to pursue this, but if you find it necessary, I may revisit it.  I'd be more inclined to do so, *if* it was profitable. :-)

Feel free to let me know how this code works out for you.  It *should* just work.

roboCAML v0.4 has been released!

You may have seen me tweet about roboCAML the last few days.  If you were scratching your head about what it actually is, don’t feel bad.  No one knew…  It’s a jQuery module, *not* a plugin, I’ve built specifically to handle the tedious task of manually building CAML or worse hard coding CAML within your scripts.  An added benefit, is the ability to create dynamic CAML queries on the fly.

Currently roboCAML has a depends on jQuery, but very well may become a  pure JavaScript module without any external dependencies.  It's hard to beat the $.ajax function within jQuery, but I'm willing to change based on feedback.  It’s all about the community, you know?

What does it do?

roboCAML takes away the pain of scripts that look like this:

     // #CAMLToggle doesn't exist, but this is here in case we want to give the user the ability to AND
     // or OR the PartNum
  var camlToggle = $("#CAMLToggle").val() ? $("#CAMLToggle").val() : "Or", 
   ddlSelected = false, 
   PartCat = [], 
   PartNum = [], 
   thisFieldRef = "", 
   camlQuery = "";
  
  $( "select.PartCat" ).each(function() {
   // Each select on the page has the PartCat class
   // the title attribute is also the name of the field for the CAML
   ctlTitle = $(this).attr("title");
   PartCat[ctlTitle] = ctlTitle;
   
   if ( ctlTitle ) != 0 ) {
    ddlSelected = true;
   }
  });
  

//First example of CAML engine
  i = 0;
  for (index in PartCat) {
   //console.log("Select title: " + i);
   //console.log("Select Val: " + PartCat[i]);
   if (PartCat[index] > 0) {
    thisFieldRef = "<Eq><FieldRef Name='" + index + "' LookupId='True' /><Value Type='Number'>" + PartCat[index] + "</Value></Eq>";
    if (i <= 1) {
     camlQuery += thisFieldRef;
    }
    if (i == 1) {
     camlQuery = "<And>" + camlQuery + "</And>";
    }
    if (i > 1) {
     camlQuery = "<And>" + camlQuery + thisFieldRef + "</And>";
    }
    i++;
   }
  }

//Show example of other CAML Engine
  
  $("input.PartNum:checked").each(function(index) {
   // If this isn't the first PartNum, we'll "wrap" the array with the camlToggle
   if(index > 0) {
    PartNum.unshift("<" + camlToggle + ">");
   }
   PartNum.push("<Eq><FieldRef Name='PartNum' LookupId='True' /><Value Type='Number'>" + $(this).attr("alt") + "</Value></Eq>");
   // If this isn't the first PartNum, we'll "wrap" the array with the camlToggle
   if(index > 0) {
    PartNum.push("</" + camlToggle + ">");
   }
  });
  
  // .join() defaults to commas, .join("") does the same thing. We'll join with a space, then replace
  // the spaces that fall between tags
  camlQuery += PartNum.join(" ").replace(/> </gi,"><");
  
  if ( $("input.PartNum:checked").length > 0 && ddlSelected ) {
   // If we have DDLs and PartNums, we'll <And> the two groups together, otherwise we won't
   camlQuery = "<And>" + camlQuery + "</And>";
  }

  camlQuery = "<Query><Where>" + camlQuery + "</Where><OrderBy><FieldRef Name='Title' Ascending='True' /></OrderBy></Query>";
  GetListItems(camlQuery);
 }

Within this script are two different ways to dynamically build CAML.  Each have their merits, but why do I have to think about setting up my CAML correctly and debugging it if I am having issues?  After being tasked to build a few of the complex scripts, sometimes several in a week, I had enough...  Time to roll up the sleeves and code a solution.  This is why I’ve built roboCAML, so now lets see what it does.

roboCAML In Action

roboCAML will assist in generating a string of useful CAML for use when making your Web Service or Client Object Model calls.  There are currently 5 methods available in roboCAML.

1. roboCAML.BatchCMD
2. roboCAML.OrderBy
3. roboCAML.Query
4. roboCAML.QueryOptions
5. roboCAML.ViewFields

roboCAML.BatchCMD

So, now let’s look at roboCAML.BatchCMD method.  This method is a little tricky because there are three distinct actions you can take when using a batch.  Each requiring a different set of parameters.  In example 2.1, we’ll look at Deleting:

Example 2.1:

roboCAML.BatchCMD({
   batchCMD: 'Delete',
   IDs: [1,2,3]
});

This call accepts an array of ID’s and the “Delete” command.  The output will be:

<Batch OnError='Continue'><Method ID='3' Cmd='Delete'><Field Name='ID'>3</Field></Method><Method ID='2' Cmd='Delete'><Field Name='ID'>2</Field></Method><Method ID='1' Cmd='Delete'><Field Name='ID'>1</Field></Method></Batch> 

Now onto the New operation.  This example accepts the command for the batch.  What is different here from the Delete operation is, we can now pass in a valuePairs parameter.  This parameter accepts an array of arrays.  You’ll notice each array follows a certain pattern.  First the Static Name is provided and then the value.

Example: 2.2:

roboCAML.BatchCMD({ 
 batchCMD: "New",  
 valuePairs: [["PersonnelLookup", 1]]  //Static Column Name, Value
});

A more complex array would look like this:

roboCAML.BatchCMD({ 
 batchCMD: "New",  
 valuePairs: [["PersonnelLookup", 1, "ModuleNotes", "ModuleNotes", "Description", "Googly Glop"], ["ListUID", 3]]  //Static Column Name, Value
});

Which in turn produces:

<Batch OnError='Continue'><Method ID='1' Cmd='New'><Field Name='PersonnelLookup'>1</Field><Field Name='ModuleNotes'>ModuleNotes</Field><Field Name='Description'>Googly Glop</Field></Method><Method ID='2' Cmd='New'><Field Name='ListUID'>3</Field></Method></Batch> 

The last operation within roboCAML.BatchCMD is Update:

Example 2.3

roboCAML.BatchCMD({
 updates: [
  {
   //Notice batchCMD isn't present...
   //Static Column Name, Value
   valuePairs: ["Title", "Numero Tres", "PercentComplete", 1, "Boolean", 0, "ID", 3]
  },
  {
   //Defaults to Update anyway. No need to pass it.
   batchCMD: "Update",
   valuePairs: ["ID", 4, "Title", "Item4", "Boolean", 0]
  },
  {
   batchCMD: "New",
   valuePairs: ["Title", "Something New", "PercentComplete", 1]
  },
  {
   batchCMD: "Delete",
   ID: 6
  }
 ]
});

The output of the call above will generate:

<Batch OnError='Continue'>
 <Method ID='1' Cmd='Update'>
  <Field Name='Title'>Numero Tres</Field>
  <Field Name='PercentComplete'>1</Field>
  <Field Name='Boolean'>0</Field>
  <Field Name='ID'>3</Field>
 </Method>
 <Method ID='2' Cmd='Update'>
  <Field Name='ID'>4</Field>
  <Field Name='Title'>Item4</Field>
  <Field Name='Boolean'>0</Field>
 </Method>
 <Method ID='3' Cmd='New'>
  <Field Name='Title'>Something New</Field>
  <Field Name='PercentComplete'>1</Field>
 </Method>
 <Method ID='4' Cmd='Delete'>
  <Field Name='ID'>6</Field>
 </Method>
</Batch>

What’s very interesting with using the updates property of roboCAML.BatchCMD is the batch that is generated can be chocked full of all your different operations. Delete, New and Update all within one Web Service call. That’s #bada55.

roboCAML.OrderBy

And if you weren’t impressed by any of the above, maybe this will persuade you… It’s another snazzy way to build CAML on the fly. Here’s how to use roboCAML.OrderBy:

roboCAML.OrderBy({
 MyColumn: false,
 ID: true
});

Note: The value of each staticName can be a boolean or a string... The output of the call above will look like this:

<OrderBy><FieldRef Name='MyColumn' Ascending='False' /><FieldRef Name='ID' Ascending='True' /></OrderBy>

roboCAML.Query

Probably the most interesting method roboCAML has to offer.  There are a plethora of options within this method.  The best way to learn how to use this would be to read the docs or use the live demo.  Let’s look over a sample query you can use with roboCAML.

roboCAML.Query({
 listName: 'Calendar', 
 closeCaml: "Clientom",
 ViewFields: ["ID", "Created", "Title"],
 OrderBy: {
  ID: true
 },
 QueryOptions: {
  IncludeMandatoryColumns: false
 },
 config: [
  {
   filter: "&&",
   op: "*",
   staticName: "Title",
   value: "Daily"
  },
  {
   filter: "&&",
   op: "^",
   staticName: "Title",
   value: "Deleted"
  },
  {
   op: "!=",
   staticName: "ID",
   value: 3
  }
 ]
});

Since the CAML is not hardcoded, you can now easily generate whatever options needed to retrieve information from SharePoint.  Just like above in the “CAML Engines”, stuff an array full of info and do something with it...  I’m thinking of building a demo soon that will serve as a real world example of why this is useful for front-end development. In case you were wondering, this is the output from heavy lifting done from roboCAML:

<View>
 <ViewFields>
  <FieldRef Name='Title' />
  <FieldRef Name='Created' />
  <FieldRef Name='ID' />
 </ViewFields>
 <Query>
  <Where>
   <And>
    <Contains>
     <FieldRef Name='Title' />
     <Value Type='Text'>Daily</Value>
    </Contains>
    <And>
     <BeginsWith>
      <FieldRef Name='Title' />
      <Value Type='Text'>Deleted</Value>
     </BeginsWith>
     <Neq>
      <FieldRef Name='ID' />
      <Value Type='Counter'>3</Value>
     </Neq>
    </And>
   </And>
  </Where>
  <OrderBy>
   <FieldRef Name='ID' Ascending='True' />
  </OrderBy>
 </Query>
 <IncludeMandatoryColumns>False</IncludeMandatoryColumns>
</View>

roboCAML.QueryOptions

This method will assist you in building queries and other various CAML fragments that you may need.  I haven’t found a comprehensive list that details all of the options available within this node.  The closest I’ve come to full documentation was on the Lists.GetListItems Method page within MSDN.  As I find/test/evaluate each new option I find, I’ll piecemeal them into the project. For now, the documentation can be found on the roboCAML project page.

roboCAML.ViewFields

As you could guess (if you are familiar with CAML), this does exactly what you would expect. Let’s take a look at roboCAML.ViewFields:

roboCAML.ViewFields(["Title", "Description", "ProjectName", "RelatedID"]);

This method accept an array of Static Names. The output will be:

<ViewFields><FieldRef Name='RelatedID' /><FieldRef Name='ProjectName' /><FieldRef Name='Description' /><FieldRef Name='Title' /></ViewFields> 

It’s just that simple… Really!

There you have it, roboCAML in a nutshell.  I’m missing some key parts that you *should* be able to do when creating CAML queries for SharePoint. I plan on adding them very soon!  Support for <Membership />, <Joins> (if it’s possible, haven’t tried yet...), and <ProjectedFields> are on top of my list as well as nested CAML fragments (Thanks Jim Bob!).  If you can think of anything you’d like to see added, feel free to ask. 

Understanding IncludeMandatoryColumns and SharePoint’s Web Services…

For a while now this has baffled me and several other people I know whom use SharePoint’s web services religiously.  The problem starts with incorrect documentation and since it has never been updated, new people to SharePoint continue to have issues with <IncludeMandatoryColumns>.  This post should give some clarity into what to expect when using this…

<IncludeMandatoryColumns>False</IncludeMandatoryColumns>

When setting this to option to: False; you’d expect a whole bunch of columns to not show up in the results of the XML.  That’s just, well: FALSE.  The only thing that changes in your data set is the Title column.  Nothing more, nothing less…  Here’s a sample data set that was retrieved using SPServices (which btw, you should use for all of your web service interactivities).

<!-- Mandatory Columns = False -->
<rs:data ItemCount="1">
   <z:row ows_MyDollars='567890.000000000' ows_Boolean='1' ows_MultiSelectLookup='' ows_ID='404' ows__ModerationStatus='0' ows__Level='1' ows_UniqueId='404;#{2541C25E-1D9A-4480-8F97-570B64077E37}' ows_owshiddenversion='3' ows_FSObjType='404;#0' ows_Created='2012-02-06 13:55:17' ows_PermMask='0x7fffffffffffffff' ows_Modified='2012-02-06 16:17:24' ows_FileRef='404;#mySite/Lists/GrandChild/TestFolder/TestSubFolder/MovedItem' />
</rs:data>

Did you notice the owsHiddenVersion in the XML?  According to the documentation, it should not be there.  For posterity, here’s a screenshot of the parameters passed to SPServices. If you are keen, you’ll notice my <ViewFields> do not contain the Title column:

<IncludeMandatoryColumns>True</IncludeMandatoryColumns>

So being coy and after a few trial and errors, I decided that <IncludeMandatoryColumns> had to be related to the required fields within the list.  Since Title was the only required field, it seemed like a logical step.  Within this list, I have a column called Hyperlink.  I decided to make that required.  Notice in my <ViewFields>, Hyperlink is not present nor is Title for that matter.

<!-- Mandatory Columns w/ 2 columns required -->
<rs:data ItemCount="1">
   <z:row ows_MyDollars='567890.000000000' ows_Boolean='1' ows_MultiSelectLookup='' ows_ID='404' ows__ModerationStatus='0' ows__Level='1' ows_Title='InSubFolder' ows_UniqueId='404;#{2541C25E-1D9A-4480-8F97-570B64077E37}' ows_owshiddenversion='4' ows_FSObjType='404;#0' ows_Created='2012-02-06 13:55:17' ows_PermMask='0x7fffffffffffffff' ows_Modified='2012-02-07 20:12:48' ows_FileRef='404;#mySite/Lists/GrandChild/TestFolder/TestSubFolder/MovedItem' />
</rs:data>

Is that ows_Title that I see?  Why yes, YES IT IS! As a matter of fact, it’s the only thing that’s different from the two sets of XML.

What have we learned?

<IncludeMandatoryColumns>True</IncludeMandatoryColumns> += Title. If Title is already in your <ViewFields>, then you’ll get nothing new. Yes, I know ID is in my <ViewFields> in the examples above.  Nothing changes except Title.

The documentation on MSDN is invaluable, however, it must be used with caution.  I’ve been running into a few whammies, gotchas, kablooey your code is busticated situations while building roboCAML.  The SP Namespace is another beast altogether, but that’s not what this post is about… yet.