Require at Least One Field in SharePoint

Applies To: SharePoint 2010

In SharePoint just checking the box for making a column required or not isn’t always sufficient. Sometimes you want to be able to say something is only required based on the status of another column. This can be done through a list’s validation settings.

This recently came up for me when the requirement was that for a contact at least an email address OR a phone number would be required. A contact didn’t need to have both (although they could), but having neither wasn’t an acceptable option.

This is actually relatively simple. In the List Settings just click on Validation settings to provide a custom formula. Here’s mine:

=COUNTA([Email],[Phone])>=1

The COUNTA function returns the number of non-blank entries. You can put as many columns as you’d like in between those parentheses. If you’re only requiring one of these, the simple >= 1 check makes sure that at least one of those columns is not blank. Add a nice User Message and you’re good to go:

Got multiple requirement groups? Wrap multiple COUNTA calls in an AND statement.

Here’s what it will look like if one of those is left blank (After you hit Save):

Field Validation

One of the nice things is that you can do field level validation in addition to the list level validation shown above. The field level validation (Column Validation) fires before the list level which creates for a smooth user experience.

In my previous posts (Phone Validation, Email Address Validation) I showed how to setup column validation for both phone numbers and email addresses and those can be used here. However, the formulas I demonstrated automatically make those required fields since they don’t allow blank fields to pass validation.

To adjust those formulas just add an OR statement around the AND with an ISBLANK function. So you can do something like this:

=OR(ISBLANK([YourColumn]),AND(....))

So for the Phone Number validation previously posted you can switch it to:

=OR(
	ISBLANK([Phone]),
	AND(
		LEN([Phone])=14,
		IF(ISERROR(FIND("(", [Phone],1)),
			FALSE,
			(FIND("(", [Phone]) = 1)
		),
		IF(ISERROR(FIND(")", [Phone],5)),
			FALSE,
			(FIND(")", [Phone], 5) = 5)
		),
		IF(ISERROR(FIND(" ", [Phone],6)),
			FALSE,
			(FIND(" ", [Phone], 6) = 6)
		),
		IF(ISERROR(FIND("-", [Phone],10)),
			FALSE,
			(FIND("-", [Phone], 10) = 10)
		),
		IF(ISERROR(1*CONCATENATE(MID([Phone], 2, 3), MID([Phone], 7, 3), MID([Phone], 11, 4))),
			FALSE,
			AND(
				1*CONCATENATE(MID([Phone], 2, 3), MID([Phone], 7, 3), MID([Phone], 11, 4)) > 1000000000,
				1*MID([Phone], 2, 3) <> 911,
				1*MID([Phone], 7, 3) <> 911,
				1*MID([Phone], 7, 3) <> 555
			)
		)
	)
)

Validate Email Address Columns in SharePoint

Applies To: SharePoint 2010

The column validation feature of SharePoint 2010 is pretty awesome but it can be relatively basic when compared to something like regular expressions. Yesterday I posted about validating phone number columns in SharePoint. Another common request is email addresses.

Proper validation of email addresses can be extremely complicated (just check out the Syntax section of the Wikipedia article). I’m sure you can get extra crazy with it and get it even closer to the actual specification, but for my needs some basic validation is all I’m really looking for.

The basic rules I’m enforcing are:

  1. No Spaces
  2. Must have 1 and only 1 @ symbol
  3. The @ symbol cannot be the first character
  4. Must have at least 1 . after the @ symbol
  5. Must have at least 1 character between the @ and the .
  6. The . cannot be the last character

The formula to do that is:

=AND(
	ISERROR(FIND(" ", [Email],1)),
	IF(ISERROR(FIND("@", [Email],2)),
		FALSE,
		AND(
			ISERROR(FIND("@",[Email], FIND("@", [Email],2)+1)),
			IF(ISERROR(FIND(".", [Email], FIND("@", [Email],2)+2)),
				FALSE,
				FIND(".", [Email], FIND("@", [Email],2)+2) < LEN([Email])
			)
		)
	)
)

To get this working in SharePoint, just copy the above and do a find and replace on [Email] with whatever your column is named. SharePoint will remove all the extra line breaks as soon as you save it.

What’s Happening

Column Validation works by returning true or false. Starting our formula with an AND statement allows us to pass multiple conditions (also returning true or false) and will return true only if all the conditions return true. This allows us to do multiple checks and ensure they all validate correctly.

The first check in Line 2 uses the FIND function to check for spaces. The ISERROR function wrapper returns true for any errors found. Since the FIND function returns an error if the string is not found, we’ll get a true for this check only when there are no spaces. This takes care of rule #1.

