CSOM CamlQuery Example - Returning list items that fall within a specified date range

Since CamlQuery isn't very intuitive or easy to work with, I thought I'd put together a serious of posts that display some examples that you can use in order to make it work better for you.

In this particular example, I'm connecting to a SharePoint list and performing the following activities:

1. Filtering the list items that were created within a specified date range while, also, ignoring the time values.  NOTE:  If you need to include the time values, simply change the IncludeTimeValue attribute from FALSE to TRUE.
2. Ordering the list so that it returns the most recently created items first and older items last

So that you can focus on the actual CamlQuery structure, I've greatly simplified the code as follows:

var selectedMinDate = '2018-1-1';
var selectedMaxDate = '2018-1-31';

var camlQueryText = "<View><Query><Where><And><Geq><FieldRef Name=\'Created\' /><Value Type=\'DateTime\' IncludeTimeValue=\'FALSE\' >"
+ selectedMinDate + "</Value></Geq><Lt><FieldRef Name=\'Created\' /><Value Type=\'DateTime\' IncludeTimeValue=\'FALSE\' >"
+ selectedMaxDate + "</Value></Lt></And></Where><OrderBy><FieldRef Name=\'Created\' Ascending=\'False\' /></OrderBy></Query></View>";

var ctx = new SP.ClientContext.get_current();
var camlQuery = new SP.CamlQuery();
camlQuery.set_viewXml(camlQueryText);
this.items = ctx.get_web().get_lists().getByTitle('NameOfList').getItems(camlQuery);
ctx.load(items);
ctx.executeQueryAsync(Function.createDelegate(this, this.onQuerySucceeded), Function.createDelegate(this, this.onQueryFailed));

function onQuerySucceeded(sender, args) {
     while (listItemEnumerator.moveNext()) {
          var oListItem = listItemEnumerator.get_current();
          var itemInfo = oListItem.get_item('Title') + " - " + oListItem.get_item('Created');
          alert(itemInfo);
     }
}

function onQueryFailed(sender, args) {
     alert('request failed ' + args.get_message() + '\n'+ args.get_stackTrace());
}

Obtain a list of SharePoint subsites using Javascript CSOM

Here is some code that you can utilize for obtaining a list of SharePoint subsites that the given user has access to:

<div id="selectsubwebs"></div>

<script type="text/javascript">
   var subsites = null;
   var web = null;
   var context = null;
   var subweb = null;
   var renderedHTML = "";

   var scriptbase = "https://rootSiteUrl/_layouts/15/";

   $.getScript(scriptbase + "SP.Runtime.js", function() {
      $.getScript(scriptbase + "SP.js", function() {
         $.getScript(scriptbase + "SP.RequestExecutor.js", getSubWebs);
      });

   });

   function getSubWebs(){
      context = SP.ClientContext.get_current();
      web = context.get_web();
      context.load(web);
      context.executeQueryAsync(onGetWebSuccess, onGetWebFail);
   }

   function onGetWebSuccess(sender, args) {
      //subsites = web.get_webs();  //Complete list of subsites
      subsites = web.getSubwebsForCurrentUser(null);
      context.load(webCollection);
      context.executeQueryAsync(onGetSubwebsSuccess, onGetSubwebsFail);
   }

   function onGetSubwebsSuccess(sender, args) {
      renderedHTML = renderedHTML + "<table id='table_id' class='display'><thead><tr><th>Team Site Title</th></tr></thead><tbody>";
      var webEnumerator = subsites.getEnumerator();
      while (webEnumerator.moveNext()){
         subweb = webEnumerator.get_current();
         renderedHTML = renderedHTML + "<tr><td><a href='" + subweb.get_url() + "' target='_blank'>" + subweb.get_title() + "</a></td></tr>";
      }
      renderedHTML = renderedHTML + "</tbody></table>";

      document.getElementById("selectsubwebs").innerHTML = renderedHTML;

   }
   function onGetSubwebsFail(sender, args){
      alert("Request to retrieve subwebs failed. Error: " + args.get_message())
   }

   function onGetWebFail(sender, args){
      alert("Request to retrieve subwebs failed. Error: " + args.get_message())
   }
