Beta version of CAML tool with intellisense is ready!

If you haven’t already read my post about the browser-based CAML testing tool with intellisense that I’ve been working on, please read that first!

If you’d like to try the tool yourself, you can download it on GitHub here: https://github.com/inclinetechnical/QuickCAML

As noted on GitHub, please use the Issues page there to submit bugs, questions, or feature requests. While I may respond to a few things here or on LinkedIn when I have time, GitHub is the primary forum for that kind of stuff.

What I’m really looking for right now is for people (mostly developers) to download the tool, test it, and report back any major issues they find (like browser compatibility problems and things like that). I also welcome suggestions for new features.

I’ll be adding more updates on the tool in coming weeks, including putting out a tutorial on how to use all the query options.

Extension method to safely get list item field value in SharePoint 2013

For SharePoint 2013, here’s an extension method I wrote for SPListItem that’s turned out to be quite useful. It gets the value of a list item field in a “safe” fashion – that is, by returning a known default value if the field value is null or can’t be converted to the type you expect.

public static T GetValue<T>(this SPListItem item, string fieldName, T defaultValue = default(T))
{
    if (item == null)
        throw new ArgumentNullException("item");

    T value = defaultValue;
    object rawValue = item[fieldName];

    try
    {
        if (rawValue != null)
        {
            value = (T)rawValue;
        }
    }
    catch
    {
        try
        {
            // Direct cast failed, but try a type conversion just in case
            // we can still convert the raw value correctly (for example,
            // if the raw value is the string "true" and T is a boolean,
            // this allows us to correctly return a true boolean value).
            value = (T)Convert.ChangeType(rawValue, typeof(T));
        }
        catch { }
    }

    return value;
}