The second check in line 3 searches for an @ symbol using the FIND function beginning with the 2nd character. This ensures the @ symbol is not the first character (rule #3).

Having found the @, we put a second AND statement in line 5 to check some things concerning the @ we just found. The check in line 6 uses a FIND to look for another @ symbol starting with the character after (+1) where we found the first one. This takes care of rule #2.

Next we check for a period after the @ symbol in line 7. We do something similar to the above check except that we start with the 2nd character after (+2) where we found the @ symbol. This ensures there is at least 1 character between the @ and the period (rule #5) while making sure there is at least 1 period (rule #4).

Now that we’ve established there is a period after the @ we make sure that the location of that period is less than the length of the whole string using the LEN function. This makes sure the last character is not the period (rule #6).

There are several holes here which could be corrected by complicating the formula quite a bit (I’d be happy to have suggestions in the comments), but for 98% of all entries this is going to be sufficient. If this is for a public facing form you’ll probably want to invest more time in increasing the complexity of this formula, but for your normal internal sites this should be more than good enough.

Just open your list settings and edit/add your email column and expand the column validation section and paste the formula from above in there:


Side Note: The above formula will automatically make this a required column since the validation doesn’t allow blank columns. An easy fix for this is to wrap the above formula in an OR statement with an ISBLANK function. So something like this:

=OR(ISBLANK([YourColumn]),AndStatementFromAbove)

More information and a full example can be found on my Requirement Groups entry.

Validate Phone Number Columns in SharePoint

Applies To: SharePoint 2010

The column validation feature of SharePoint 2010 lists can save you a lot of headache and prevent problems before list items are ever added. I recently added a Phone column to a custom list and wanted to validate it was an actual phone number. Unfortunately, my handy dandy regular expression that I always use in .NET wouldn’t work here since you are limited to using the calculated field formulas (Excel).

Some quick searching turned up Robert Freeman’s blog where he gives a great formula for ensuring phone numbers are entered in the form (###) ###-####. This worked perfectly for me. However, I wanted to make sure I understood it so I broke it out into logical sections and went through it.

I’ve taken his formula and added 2 additional checks (Length = 14 and ensuring the number doesn’t start with 555) and formatted it to be more readable:

=AND(
	LEN([Phone])=14,
	IF(ISERROR(FIND("(", [Phone],1)),
		FALSE,
		(FIND("(", [Phone]) = 1)
	),
	IF(ISERROR(FIND(")", [Phone],5)),
		FALSE,
		(FIND(")", [Phone], 5) = 5)
	),
	IF(ISERROR(FIND(" ", [Phone],6)),
		FALSE,
		(FIND(" ", [Phone], 6) = 6)
	),
	IF(ISERROR(FIND("-", [Phone],10)),
		FALSE,
		(FIND("-", [Phone], 10) = 10)
	),
	IF(ISERROR(1*CONCATENATE(MID([Phone], 2, 3), MID([Phone], 7, 3), MID([Phone], 11, 4))),
		FALSE,
		AND(
			1*CONCATENATE(MID([Phone], 2, 3), MID([Phone], 7, 3), MID([Phone], 11, 4)) > 1000000000,
			1*MID([Phone], 2, 3) <> 911,
			1*MID([Phone], 7, 3) <> 911,
			1*MID([Phone], 7, 3) <> 555
		)
	)
)

To get this working in SharePoint, just copy the above and do a find and replace on [Phone] with whatever your column is named. SharePoint will remove all the extra line breaks and make it ugly again as soon as you save it.

What’s Happening

Column Validation works on the simple principal of returning true or false. So Freeman starts his formula in line 1 with an AND statement. An AND statement allows you to pass multiple conditions (also returning true or false) and will return true only if all the conditions return true. This is a great way to ensure that multiple checks all validate correctly.

The first check in line 2 uses the LEN function to ensure that the total length of the entry is exactly 14 characters (10 numbers, 2 parenthesis, a space, and a dash). This is a slight improvement over the original since any entry with a properly formatted phone number at the start could be used. This prevents someone from adding extra numbers or characters.

The second check in lines 3-6 uses the FIND function to see if the first character is the (. It’s wrapped in an ISERROR check because the FIND function returns an error if the string is not as long as the start number (in this case, empty) or if the text wasn’t found at all. So if it wasn’t found (error), we return false. If it was found then there is a check to see where it was found (FIND returns the character number where the search text was found). In this case we want it in position 1.

The next 3 checks in lines 7-18 are essentially the same as above just looking for the closing parenthesis in position 5, a space after the parenthesis in position 6 and a dash in position 10.

In line 19, a check is performed to ensure that the number portions of the entry are actually numbers. The 3 sections of numbers are put together using a CONCATENATE function and are multiplied by 1. If the multiplication fails (because the string can’t be converted to a number) the error condition is caught by the ISERROR wrapper and FALSE is returned. An ISNUMBER function would have worked here as well. If it’s a number, then some additional checks are performed in a new AND clause beginning in line 21.

Line 22 uses the CONCATENATE function to ensure that not only are the numbers numbers, but also that they aren’t all zeroes.

Lines 23-25 use the MID function to ensure that the area code and the prefix don’t equal 911 and that the prefix also doesn’t equal 555 since these would not be valid numbers.

That’s it! Open your list settings and edit/add your phone column and expand the column validation section and paste the formula from above in there:

 


Side Note: The above formula will automatically make this a required column since the validation doesn’t allow blank columns. An easy fix for this is to wrap the above formula in an OR statement with an ISBLANK function. So something like this:

=OR(ISBLANK([YourColumn]),AndStatementFromAbove)

More information and a full example can be found on my Requirement Groups entry.

Extract Timer Job History Using PowerShell

Applies To: SharePoint 2010, PowerShell

I was tasked with finding all timer jobs that ran in a given time period. Some quick searching turned up a pretty cool solution by Glyn Clough using PowerShell. I took his script and modified it some to account for UTC times and it works great. Although I’m presenting my modified script, the bulk of the work was done by Glyn and I’m really just tweaking it a little.

The Script

Param(
	[parameter(position=0)]
	[DateTime]
		$StartTime,
	[parameter(position=1)]
	[DateTime]
		$EndTime
)

if(!$StartTime) {$StartTime = (Get-Date).Date}
if(!$EndTime) {$EndTime = (Get-Date).AddDays(1).Date}

$StartTime = $StartTime.ToUniversalTime()
$EndTime = $EndTime.ToUniversalTime()

$TZ = [System.TimeZoneInfo]::FindSystemTimeZoneById(((Get-WmiObject win32_timezone).StandardName))

Get-SPWebApplication | foreach {
	$_.JobHistoryEntries |
		where{	($StartTime -le $_.StartTime -and $_.StartTime -le $EndTime) -or
			($StartTime -le $_.EndTime -and $_.EndTime -le $EndTime) } |
		sort StartTime |
		select	JobDefinitionTitle,
			WebApplicationName,
			ServerName,
			Status,
			@{Expression={[System.TimeZoneInfo]::ConvertTimeFromUtc($_.StartTime, $TZ)};Label="Start Time"},
			@{Expression={[System.TimeZoneInfo]::ConvertTimeFromUtc($_.EndTime, $TZ)};Label="End Time"},
			@{Expression={($_.EndTime - $_.StartTime).TotalSeconds};Label="Duration (secs)"}
} | Out-GridView -Title "Timer Job History"

You can copy the above script save it in a text file with a ps1 extension and run it from the console. Assuming you’ve named the file JobHistory.ps1 you can run it in a couple of different ways:

No Parameters:

Running it this way will return the entire history for the current day starting at midnight.

Specify Start Time:

This is especially helpful if you’re just trying to find the most recent history since this will give you the full history starting at the specified date/time to now.

Specify Range:

Doing this will return all history entries for the given range. The date/time parameters can be entered in a variety of ways since powershell is converting the string to a date you can enter the date/time in a format that matches your culture/locale.

The Output

The results are funneled to a GridView which requires the Windows PowerShell Integrated Scripting Environment (ISE) to be installed. On a server this is as simple as opening Server Manager, selecting features, Add Features, then choosing the Windows PowerShell Integrated Scripting Environment and installing (This did not require a restart).

There are many benefits to using the GridView. The best is the filtering, but I also like the sorting and copy/paste functionality. I often filter on job status or sort by duration to catch problem jobs. Then I can copy those rows and paste them directly into Excel if needed.

The above script also outputs histories for every web application (And will immediately show the GridView when the first is done and then slowly add the remaining ones). This could be changed by modifying the above script and adding a parameter, but this was unnecessary for me. You can also use the GridView filter to show only the web application you need.

Obviously yours will show the WebApplicationName and ServerName without my sloppy black bars

Open a Link in SharePoint 2010’s Modal Dialog

Applies To: SharePoint 2010

Recently I’ve been customizing the XSLT of some of my XsltListViewWebParts. Getting all of that to work is worth another post in itself, but I wanted to talk briefly about a small frustration I had. I was customizing an announcement’s list part and I stripped out most of the nearly 1700 lines of XSLT used by default. However, one of the things I liked was being able to open the announcement in the modal dialog (sometimes called the Lightbox or the popup window):

Some searching through the autogenerated XSL for my view, I came across this section in the LinkTitleNoMenu.LinkTitle template:

<a onfocus="OnLink(this)" href="{$FORM_DISPLAY}&amp;ID={$ID}&amp;ContentTypeID={$thisNode/@ContentTypeId}" onclick="EditLink2(this,{$ViewCounter});return false;" target="_self">
	<xsl:call-template name="LinkTitleValue.LinkTitle">
		<xsl:with-param name="thisNode" select="$thisNode"/>
		<xsl:with-param name="ShowAccessibleIcon" select="$ShowAccessibleIcon"/>
	</xsl:call-template>
</a>

I’m going to dissect what’s happening in terms of XSL for the next couple of paragraphs. If you’re just looking for the format needed, skip to the Link Format section.

Basically this is the link that gets generated inside the view’s table. The call-template element is used to fill the contents (link text), but I already had that covered and am mostly just interested in the formatting of the link to do the modal dialog magic.

Some quick experimentation shows that the onfocus call was not needed for the popup (This is what causes the menu to display and the box around the row in a standard view). Also not needed is the target=”_self” since this is equivalent to leaving the target attribute out entirely. There are really just 2 key items:

HREF

This is the URL to display in the modal dialog. In this case, it’s generated using a number of variables defined automatically. The $FORM_DISPLAY is the absolute path to the item display page. The $ID is generated using a simple Template call (we’ll come back to this). and the $thisNode/@ContentTypeId is pulling the ContentTypeId attribute from the current Row element in the $AllRows variable populated by the dsQueryResponse XML. For now, all you need to know is that it is automatically finding the display form URL and populating the necessary ID and ContentTypeId query strings for the specific item URL.

OnClick

This calls the EditLink2 javascript method defined in Core.js. This extracts the link with the webpart’s ID ($ViewCounter) and shows it in the modal dialog. Then it returns false to prevent the browser from following the link like normal.

Trying to implement this exactly in my code wasn’t too hard. Unfortunately, it wouldn’t load in the popup and always just opened the page directly. Doing some searching, I came across a quick explanation and solution on technet. The EditLink2 function attempts to use a window object referenced by my webpart’s id ($ViewCounter). Whatever code sets this all up wasn’t firing in my XSL causing the window reference to be NULL and making the function default to just opening the link. Instead of tracking it down somewhere in the default generation, I did something similar to the proposed solution on technet.

Link Format

Ultimately my goal was to have a link generated using this format:

<a href="http://mysharepoint.com/sites/thesite/_layouts/listform.aspx?PageType=4&amp;ListId={SomeGUID}&amp;ID=SomeID&amp;ContentTypeID=SomeContentTypeID" onclick="ShowPopupDialog(GetGotoLinkUrl(this));return false;">Click Me</a>

So, I’m using the same link generation (but this could be any link). The real difference is that instead of calling EditLink2 I’m calling ShowPopupDialog. For the URL, I’m using a technique found in the EditLink2 method of calling GetGotoLinkUrl which extracts the URL from the link element.

XSL Implementation

To get this to work in XSL, you can do something similar to this:

<xsl:for-each select="$AllRows">
	<xsl:variable name="thisNode" select="."/>
	<xsl:variable name="link">
		<xsl:value-of select="$FORM_DISPLAY" />
		<xsl:text>&amp;ID=</xsl:text>
		<xsl:call-template name="ResolveId">
			<xsl:with-param name="thisNode" select ="$thisNode"/>
		</xsl:call-template>
		<xsl:text>&amp;ContentTypeID=</xsl:text>
		<xsl:value-of select="$thisNode/@ContentTypeId"/>
	</xsl:variable>

	<a onclick="ShowPopupDialog(GetGotoLinkUrl(this));return false;">
		<xsl:attribute name="href">
			<xsl:value-of select="$link"/>
		</xsl:attribute>
		<xsl:text>View Announcement</xsl:text>
	</a>
</xsl:for-each>

In the above XSL, we’re looping through each row returned by your view’s CAML query. We setup a link variable that builds the full HREF attribute in lines 3-11. The thing to note is the call to the ResolveId template to pull the item’s ID from the row. This is a standard template that will automatically be referenced as long as you keep the standard includes (main.xsl and internal.xsl).

Then we generate the actual html link in lines 13-17 using the $link variable we created above. This could be consolidated some, but hopefully it’s relatively easy to follow in this format.

That’s it! Now you can generate those links using XSL or follow the link format to make them on your own (like in a content editor web part).

Using Feed Parameters in the RSS Viewer Web Part

Applies To: SharePoint 2010, RSS Viewer Web Part

The RSS Viewer web part is an Out of the Box SharePoint web part that can really add to your site by allowing you to integrate dynamic content directly on your pages from other sites in your environment (requires either anonymous or kerberos authentication) or from sites all over the web. You can add it like any other web part (Located under Content Rollup) and get it going quickly by setting the Feed URL property.

There are feeds all over the web and getting the URL for these is generally straight forward (click the RSS icon/button and then copy the URL) and I’ll let you figure that part out. If you’re simply grabbing a news feed from Yahoo! or someplace, then you’re probably done. There’s a lot that can be done with the formatting of the feed’s content using XSLT, but that’s out of scope for this particular article.

However, what if you would like something a little bit more dynamic? Some feeds are very customizable using query string parameters to automatically filter what you want. A good example might be pulling the SEC Filings from the Securities and Exchange Commission website. You can get a feed of Google’s Filings using the following URL:

http://www.sec.gov/cgi-bin/browse-edgar?action=getcompany&CIK=0001288776&type=&dateb=&owner=exclude&start=0&count=40&output=atom

There are several parts to this URL beyond the base of http://www.sec.gov/cgi-bin/browse=edgar (They begin after the ? mark and are separated by the & and take the format of name=value). The one I’m most interested in is the CIK (Central Index Key). This is what tells the feed to return Google’s filings. (As a bonus, for publicly traded companies you can replace that 10 digit number with their Ticker Symbol: GOOG for Google).

Great! Now you can generate a feed for multiple companies’ SEC filings using multiple RSS Viewer web parts. But what if you only want one web part that can switch which company is being displayed? That’s exactly where Feed Parameters come in.

Configure the Filter

Like many web parts, the RSS Viewer web part allows connections to other parts including the filter web parts. Most any of the Filter parts will work depending on what you’re trying to accomplish. An obvious example would be to use the SharePoint List Filter web part to pull company ticker symbols in from another SharePoint list and use a drop down to select which filings to view. My preference, however, is to use a Query String (URL) Filter part so that I can hook it to multiple parts to create a dynamic page that can be linked as if it were many pages. I’ll show you how to hook that up, but the basic concept is the same for any of the parts – Filter some value and send it to the RSS Viewer.

Add a Query String (URL) Fitler part to your page (Located under Filters):

From the tool pane for the Query String (URL) Filter part the key values can be found in the Filter section. The Filter Name property should match the query string property name in your RSS Feed’s URL. So in this case our Filter Name is CIK. The Query String Parameter Name is the name of your own query string to the page. In this case I’ve set it to Symbol. The Default Value is optional, but is generally a good idea so that if the page is accessed without a query string, the feed still shows something. I’ve set ours to GOOG (Although I could easily have set it to Google’s CIK value since the SEC feed is pretty flexible):

It’s also a good idea to also give your filter web part a better name than the default since this is the name you’ll see when setting up the connection. This is just the Title property in the Appearance section. I’ve set mine to Symbol Filter. Press OK to save your changes.

Setup the Connection

Using the drop down menu for the RSS Viewer Web Part choose Connections > Get Feed URL From > Symbol Filter (If you don’t have a Connections menu, select Edit Web Part and look again):

If everything has been successfully connected the Query String (URL) Filter should look similar to this when editing the page (it isn’t visible normally):

If you add ?Symbol=MSFT to the end of your page’s URL (If your page is http://app/site/SitePages/Home.aspx then the filtered url would be http://app/site/SitePages/Home.aspx?Symbol=MSFT) then the RSS Viewer should now be showing Microsoft’s latest SEC Filings. Just kidding! There’s one little gotcha that’s very easy to fix.

If a feed parameter is already set in the RSS Viewer’s URL, then any feed parameters from connected web parts are ignored. In this case, this is because we pasted the sec.gov feed’s URL in directly including the CIK=0001288776 section of the URL. To fix this, just delete this part of the URL. So our new feed URL will look like:

http://www.sec.gov/cgi-bin/browse-edgar?action=getcompany&type=&dateb=&owner=exclude&start=0&count=40&output=atom

Now try that filtered URL (Your page + ?Symbol=MSFT) again and you should see Microsoft’s SEC Filings:

This quickly gives us a lot of flexibility. This works great for all sorts of feeds allowing you to filter or customize your feeds before they ever get to you; and all on the fly.

Conclusion

So here’s the summary of what you need to do to get this working:

  • Add an RSS Viewer web part and set the Feed URL to the address of the feed you want to pull. Remove the dynamic parameter you want to set.
  • Add a Filter web part and configure it to pass the filter name as the name of the dynamic filter in the feed URL
  • Connect the RSS Viewer web part to the Filter web part using the Feed Parameter connection
  • Shout Abracadabra every time anyone uses a dynamic view of the page

The RSS Viewer part can be a very powerful part of your page allowing dynamic content without any custom code.

Hiding the Recently Modified Section in SharePoint 2010

Applies To: SharePoint 2010, CSS

I recently added a Wiki Pages Library to a site for some end users and they really like it. However, they had a seemingly straight forward request to hide the Recently Modified section that was showing up above the Quick Launch:

This may come up as a requirement when using some of the default templates that automatically include a Site Pages library or if a user adds a new page and is prompted to create the Site Pages library automatically.

I assumed there was a setting somewhere either for the library or the site in order to turn off this “feature”. Nope. Somebody decided that this was not only a feature everyone would want, but it was so great they put it in the left actions content place holder (PlaceHolderLeftActions) of the master page – which puts it on top of the quick launch.

Some quick searching turned up “solutions” that suggested setting the contentplaceholder’s visible property to false within the master page. This works; however, it also hides anything that uses that contentplaceholder such as some of the Blog tools. This makes it a very poor candidate for a farm wide branding solution.

The other option is to use some CSS (cascading style sheets). If you’re pushing this as part of a branding solution, just add this to one of your style sheets:

.s4-recentchanges{
	display:none;
}

That’s it. Microsoft provided a very handy class just for this section and some quick use of the CSS Display property takes care of it.

So what if this is just a one off thing – You aren’t currently using any custom branding or just want it to affect one site? For a single site you can use SharePoint Designer 2010 to open the master page (v4.master – choose edit in advanced mode). Then somewhere on the page add the following:

<style>
.s4-recentchanges{
	display:none;
}
</style>

If you just want to apply it page by page, you can put the style directly in the HTML of the page. Since this is a Wiki page, choose to edit the page (Under the Page Ribbon assuming you have the rights). Click anywhere on the page and choose the HTML drop down and pick Edit HTML Source:

Somewhere on the page add the following:

<style>
.s4-recentchanges{
	display:none;
}
</style>
You can also do this in a content editor web part using the same Edit HTML Source option.

If you don’t hide this thing, I would suggest editing the master page to at least move that contentplaceholder below the quicklaunch so your navigation doesn’t get all wonky or at least displaced by a relatively unused feature.

Make Your Cisco IP Phone Ring Using .NET

Applies To: C#, VB.NET, Cisco Phones

I often get interrupted during the day. This is irritating but a part of office life and you get used to it. What I can’t seem to get used to, however, is hearing the same 3 hour story about my coworker’s dog’s stranger anxiety and all the mundane solutions they tried in order to fix poor Rover and even though that veterinarian is a “sweetheart” they just don’t know what they’re talking about sometimes blah blah blah – EVERY SINGLE DAY OF MY LIFE. I often find myself in conversations I neither started nor encouraged to continue that have long since passed the polite listening timeout.

Generally a good strategy is to get a friendly coworker to come and rescue you. Unfortunately, they may not always be around or may not have noticed. Another option is to fake a call. If you’ve got a Cisco IP Phone sitting on your desk and don’t mind writing a little code, you can have a handy app in just a few minutes that can send disarm the Chatinators*. Even if you are able to fully function in society without the help of fake social cues, you might find it interesting what you can do with that phone on your desk.

Cisco IP Phones can accept a wide variety of commands and it’s worth taking a look at the documentation sometime. The basic idea, however, is to send the phone an HTTP Post with some XML. In this case we are going to use the ExecuteItem command with a URI. That URI will contain a Play command. Sound confusing? It is a little, but that’s why I’m going to provide the code for you to cut and paste.

To send a command using VB.NET, you can use this helper function:

    Private Function SendCommand(Address As String, Command As String, Username As String, Password As String) As String

        Dim ResponseXML As String = String.Empty

        Dim request As HttpWebRequest = WebRequest.Create(String.Format("http://{0}/CGI/Execute", Address))
        request.Timeout = 30 * 1000
        request.Method = "POST"
        request.Accept = "*/*"
        request.ContentType = "application/x-www-form-urlencoded"
        request.Credentials = New NetworkCredential(Username, Password)
        request.PreAuthenticate = True

        Dim bytes As Byte() = Encoding.UTF8.GetBytes(String.Format("XML={0}", HttpUtility.UrlEncode(Command)))
        Using outStream As Stream = request.GetRequestStream
            outStream.Write(bytes, 0, bytes.Length)
            outStream.Close()
        End Using

        Using response As WebResponse = request.GetResponse
            Using responseStream As Stream = response.GetResponseStream
                Using reader As New StreamReader(responseStream)
                    If reader IsNot Nothing Then
                        ResponseXML = reader.ReadToEnd
                        reader.Close()
                    End If
                    responseStream.Close()
                End Using
            End Using
            response.Close()
        End Using

        Return ResponseXML
    End Function

In line 5 we setup the HttpWebRequest object to send the POST to the phone. The URL that accepts the commands is either your phone’s IP Address or DNS entry followed by “/CGI/Execute“. To find your phone’s IP Address, press the settings button on the device. There should be a Phone Information section that will have your phone’s address. You may also see an entry for Host Name. This is the name of your phone and will often be the DNS entry for it. In my case it was the fully qualified version of this host name. So SEP#####.domain.com. If you are unsure, just use the IP Address and look at the response in Fiddler or something similar.

Lines 6-11 setup all the required properties to make this POST acceptable to the phone. Depending on your network settings, you’ll need to provide a username and password. This means writing programs that cause other people’s phones to ring or display funny pictures is going to be extra hard. For our phones, our AD accounts were all that was needed to authenticate with the phones. If you were given a website to configure your phone’s address book or speed dials, it’s going to be the same login information. The PreAuthenticate setting is not required, but does reduce the number of 401 challenge responses when sending multiple commands in succession.

We write out the body of the response in lines 13-17 using UTF8 and a URL Encoded XML String that starts with XML=. Finally we close the request and capture the phone’s response as XML and return it in lines 19-32.

Okay, so now we can send a command, but what does the command look like? A basic play command looks like this:

<CiscoIPPhoneExecute><ExecuteItem Priority="2" URL="Play:Classic1.raw" /></CiscoIPPhoneExecute>

It’s pretty straightforward XML. The ExecuteItem element has 2 attributes, Priority and URL. The Priority attribute can be set from 0 to 2:

  • 0 = Execute Immediately (The command takes priority over anything else the phone might be doing)
  • 1 = Execute When Idle (The command waits until the phone isn’t busy before executing)
  • 2 = Execute If Idle (The command executes if the phone isn’t busy, otherwise it’s ignored)

For a fake ring program, priority 2 is best. That way you don’t get any extra ringing if someone actually is trying to call you.

The second attribute, URL, can take an actual URL to more commands or a simple URI depending on what your phone accepts. More information can be found in that documentation I mentioned, but for what we’re doing a simple Play followed by a colon and the name of the ringtone file takes care of things.

So now you’ve got the command and a send command function. You can write whatever fancy code you want to wrap these things up. I’ve written a little taskbar app that listens for a global key press and sends rings in a configurable loop to the phone. This allows me to secretly reach for the keyboard while the talker is distracted. Most of that’s beyond this article, but I will show you my Ring method and let you fill in the blanks:

    Private Sub Ring()
        If String.IsNullOrEmpty(My.Settings.PhoneIP) Then
            ShowSettings()
        Else
            Try
                For i As Integer = 0 To My.Settings.RingRepeat - 1
                    SendCommand(My.Settings.PhoneIP, String.Format("<CiscoIPPhoneExecute><ExecuteItem Priority=""2"" URL=""Play:{0}"" /></CiscoIPPhoneExecute>", My.Settings.RingTone), My.Settings.Username, My.Settings.Password)
                    If i < My.Settings.RingRepeat - 1 Then Threading.Thread.Sleep(3000)
                Next

            Catch wex As WebException
                MsgBox("Error when talking to the phone, please check your settings!" & vbCrLf & "(Probably your credentials)" & vbCrLf & vbCrLf & wex.ToString, MsgBoxStyle.Critical, "No Ring Ring :(")
                ShowSettings()
            Catch ex As Exception
                MsgBox("Error when talking to the phone, please check your settings!" & vbCrLf & vbCrLf & ex.ToString, MsgBoxStyle.Critical, "No Ring Ring :(")
                ShowSettings()
            End Try
        End If
    End Sub

The ShowSettings method is just a helper method that instantiates a Windows Form to allow some configuration. You can do something similar or just hardcode everything. Lines 6-9 are the important lines, everything else is just error handling with the assumption that the settings are wrong.

In a loop corresponding to the number of rings we want, I call line 7. This is just our SendCommand function from above. Then I wait 3 seconds and do it again.

That should get you started. Pretty soon you’ll be interrupting Talkaholics with ease. There are actually several really cool things you can do with your phone and the SendCommand function above should help you get going.

One last thing, I did a bunch of guess work with the names of the ringtones in my phone. These are configured by your administrator and may be totally different for you, but here are the ringtone filenames I found worked for me:

  • AreYouThere.raw
  • Analog1.raw
  • Analog2.raw
  • Bass.raw
  • Chime.raw
  • CiscoStandard.raw
  • CiscoSymphonic.raw
  • CiscoTechno.raw
  • Classic1.raw
  • Classic2.raw
  • ClockShop.raw
  • Drums1.raw
  • Drums2.raw
  • FilmScore.raw
  • HarpSynth.raw
  • Jamaica.raw
  • KotoEffect.raw
  • MusicBox.raw
  • Piano1.raw
  • Pop.raw
  • Pulse1.raw
  • Sax1.raw
  • Sax2.raw
  • Vibe.raw

I should note that for whatever reason sending Piano2.raw crashed my entire phone. Also, just for fun, you can take a screenshot of your phone by using the following address in your browser: http://YOURPHONEIP/CGI/Screenshot

*Chatinators © 2012 (and for all time), Chris Kent

Merging Data into a SharePoint list using SSIS

Applies To: SharePoint, SSIS, SQL

I was recently tasked with creating a one way sync between some data in SQL and a SharePoint list. This can be done through an External Content Type and External Content List and works pretty well. You can find instructions for setting this up all over Google. I found some and set it up pretty quickly. Unfortunately, it wasn’t quite what they or I wanted. The interface was kinda clunky and caused some complications in terms of using the list as a lookup (specifically in a site column).

So, I did some more searching and found the SSIS SharePoint List Source and Destination Adapters available on Codeplex. The install is super simple (Needs to be done on both your development machine and the SSIS Server) and following the readme included you can get up to speed pretty quick. Assuming you have Business Intelligence Studio installed, it’s a simple matter of right-clicking on the toolbox and selecting Choose Items. In the SSIS Data Flow Components tab scroll down and add the SharePoint Source and Destination components (again more details can be found in the readme).

So… How do you make these work? I’m certainly no expert, but I have gotten them to work and it’s solved my problem. I ran into some issues that you can hopefully avoid by following my small example.

My goal was to take a table from SQL (could also be a view or a simple SELECT statement) and to have that be written to a SharePoint list on a daily basis. However, I am using the SharePoint list for lookups. This means that I want to be able to add any new rows, update existing rows and delete missing rows without changing the SharePoint IDs. This can be done, but it’s not as straightforward as you might hope.

Continue reading

Intermittent “Unable to display this Web Part” messages

Applies To: SharePoint 2010

I few months ago I customized a view in SharePoint designer to turn the due date red for any past due items in the list. The end users really liked this but an obnoxious problem started turning up. Seemingly randomly we would get:

Unable to display this Web Part. To troubleshoot the problem, open this Web page in a Microsoft SharePoint Foundation-compatible HTML editor such as Microsoft SharePoint Designer. If the problem persists, contact your Web server administrator.

Correlation ID: Some GUID

Taking a look through our logs didn’t reveal anything and often a refresh or two would solve the problem. So it wasn’t really stopping business but it was pretty annoying. Adjusting the logging settings we finally saw some messages corresponding to the provided Correlation ID and found the issue was Value did not fall into expected range often followed by Stack Overflow exceptions.

Unfortunately the above error message is so generic it was pretty difficult to find anyone else even having the same problem, let alone the solution. Finally I came across this thread on MSDN discussing the exact issue. Instructions for fixing the problem and the background of this issue can be found on this article on Englando’s Blog. The solution presented was to get a hotfix from Microsoft. Fortunately, that is no longer necessary and the fix is provided in the February 2012 Cumulative Update from Microsoft.

The problem was introduced in the June 2011 Cumulative Update when Microsoft reduced the timeout for XSLT transformation (used whenever you customize a view in SharePoint Designer) from 5 seconds to 1 second. This is a good idea for public facing farms to help mitigate Denial of Service attacks but pretty unnecessary for internal farms like the one I was working on.

The timeout causes modified XSLTListView Web Parts and XSLTDataView Web Parts to sometimes show the “Unable to display this Web Part” errors. This is especially true if you have several columns (more transformation) or are doing anything of even mild complexity. The issue was “fixed” in the August 2011 Cumulative Update but broken again in the December 2011 Cumulative Update.

To fix this issue we installed the February 2012 Cumulative Update on our farm (More about our experiences with this update to follow). Keep in mind, however, that the update does not change the XsltTransformTimeOut but merely provides you the ability to do so using PowerShell.

To check your current timeout settings, simply use the following PowerShell:

$myfarm = Get-SPFarm
$myfarm.XsltTransformTimeOut

If you’re experiencing the above problem, you probably got a 1 back from the above command indicating that the timeout is currently set to 1 second. To set it to a more reasonable value (we choose the original 5 seconds) just do this (assuming you set the $myfarm object using the above powershell):

$myfarm.XsltTransformTimeOut = 5
$myfarm.Update()

That’s it, things are happy again.