</script>

How to obtain a list of documents from a SharePoint library that were created X days ago using CSOM

In case you ever need to generate a list of documents contained in a SharePoint library that were created, say, 30 days ago or earlier, here is one way that you might go about doing that using CSOM:

using (ClientContext context = new ClientContext(@"https://webApplicationName/siteName"))
{
    context.Credentials = System.Net.CredentialCache.DefaultCredentials;
                    
    List documentsLibrary = context.Web.Lists.GetByTitle("Documents");
                    
    CamlQuery query = new CamlQuery();
    query.ViewXml = "<View><Query><Where><Leq><FieldRef Name='Created'/><Value Type='DateTime'><Today OffsetDays='-30' /></Value></Leq></Where></Query></View>";
         
    //CamlQuery query = CamlQuery.CreateAllItemsQuery();
    ListItemCollection expiringDocs = approvedDocumentsLibrary.GetItems(query);
                   
    context.Load(expiringDocs);
    context.ExecuteQuery();

    foreach (ListItem document in expiringDocs)
    {
        Console.WriteLine(string.Format("{0}", document["Title"]));
    }
}

You'll notice that the crux of the filtering operation is dependent on the XML contents contained in my CamlQuery object as follows:

<View><Query><Where><Leq><FieldRef Name='Created'/><Value Type='DateTime'><Today OffsetDays='-30' /></Value></Leq></Where></Query></View>

with the <Today> element being the key to this in order to take the current date and offsetting it by 30 days in the past.

CAML Queries always take a bit of trial and error in order to get them right so I hope this helps save you some time!

Possible solution for resolving a 409 Conflict Error when uploading a file to a SharePoint 2013 site using CSOM

I was just attempting to upload a file to one of my SharePoint 2013 sites using CSOM when I ran into a 409 Conflict error.  A simplified version of the code I was using is as follows:

using Microsoft.SharePoint.Client;
using Microsoft.SharePoint.Client.Publishing;
using Microsoft.SharePoint.Client.Publishing.Navigation;
using Microsoft.SharePoint;
using System.IO;
...
using (ClientContext context = new ClientContext(@"https://webapplicationname/sitename"))
{
   context.Credentials = System.Net.CredentialCache.DefaultCredentials;
   using (FileStream fs = new FileStream(@"c:\docs\sample.doc", FileMode.Open))
   {
      Microsoft.SharePoint.Client.File.SaveBinaryDirect(context, @"/Documents/sample.doc", fs, true);
   }
}

One major thing I'd like to highlight in the code above is that I was trying to set the context to reference a subsite that is located under the root site collection.  After a bit of experimentation, I actually stumbled onto the solution which is as follows:

using (ClientContext context = new ClientContext(@"https://webapplicationname"))
{
   context.Credentials = System.Net.CredentialCache.DefaultCredentials;
   using (FileStream fs = new FileStream(@"c:\docs\sample.doc", FileMode.Open))
   {
      Microsoft.SharePoint.Client.File.SaveBinaryDirect(context, @"sitename/Documents/sample.doc", fs, true);
   }
}

In the code that worked, notice that I set the context to reference the root site collection and then used the second parameter in the SaveBinaryDirect method call to specify the relative path to the document library on the site in question.  At that point, the code worked perfectly and I was able to upload the document to the site's document library without any issues.

Hopefully, this may help solve your problem as well.

How to filter multiple metadata fields when using the SharePoint REST API

To place a filter on multiple metadata fields when you're leveraging the SharePoint REST API to extract information from a SharePoint list or document library, you can utilize the $filter operator; however, you'll need to be careful with how you structure the query or you'll get an error when submitting the request.

To combine these filters, you will need to utilize parentheses to seperate each unique filter operator.  To demonstrate this technique, I'm going to place two filters in my query that will be used to filter on two different metadata fields:

http://SharePointWebApp/SharePointSite/_api/web/lists/getbytitle('Documents')/items?$select=Name,LinkFilename&$filter=((Field1%20eq%20'FilterValueOnField1')%20and%20(Field2%20eq%20'FilterValueOnField2'))&$orderby=Name%20asc