To use this, you need to put it inside a static class (like any other C# extension method) and add a using statement for Microsoft.SharePoint.

The defaultValue parameter is optional and specifies the default value to return. You can either pass in your own value or let C# determine it using the default keyword. The default keyword returns null for reference types, 0 for numeric types, and false for boolean types. For structs, it returns a new struct with each member initialized as described previously.

Because this is implemented using the standard SPListItem.Item[string] indexer, the behavior for finding fields by name is as follows:

  • First, SharePoint treats fieldName as the internal name of a field
  • If no match, SharePoint treats fieldName as the display name (title) of a field
  • If still no match, SharePoint treats fieldName as the static name of a field
  • If none of the above result in a match, an exception is thrown saying the field cannot be found

So as Microsoft says, the best performance comes when you pass fieldName as the internal name of a field because only the first check is done and the others are skipped.

Examples of usage:

// gets the integer value of "MyIntegerField" or -1 (programmer-specified default)
int value = listItem.GetValue("MyIntegerField", -1);

// gets the boolean value of "MyBooleanField" or false (C#-defined default)
bool value = listItem.GetValue("MyBooleanField");

// gets the lookup value of "MyLookupField" or null (C#-defined default)
SPFieldLookupValue value = listItem.GetValue("MyLookupField");

SharePoint 2010 compatibility: This extension won’t work as-is with SharePoint 2010 because of the optional defaultValue parameter. SharePoint 2010 uses version 3.5 of the .NET framework, and optional parameters are only supported in .NET 4.0 and above. For SharePoint 2010, I’d recommend overloading this method where one signature takes the optional parameter and one does not.

SharePoint CAML Query Testing App with Intellisense – Coming soon!

Update – Jan 21, 2015: You can now download and test a beta version of this tool! See this post for details: http://sharepointtaproom.com/2015/01/21/beta-version-of-caml-tool-with-intellisense-is-ready

Good news for SharePoint developers! I’m pretty far along on developing a free, browser-based CAML tool with intellisense and other cool features that lets you write and test CAML queries right from your browser. This is actually a tool I’ve been using myself for a while, but I’m adding a few things to it and releasing it to the larger community.

Here’s a sneak preview that shows the code editor (with intellisense) and some of the options:

caml_tool

The tool is still a work-in-progress so the above screenshot may not reflect how the final version looks, but you get the general idea.

I know some of you will wonder why I’m creating this when there are other CAML tools out there, so here are my reasons:

  • The best intellisense for CAML. Honestly, the coolest feature of this tool is the intellisense! It’s context-sensitive (aware of “where you are” in the query), and LIST FIELDS are included in the intellisense suggestions when you’re typing an attribute that takes a field name! How cool is that?! The intellisense feature has you covered for most tag names and attribute values you’re likely to run across.
  • View and page through results right in your browser. The results of your query are displayed in tabular form right in your browser for easy viewing. You can also copy results out in CSV format if you’d like to paste into Excel or another tool. In addition, paging of results is supported so you can keep your row limit (items per page) small to maximize query speed but still get the data you need.
  • Lots of query options. This tool gives you a pretty full set of query options (comparable to what you’d see in server-side code), including options like “Calendar Date” and “Expand Recurrence” (for querying and expanding recurring events). It’s also smart enough to enable and disable certain options depending which client-side API it has to use to execute the query (see “APIs and Query Functionality” later in this post).
  • No desktop software to install. Some companies are strict about (a) who has admin rights to install software on their PC, and (b) what software is allowed to be installed on local PCs, including running periodic scans to ensure compliance. When I work with clients like that, I often turn to browser-based tools like this to avoid red tape and get work done faster.
  • This is a code-oriented tool. I’m a developer. I write code. And when I don’t know the syntax for something, I rely on intellisense and other resources to help me learn the language I’m coding in so I can get more proficient at it. Why? Because once I’m proficient, I can just write code and test things quickly. I don’t have to deal with cumbersome UIs or various layers of abstraction. This is a code-oriented tool. If you need help learning CAML or remembering syntax, you’ll get it. But if you already know CAML, you can just write code and go.
  • No dependencies on other tools (besides SharePoint itself). Not much else to say on this one. There are a couple of JavaScript dependencies (see “Technologies and frameworks” later in this post), but I load those from a CDN so there’s nothing to install or upload to use them.
  • It will teach you about CAML and the client-side APIs. If you mess up something in your CAML query, you’ll find out quickly, can tweak it, and try again. (For example, did you know the order of your <FieldRef /> tags matters inside of a <DateRangesOverlap> tag? I found that out by using this tool!) And if you’re curious about how I did anything in this tool, all you have to do is right-click the page and view the source. It’s just JavaScript and HTML! Nothing is hidden.

What can this be used for? Well, here’s how I’ve used it so far:

  • Finding internal names of list fields.  There are probably 100 ways to find internal names of list fields in SharePoint, but this tool makes it super simple and easy. I usually just pull it up and start typing like you see in the screenshot above, and bam, I’ve got my field names for using in XSL or client object model code or whatever.
  • Testing large list limits. I’ve built several solutions involving large lists, and testing the query throttling limits is always a pain. This tools makes it easy because I find out real quick what kinds of queries generate query throttling exceptions, and I can tweak them.
  • Plain old CAML query testing. Perhaps the most obvious use case, I use it to write and test CAML queries before inserting the queries into code elsewhere. In the past I’d use PowerShell, but this is even easier, and PowerShell isn’t always an option depending on the environment and restrictions.
  • Ad-hoc reporting. Sometimes people ask me for random, one-time reports of data in SharePoint. I use this tool to grab what they want and export it to Excel. In some cases I’d make a SharePoint list view and use the “Export to Excel” button in the ribbon, but depending on the type of query I’m doing, list view don’t always cut it. (And honestly, as someone proficient in CAML, this is even faster for me than creating list views most of the time.)

The name of the tool will be QuickCAML, which I think plainly describes its purpose: to write and test CAML queries QUICKLY.

Regarding distribution, I thought about the “app model” but am mostly convinced that’s a bad idea for a variety of reasons. If you’re wondering why, just read this post: http://blog.furuknap.net/sharepoint-2013-app-model-the-jury-is-back. Bjørn does a great job summarizing what I think about the app model.

Instead, I’ll likely make this available as a download you can just “snap into SharePoint” by (a) uploading an ASPX file to a Site Pages library, or (b) copying-and-pasting code directly into Script Editor and Content Editor web parts. Again, the goal here is simplicity. I don’t see a reason to mess with sandboxed solutions or apps for this as neither are necessary.

Technologies and frameworks: I built this tool using Knockout JS, jQuery, and the Ace Editor for JavaScript (the last of which I made a few enhancements to in order to support CAML syntax better).

APIs and Query Functionality: I used two client-side APIs to grab data from SharePoint: the JSOM (JavaScript Object Model) and the “Lists” web service. I didn’t want to use the latter API at all but had no choice since not all query options supported by the web service are supported by the JSOM API. Oddly enough, the reverse is true too. JSOM supports some stuff the web service doesn’t, so I had to use both to get as close as possible to the server-side API. Don’t worry, though, you don’t have to pick an API when using the tool. Just write your code, pick your options, and the tool will figure it out for you (and tell you on the results page which API was used to run your query).

Look for more updates soon.

Data source details link disabled in SharePoint Designer 2013

I haven’t seen anyone else write about this yet, but now that I’ve run into this problem several times I decided it was time to post about it.

I’m talking about some strange behavior with the Data Source Details pane in SharePoint Designer 2013.

Here’s the scenario I ran through:

  1. I created a new wiki page in my Site Pages library and opened it in SPD 2013 for editing so I could add a custom data view.
  2. After using the usual “Parse Html” hack to get the Data View button to light up on the Insert tab, I chose to insert an Empty Data View onto my page.
  3. I then clicked inside my data view web part tag (because remember, we have no design view in SPD anymore) and inserted a data source using the Data Source button on the Insert tab.
  4. I next tried to edit my data source in the Data Source Details pane, but the link under the “Current Data Source” label (which in my case said “DataView 1…”) was disabled. Clicking around and saving/refreshing the page in SPD didn’t help.

After a few minutes, I discovered the workaround is to hide and re-show the Data Source Details pane using the Data Source Details button on the Options tab of the “Data View Tools” group in the ribbon (while my cursor was inside of the data view web part tag, which causes the “Data View Tools” group to be visible). Clicking that button twice to hide and re-show the pane fixed the problem, and I could now edit my data source.

I must say, I continue to be dismayed with how strangely SharePoint Designer 2013 behaves sometimes. It seems like at least 50% of the tasks I try to accomplish require at least one workaround to successfully complete the task.

Stopping SharePoint from Messing Up Protocol-Relative Links Added with SharePoint Designer

Protocol-relative links are a standard way to avoid mixed content warnings (“This page contains both secure and nonsecure items”) when viewing SharePoint pages. However, if you’re editing a page in SharePoint Designer and try to add one, you’ll find that it doesn’t work forever – SharePoint will eventually mess the link up, usually when you least expect it.

In this video I demonstrate the problem and explain a solution I found to stop SharePoint from messing up protocol-relative links added with SharePoint Designer.

How to fix broken navigation links after SharePoint upgrade or migration

I was recently testing a SharePoint 2007-to-2013 upgrade for a client and was reminded of a problem where links can break in the upgraded/migrated site if the base URL changes along the way.

Here was the scenario:

  1. Client had a 2007 SharePoint site with a base URL similar to http://intranet.
  2. We did a database-attach upgrade to SharePoint 2013 and set up a base URL for testing similar to http://SP-2013:12000 (using a port number rather than host header).
  3. In the upgraded site, some of the links to pages and sub-sites were still referencing the old URL at http://intranet. Other links worked fine and referenced the new URL.

Root cause:

Navigation links that were added by site owners as relative links in the old site continued to work, and links that were added and managed automatically by SharePoint continued to work as well.

Links that were added as absolute links in the old site are what failed to migrate over because they were effectively “hard-coded” to reference the old URL.

Resolution:

The easiest fix was to use a PowerShell script that examined every site collection and site within our upgraded web application and fixed the links in the navigation menus. To do this, the script would look for absolute links that referenced the old base URL and update those links to be relative so they’d work in the new environment.

I checked online and found a few scripts to do this, but they all had problems. Among the problems I found were:

  • Poor coding practices (i.e. not disposing of objects, improper checking of URLs, etc.)
  • Only going 1 or 2 levels deep in the navigation hierarchy and missing deeper links.
  • Checking and fixing links in the Quick Launch menu or the top navigation menu but not both.

So, as a PowerShell guy, I decided to write my own script and avoid the problems I listed above. The client and I ran it earlier today, and all the links were fixed and work fine now.

Here’s the script I used:

###############################################################################
# Configuration variables.
###############################################################################

$WebAppUrl = 'http://newsite.contoso.com'
$OldHostName = 'intranet'

###############################################################################
# Script execution logic.
###############################################################################

function UpdateMenuLinks($topNode) {
	$count = 0
	foreach ($node in $topNode.Children) {
		$uri = New-Object System.Uri($node.Url, [System.UriKind]::RelativeOrAbsolute)
		if ($uri.IsAbsoluteUri -and $uri.Host -eq $OldHostName) {
			$node.Url = $uri.PathAndQuery
			$node.Update()
			$count++
		}
		$subCount = UpdateMenuLinks($node)
		$count += $subCount
	}
	return $count
}

$webApp = Get-SPWebApplication $WebAppUrl -ErrorAction Stop

$webApp.Sites | foreach {
	$site = $_
	try {
		$site.AllWebs | foreach {
			$web = $_
			try {
				"Checking nav links in $($web.Url) ..."
				$fixedTopLinkCount = UpdateMenuLinks($web.Navigation.TopNavigationBar.Parent)
				$fixedLeftLinkCount = UpdateMenuLinks($web.Navigation.QuickLaunch.Parent)
				if ($fixedTopLinkCount -gt 0 -or $fixedLeftLinkCount -gt 0) {
					" - Updated $($fixedLeftLinkCount) quick launch link(s) and $($fixedTopLinkCount) top menu link(s)."
				}
				else {
					" - No links updated."
				}
			}
			finally {
				$web.Dispose()
			}
		}
	}
	finally {
		$site.Dispose()
	}
}

"Done. Finished checking all sites in web application."

The script produces output like this (cut down a lot to save space):

Checking nav links in http://newsite.contoso.com/sites/it ...
- Updated 1 quick launch link(s) and 2 top menu link(s).
Checking nav links in http://newsite.contoso.com/sites/finance ...
- Updated 3 quick launch link(s) and 2 top menu link(s).
Done. Finished checking all sites in web application.

There are two variables to set at the top. One is the URL of the target web application where you want the links fixed (your upgraded/migrated site), and the other is the old host name (base URL without the http/https part) that needs to be searched for.

If you choose to copy and use this script, you do so AT YOUR OWN RISK. I will not be held liable for anything that happens in your environment. As always, I recommend TESTING things like this before you use it in a production environment.

Lastly, this only fixes navigation links in the Quick Launch and top navigation menus. It does not fix links within content such as pages or web parts or in code such as JavaScript code. You’ll either need to fix those manually or write another script to address those things. In my case it wasn’t necessary to automate fixing links to that level, so I didn’t worry about it.