Essentially, the basics of the REST call is that it will obtain the the Name and LinkFilename fields from documents contained in the default Documents library and order the results alphabetically. These results will be filtered so that only documents that have a value in Field1 equal to the specified value of FilterValueOnField1 and a value in Field2 equal to the specified value of FilterValueOnField2.

How to obtain the PerformancePoint Dashboard Designer for SharePoint 2013

There is a lot of documentation floating around the web concerning the use of SharePoint 2013's PerformancePoint Dashboard Designer, but I had to do quite a lot of searching before I was able to determine exactly how you obtain it in the first place.  Here are the steps:

If you don't have a Business Intelligence Center site collection located on your farm, you will first need to create one via the following steps:

1. Open Central Administration on your SharePoint 2013 environment
2. Select Application Management
3. Under Site Collections, select Create site collections
4. Choose the appropriate web application where it should be installed
5. Enter an appropriate Title and URL for your new site collection
6. For Select experience version, select "2013"
7. For Select a template, click on the Enterprise tab and select Business Intelligence Center
8. Select an appropriate Primary and Secondary Site Collection Administrator
9. Click OK

Once your Business Intelligence Center site collection is in place, use the following steps to obtain the PerformancePoint Dashboard Designer:

1. Log on to the Business Intelligence Center site collection via your browser
2. In the left navigation panel, select PerformancePoint Content
   
   
   
   
3. Click on the tab that reads "PERFORMANCEPOINT"
   
   
   
   
4. Click on the Dashboard Designer icon
   
   
   
   
5. Click the Run button when prompted
   
   
   
   

Once the installation is complete, the Dashboard Designer will open and you can now begin working on all those other tutorials you found.

Obtain the URL of your root website using Javascript

If you ever need to obtain the URL to your root website via Javascript code (i.e. http://rootWebsite), I've got a couple of good methods for doing so which are presented as follows:

If you're on a SharePoint site want to use CSOM:

var clientContext = new SP.ClientContext();
var rootWebsiteUrl = clientContext.get_site.get_rootWeb();

If you're on a SharePoint site and don't want to use CSOM:

var rootWebsiteUrl = _spPageContextInfo.siteServerRelativeUrl;

If you're not on a SharePoint site or, actually, have no idea what SharePoint is:

var currentLocation = document.location.toString();
var webApplicationNameIndex = currentLocation.indexOf('/', currentLocation.indexOf('://') + 3);
var rootWebsiteUrl = currentLocation.substring(0, webApplicationNameIndex);

Exception from HRESULT: 0x80131904

Over the years, I've periodically encountered an issue in which SharePoint will give you the following error message whenever you attempt to perform some simple write, update, or delete operations on a site (i.e. like adding a new list item to a SharePoint list):

Exception from HRESULT: 0x80131904

In my experience, this error message is indicating that SharePoint is not able to write information to the content database and the most common cause that I've encountered is that the location where SQL logs are stored has run out of drive space (i.e. this being the SQL server that hosts the SharePoint content database).  Typically, we've either deleted the older SQL transaction logs stored here or simply moved them to another location with more drive space (i.e. archive) in order to free up space on that drive location.  Once that delete/move operation of the log files is complete and more drive space is available, the SharePoint environment will return to normal operation immediately.

Good solution for evaluating your web applications on various mobile devices - Ripple Emulator

If you're looking for a quick and easy solution for being able to evaluate the look and feel of your web applications or SharePoint sites across various mobile devices, the Ripple Emulator add-on for Chrome (created by the folks at tinyHippos) is definitely a great tool to have in your toolbox.  Once you activate/enable the add-on against a targeted page of a site, it will allow you to quickly select a target device and instantly view the layout of your page as it would appear on that device.

For SharePoint 2013 device channels, one important note is that the user agent being posted by the emulator doesn't match the device (at least at the time of this writing); therefore, you'll want to specify the channel to use by appending the following line at the end of URL in your browser's address bar:

?DeviceChannel=DeviceUserAgent

An example would be as follows:

http://SPWebApplication/Sites/Site/default.aspx?DeviceChannel=iPhone

Obtain all files contained under folders and subfolders within a SharePoint Document Library

If you are looking for a way to programmatically return all documents of a specific content type located within a given SharePoint Document Library regardless of whether they are contained in folders and subfolders within the given library, I've provided the following sample method that will allow you to do just that:

private SPListItemCollection GetDocumentsByContentType(SPList docLibrary, string contentTypeName)
{
    SPQuery query = new SPQuery();
    query.ViewAttributes = "Scope=\"RecursiveAll\"";
    SPListItemCollection documents = null;
    try
    {
        query.Query = string.Format("<where><eq><fieldref name="ContentType"><value type="Text">{0}</value></fieldref></eq></where>", contentTypeName);
        documents = docLibrary.GetItems(query);
    }
    catch (Exception ex)
    {
        throw ex;
    }
    return documents;
}

The key to this code being able to return all documents, including documents contained under folders and subfolders, is that I've set the ViewAttributes property to be Scope="RecursiveAll".  Without this particular setting, the query would have only returned items that were located immediately under the SharePoint Document Library and would have skipped any documents contained under folders and subfolders.

Design Manager deployed Page Layout not displaying custom css file

I recently ran into an issue in which I was attempting to reference a custom css page within a Design Manager deployed page layout; however, I couldn't get the custom css page to load on web pages despite the fact that I was prominently referencing the css file in the proper location of the page layout's HTML file.  After doing a bit of research, I finally discovered that you also need to add a special attribute to the <link> element called "ms-design-css-conversion" and set the value to "no".  Here is an example of what your <link> element will look like in order for the custom css file to load properly:

<link href="MyCustomPageLayoutStyle.css" type="text/css" rel="stylesheet" ms-design-css-conversion="no" />

Also, here is a complete example that also shows you the proper location for referencing you custom css file within your custom page layout's HTML file:

<!--MS:<asp:ContentPlaceHolder id="PlaceHolderAdditionalPageHead" runat="server">-->
<!--CS: Start Edit Mode Panel Snippet-->
    <!--SPM:<%@Register Tagprefix="SharePoint" Namespace="Microsoft.SharePoint.WebControls" Assembly="Microsoft.SharePoint, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c"%>-->
    <!--SPM:<%@Register Tagprefix="Publishing" Namespace="Microsoft.SharePoint.Publishing.WebControls" Assembly="Microsoft.SharePoint.Publishing, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c"%>-->
        <!--MS:<Publishing:EditModePanel runat="server" id="editmodestyles">-->
        <!--MS:<SharePoint:CssRegistration name="&#60;% $SPUrl:~sitecollection/Style Library/~language/Themable/Core Styles/editmode15.css %&#62;" After="&#60;% $SPUrl:~sitecollection/Style Library/~language/Themable/Core Styles/pagelayouts15.css %&#62;" runat="server">-->
        <!--ME:</SharePoint:CssRegistration>-->
    <!--ME:</Publishing:EditModePanel>-->
    <!--CE: End Edit Mode Panel Snippet-->

    <link href="MyCustomPageLayoutStyle.css" type="text/css" rel="stylesheet" ms-design-css-conversion="no" /> 

<!--ME:</asp:ContentPlaceHolder>-->

Programmatically determine if a web-scoped feature has been activated

This may be of limited use, but if you're ever looking for a way to programmatically determine if a web-scoped feature has been activated on a given SharePoint site, here is a quick method you can use to obtain that information:

private bool IsFeatureActivated(Guid siteId, Guid webId, Guid featureId)
{
    bool isFeatureActivated = false;
    using (SPSite site = new SPSite(siteId))
    {
        using (SPWeb web = site.OpenWeb(webId))
        {
            if (web.Features[featureId] != null)
            {
                isFeatureActivated = true;
            }
        }
    }
    return isFeatureActivated;
}

Assign a Preview image to your custom Page Layouts

There are quite a few good sites out there that provide steps for creating custom page layouts using the new SharePoint 2013 Design Manager, but none of them bring up how you would go about assigning a Preview image to your new custom Page Layouts. This is most likely due to the fact it's pretty simple to do once you know where to look for it. Anyway, these procedures assume that you've already created and deployed your new custom Page Layouts using Design Manager. Here goes...

Add the Preview Images to the Master Page Gallery

1. Using your web browser, open up a link to your Master Page Gallery (i.e.  http://SharePointWebApp/_catalogs/masterpage/Forms/AllItems.aspx)
2. Double-click on the en-us directory
3. Double-click on the Preview Images directory
4. Click on the Files tab
5. Under the New section, click Upload Document
6. Click Browse..., navigate to your first Preview file, and click on it
7. Click Open
8. Click OK
9. For Content Type, select Master Page Preview
10. For UI Version, place a check in the 15 checkbox
    
    
    
    
11. Click Save
12. Once the image is uploaded, click on the image list item and select Publish a Major Version
13. Click OK
14. Repeat Steps 5-13 for each Preview Image

NOTE:  If you don't publish your files as shown in Steps 12-13, your Preview images will appear as a red X when the user is looking at the locations where the Preview image is viewed

Assign the Preview Images

1. Navigate back to the home page of the Master Page Gallery
2. If you used the Design Manager to add your custom Page Layouts, you will click on the .html file associated with your first custom Page Layout and select Edit Properties (that is, if they are still associated with each other which is most likely the case...)
3. Locate the Preview Image section
4. Enter the URL of the file in the Type the Web address: textbox  (i.e. http://SharePointWebApp/_catalogs/masterpage/en-US/Preview%20Images/PreviewImageName.png)
5. Enter a user-friendly description in the Type the description: textbox
6. Click Save
7. Repeat Steps 2-6 to assign the Preview images to their resepctive Page Layouts

Verify the Preview Image of a custom Page Layout

1. Navigate to a page on your site and click Edit
2. Click on the Page tab
3. Under Page Actions, click Page Layout
4. Verify that your custom Preview image has been assigned (i.e. in my example, my custom Page Layout is TestLayout1 and it's based on the Article Page content type)

List of "illegal" characters that cannot be included in a SharePoint file name

I've been working on a utility that our team is going to leverage for rapidly uploading a ton of existing Policy and Procedure documents from file shares into a SharePoint site (by the way, this includes defining the relevant document metadata which is why we aren't simply copying the files over).  During my investigation, I happened to notice that many of the file names in the file share contain characters that SharePoint is not going to like at all (i.e. '&' is the leading offender).  In case you're curious what these "illegal" characters are for file names, here is a complete list:

• Tilde
• Number sign
• Percent
• Ampersand
• Asterisk
• Braces
• Backslash
• Colon
• Angle brackets
• Question mark
• Slash
• Pipe
• Quotation mark

The source of this information is the following link: http://support.microsoft.com/kb/905231

If you're looking for a solution to this problem, one option is to use the code that I created in one of my previous posts that will allow you to rename the file as you're adding to to your document library:  Remove special characters from a string using Regular Expression

How to find SharePoint 2013 Central Administration

I know that I'll be laughing about this post in the future, but I've got to be perfectly honest and tell you that it took me nearly 20 minutes to figure out how to access SharePoint 2013 Central Administration in my new Windows 2012 Server environment.  Here two different methods that I finally stumbled upon ways that will make this happen:

Method 1:

1. Log on to your Windows 2012 Server environment
2. Click on the center of the screen
3. Move your mouse to the upper right corner of the screen and a panel will appear on the far right side of the screen
4. Click on the Search icon
   
   
   
   
5. Locate the Microsoft SharePoint 2013 section
6. Click on the SharePoint 2013 Central Administration icon
   
   
   
   
   

Method 2:

1. Log on to your Windows 2012 Server environment
2. Click on the center of the screen
3. Move your mouse to the upper right corner of the screen and a panel will appear on the far right side of the screen
4. Click on the Start icon
   
   
   
   
   
5. Move your mouse to the bottom of the screen and right click
6. Click on the All Apps icon
   
   
   
   
   
7. Locate the Microsoft SharePoint 2013 section
8. Click on the SharePoint 2013 Central Administration icon

If it makes things easier, you can pin this icon to the Start page or Taskbar by replacing steps 6 and 8, respectively, as follows:

1. Right-click on the SharePoint 2013 Central Administration icon 
2. Click on the appropriate pin option icon