A problem was recently posted on one of our SharePoint Solutions Help Community Forums. The situation was that an individual wanted to “roll up” multiple discussion boards from multiple sub-sites; essentially pulling the content from different discussion board lists.

One solution is to upgrade to SharePoint 2013 and take advantage of all the new social features such as Communities. Another option is to buy a third-party web part such as this one. Neither of these may be possible in your organization.

In this post, I will present a solution that involves modifying the styles available for presentation within the Content Query Web Part.

The content query web part can roll-up information from multiple sites, based on Content Type. Unfortunately, discussion boards in 2010 are not like most lists. Only the first post in each thread has a “Title.” That first post is the Discussion Content Type, which is actually a folder with metadata. All the replies are Message Content Types within that folder. This means that if you don’t limit the content query to only folder-type content types then you receive many, many items that display “(blank)” for a placeholder for the missing Title field. Another problem is that each of the links will take you to a page with just that single message instead of showing the entire thread. In this age of Facebook and ongoing social dialogue, this setup doesn’t work for most people.

Begin by editing a page, inserting a web part, selecting “content rollup”, and choosing content query.

Edit the web part and set it to query all Discussion Board “Folder Content Types” group from the parent site of the Discussion Boards you want to roll up. In the example below, my discussion boards were in separate subsites of the Production site.

Initially, the Content Query web part looks like this.

Clicking on any of these links take the user to a single page displaying only that message:

Clicking through obviously doesn’t show the whole thread and clicking “close” takes the user back to the page with the query. This is not what most users are expecting.

This is an example of a style. A style is simply a markup template that is applied to the information you give it. The good news is that styles can be added to or modified. Each style is pulled from a specific file on your SharePoint installation, ItemStyles.xsl. So one way to make this look better and act better is to modify those styles. A familiarity with the basics of computer languages and/or markup languages comes in very handy, as I found out. The location of these styles is found in a folder on your top level site in a site collection. If you open up your site with SharePoint Designer and choose the topic of “All Files” in the left-side menu then you will be able to browse to the folder “Style Library” –> “XSL Style Sheets”. In this folder there is a file called “ItemStyle.xsl.” You can edit this item from within Designer. I added the following into this file right before the last /xsl:template tag. Because this is a markup language it gets swallowed up by web browsers in its correct syntax so I couldn’t copy and paste the text in this blog. Here’s an image of the correct lines.

This inserts a new style into the ItemStyle.xsl using the XSL standards. I save the document and return to SharePoint. Now I can choose the style I just created when editing the web part.

So now when I select the “CustomRollup” style I get this:

Not too impressive until we click on one and receive this:

Now we get links that take us to the full discussion with the default view for that discussion, whether flat or threaded. Rather than a “close” button, the browser’s back button will return us to the appropriate page. You can add to the code in order to make it look better. I got a lot of help from W3schools where I learned that XSL was simpler than I thought and from Stackoverflow.com for specific help on getting a hyperlink displayed properly. This instruction set makes much better and more advanced use of XSL to provide an experience in styles.

There are limitations and problems with this solution (just like any other) in that if Microsoft ever chooses to update the file, your custom changes will probably be overwritten without notice. I will continue to play with this and see if I can make it better looking, but I hope this helps you get started on aggregating your discussion boards for a better user experience in SharePoint 2010.

It used to take months for highly specialized IT professionals to gather, assimilate and share Business Intelligence data. Now that process can be done in days by business professionals – if you have the right tools.

Join us March 21 at 11 a.m. EDT for our new webinar “What is Microsoft’s Self-Service BI Toolset?”

If you’re looking for a way to quickly and efficiently mine important data your organization’s decision makers need, you’ll want to attend this informative webinar, which is hosted by Jeff Cate, president of SharePoint Solutions and author/instructor of the course Business Intelligence Training – the Microsoft Self-Service BI Toolset.

Jeff will give an overview of the evolution of Business Intelligence and a description of Microsoft’s BI Tools:

• PowerPivot

• Power View

• SQL Report Builder and

• SharePoint

Click here to register, and discover how having the right BI tools can lead to better-informed business decisions.

The ostensible purpose of the Calendar Overlay feature in SharePoint 2010 is to aggregate multiple calendars into one master, or roll-up calendar. The appointments in each calendar can display in an assigned color, allowing for a quick visual impression of which appointments belong on which sub-calendar.

In my previous blog post, I described how the SharePoint 2010 Calendar Overlay feature could be used to color-code individual appointments in a single calendar. The trick is to create views of types of appointments (based on perhaps a custom metadata column you might add to the Calendar). The Calendar Overlay can then be used to assign a color to each view. Then, each appointment in that view will have the assigned color.

This post shows another feature of Calendar Overlay: Showing items that are not on a calendar. In our example, the IT department has the default Team Site Calendar that they use for general items. In addition, they would like to include on this calendar the anticipated end date for their major projects, and the review date for the various contracts they have with vendors. This information is already captured in a Project List and a Contracts and Agreements Library. Both the Library and the List have a column for a date. Using these I can (a) create a view for each one that is a calendar, then (b) use Calendar Overlay to roll those dates up into a master Team calendar.

The first step is to create Calendar views of the non-calendar lists / libraries. Below is the Contracts and Agreements Library in the typical list format. Each document has a Review Date; that will be the basis for the Calendar view.

1. Go the Library ribbon and click the Create View button.
2. In the Create View page, click the Calendar View link.
3. Type in a Name for the view, such as Review Date Calendar.
4. We have only one relevant date column in this Library, so we’ll choose Renewal Date in both the Begin and End fields in the Time Interval section. Use the document Name as the item heading in the Month, Week, and Day views.
   
5. Add any other needed settings and click OK.

This gives us a view of the Library in a Calendar grid, based on the Review Date.

Here is the list of Projects that the IT department is currently working on.

Following the same steps outlined above with the Library, create a Calendar view for the list, using the Completion Date as both the Begin and End date for the Time Interval. (We want to show only the Completion Date on the Calendar, not the entire span of the project from start to finish.)

The resulting Calendar view looks like this:

Now that we have Calendar views of the Library and the Project List, we can use Calendar Overlay to roll them up into the department’s main SharePoint Calendar, shown here:

Following the steps outlined in the previous post on Calendar Overlay, add the Library and Project List to the Calendar. Briefly:

1. In the target Calendar, click the Calendars
   Overlay button in the Calendar ribbon.
   
2. Click the New Calendar link and add the relevant information for the Contracts and Agreements Library.
   
3. Repeat to add the Projects List.
   

The main IT Calendar now includes not only the events placed directly on that calendar, but the dates from the Library and Project List as well.

One of the most common requests I received as a SharePoint administrator was to color-code Events. Users are accustomed to assigning colors to appointments in Outlook and other calendaring applications, so it’s natural to want that type visual organization in SharePoint as well. Unfortunately, in SharePoint 2007 and earlier, there was no built-in solution. It was such a common need that many administrators, developers, and power-users devised solutions using various types of code. These required the know-how to develop (or the know-how to research and try to adapt to your situation), and most end-users would not be comfortable with trying such a solution.

The good news is that SharePoint 2010 includes functionality that can be leveraged to achieve this effect, with no code needed. It may not be a completely obvious solution when working with a single Calendar, though. The secret lies in the use of the Calendar Overlay. Typically this feature is used to roll up multiple Calendars (or lists with Calendar Views) into one master Calendar. Links to the sub-Calendars are included in the Calendar Overlay definition, a color is assigned to each sub-Calendar, and the user can easily understand which Events belong to which sub-Calendar. We teach this feature in detail in our two introductory SharePoint 2010 classes: Introduction to SharePoint 2010 – Using SharePoint Foundation 2010 and Introduction to SharePoint 2010 – Using SharePoint Server 2010.

What if I have Events on a single Calendar, but want to assign different colors to different types of Events, however? I might want meetings to be one color, deadlines another color, informational Events a third, HR Events a fourth, and so on. Colors cannot be applied ad hoc, but if I have the Events flagged in such a way that I can create a filtered View of those Events, I can then assign a color to those types of Events in a master View.

Add Metadata

The first step is to set up your Calendar with the appropriate metadata Column or Columns so that each event may be categorized. The Calendar has a built-in Category Column; you may wish to use this, or use it with modifications. You might also set up your own Column. For example, you might wish to categorize items on a Calendar based on the department or workgroup it belongs to. For the example below, though, I will modify the default Category Column.

1. On the page for the Calendar you wish to work with, click the Calendar tab, then click List Settings.
   
   
2. In List Settings, click the Category Column.
   
3. Choose Yes for Require that this column contains information. Update the choices under Additional Column Settings, and change the setting for Allow Fill-in Choices to No
   
   
4. Click OK.

Once the Column has the desired options, update the existing Events to include the correct Category. (If you have many Events to edit, you may want to add the Category Column to the All Events View, then use Datasheet View to quickly edit each item.)

Any new Events will be categorized as they are created.

Create Views

1. In the Calendar tab, click Create View.
   
2. You want to copy the default Calendar View and make a small change to it to add a filter. On the Create View page, click Calendar under Start with an existing view.
   
3. On the Create Calendar View page, type a name in the View Name field (i.e., Meeting Events).
   
4. In the Filter section, add settings to show only Events where the Category is Meeting. (Be careful to type the criteria exactly as it appears in the Column, including the correct case.)
   
   
5. Click OK.

Create Calendars Overlay

1. On the Calendar tab, click the Calendars Overlay button.
   
2. On the Calendar Overlay
   Settings page, add the Calendars (or in this case the Views of this Calendar) you wish to see by clicking the New Calendar link.
   
3. On the New Calendar page, type in a name for the Calendar (in this case, a View of the same calendar).
   
4. Choose a color for this Calendar View. (The colors available will depend on the theme or color scheme for the site as a whole.)
   
5. Next, SharePoint needs to know the URL of the site (or subsite) for the Calendar. By default, it fills in the URL of the subsite you are working with. In this case, that is correct. If you were trying to bring in a Calendar from another subsite, you would type or paste in that URL. With the correct URL in place, click the Resolve button.
   
6. When the URL resolves, the List field just below will be populated. From this drop-down, choose the Calendar you are working with. There may be only one selection if there is only one calendar on this subsite.
   
7. Next, choose the specific View you wish to use in the List View field.
   
8. Click OK.
9. You will be returned to the Calendar Overlay Settings page, where you’ll see the calendar addition.
10. Repeat these steps to add the Calendar Views for HR, Social, and Training, choosing a different color for each.
    
11. Return to the Calendar. Now each type of Event has its own color. In addition, you can use the buttons on the left to show only one type of Event at a time. There’s a lingering problem, though … each Event is listed twice, since the Calendars Overlay layers the other Calendars/Views on top of the Events that already show in the normal Calendar View. Therefore, we’ll need to tweak the normal Calendar View to hide the duplicates.
    
12. In our Calendar, every item will have a Category; that Column will not be blank in any instance. Therefore, it will be easy to hide the Events in the default View with a Filter that is looking only for uncategorized items. To do this, go to the View selector in the navigation breadcrumb and choose Modify this View.
    
13. In the Filter section, make selections to build a filter where the Category – is equal to – [blank].
    
14. Click OK.
15. You will be returned to the Calendar with the Calendar View and the Calendars Overlay active. Since there are no Events in the normal Calendar View that have a blank Category, all items are hidden, but the overlaid views for each Category are visible with the color coding.
    

The Content Query Web Part is a very handy tool in SharePoint Standard and SharePoint Enterprise. The web part has a number of uses. For one, it can allow you to create an aggregated list based on multiple lists or libraries, either from within a single site or from across a site collection, as we show in our course Introduction to SharePoint Server 2010. For another, it can also be used to display items from a single list or library on a different site within a site collection. For example, you might have a master Contact list on the top-level site in a site collection, but need to display a filtered version of that list on a sub site through the Content Query Web Part.

You can add the Content Query Web Part (often called CQWP for short) to a SharePoint page by going into Edit mode (Site Actions > Edit Page), then click the Insert tab and choose Web Part. Click the Content Rollup folder and select Content Query Web Part.

This blog post isn’t intended as a description of how to use the CQWP, but to point out a SharePoint site collection administration detail that sometimes causes a problem. That problem is that frequently a user will go to insert the CQWP and find that it’s missing. They are sure they are in the Standard or Enterprise edition of SharePoint (as opposed to Foundation), but the web part simply isn’t there. Why? And how do you make it available?

The answer lies in Site Collection Features. The Features are a series of options in SharePoint that can be turned on or off. Some are on by default when a site collection is created; others have to be activated before they can be used. Be aware that sometimes the name and description of a feature doesn’t tell you everything that the feature controls. In this case, the CQWP is part of a bundle of features called SharePoint Server Publishing Infrastructure. Generally we think of Publishing in SharePoint as a way to enable content management, including combining content with specific page layouts. It’s a feature commonly used by public web sites meant to provide large amounts of information (or frequently changing information) in formats similar to a newspaper or catalog. Activation of the Publishing Infrastructure is necessary, however, to make the CQWP (and certain other web parts) available for use.

A user with Full Control or Site Collection Administrator permissions can take the following steps to activate the Publishing Infrastructure:

1. Go to Site Actions > Site Settings
2. In the Site Collection Administration section, click Site collection features
   
   
3. On the Site Collection Features page, click the Activate button next to SharePoint Server Publishing Infrastructure.

Once this feature has been activated, the Content Query Web Part should then be available throughout the Site Collection.

NOTE: In addition to Site Collection Features that may be activated or deactivated, there are Features that may be turned on or off on each site; this includes the top-level site. If you want to use the full set of Publishing tools, you must also activate the SharePoint Server Publishing site feature on each site/subsite where you wish to use them.

1. Go to Site Actions > Site Settings
2. In the Site Actions section, click Manage site features
   
3. On the Site Features page, click Activate for SharePoint Server Publishing.

Keep this tip in mind whenever you can’t find a tool or feature in SharePoint – it might just be part of a Site Collection Feature or Site Feature that needs to be activated. In addition, there are some features that are enabled at the server level, so you may need to contact your server administrator if you can’t find it in the Site Collection or Site lists.

In our Introduction to SharePoint Foundation / Server 2010 course, we briefly mention that SharePoint limits the total URL length of any location in a site: Depending on some variables, it can be up to 256 to 260 characters. (See http://technet.microsoft.com/en-us/library/ff919564.aspx for a technical explanation.) In addition, you’ll run across a 255-character limit for URLs in lots of other places. For example, if you want to paste a link into an email, your email program may not accept URLs longer than 255 characters. In some SharePoint sites it can be surprisingly easy to hit that character limit. Some of the things that can eat up URL space are:

• Long names for lists or libraries
• Use of folders and subfolders
• Long names for documents or list items
• Multiple levels of subsites

In this post I’ll discuss how to help keep that length under control by smart management of lists and libraries.

Creating Lists and Libraries

First, let’s look at what happens when you create a List or Library. (In the example below, we’ll be working with a Library, but the same principles apply when working with a List.) When you open the Create dialog box, the first field that you fill in is Name. Whatever you type in as the name becomes part of the URL for that List or Library. Browsers cannot understand certain special characters, though, so they can’t be used in URLs. Spaces are one such special character, so SharePoint replaces every space with %20 to encode it in a way that browsers will understand. For example, let’s say I need to create a Library for policies and procedures on the Human Resources subsite of an intranet Site Collection. I might be a bit verbose on top of that, so I name my new Library Policy and Procedure Documentation. SharePoint uses the Name as both the display name (for the Library page, the Quick Launch Bar, etc.) and the library URL. In the URL, the %20 code is used instead of the spaces. So, I end up with something like http://intranet/sites/humanresources/Policy%20and%20Procedure%20Documentation. In addition to being long, the URL is also not very user-friendly – the space encoding makes it difficult to “read.”

So, how do we create Lists and Libraries in such a way that the URL will be shorter and more user-friendly, while also having as descriptive a display name as possible? The trick lies in creating the List or Library with a brief name first, then going back and updating the display name. For example, in my Human Resources subsite I’ll go to Site Actions > New Document Library to open the Create dialog box. Instead of naming it Policy and Procedure Documentation, I’ll name it policies. If I really wanted to include “procedures” in the URL, I might do something like policies_procedures, substituting an underscore for a space.

(Note that I also turned on Versioning for this library; more about that later.)

After I click Create in the dialog box, I’ll have a library that looks like the example below. The URL includes policies, and that’s also the display name in four locations: The descriptive text below the column headings, the breadcrumb navigation at the top of the page, the link in the Quick Launch Bar, and the browser tab (page title):

Since I want that display name to be more formal and descriptive, now I’ll go into the Library Settings to modify it. I’ll click on the Library

tab under Library Tools, then click on the Library Settings button.

In Library Settings I’ll click Title, description, and navigation under the General Settings heading.

On the next page, I’ll see that the current Display Name is policies. I’ll change that to my preferred Display Name, Policies and Procedures Documentation, and click Save.

before:

after:

Returning to the Library, I see that my display names have updated, but the short, user-friendly URL hasn’t changed.

Folders

When creating folders, not only do you have to be aware that a folder just creates another level in the URL path, but you are also limited to no more than 128 characters when creating a folder name. As we discuss in the Introduction to SharePoint 2010 Foundation / Server class, the best practice in SharePoint is to not use folders at all, but to use metadata (columns) to categorize items. By using this flat structure with metadata, you can categorize items in a number of ways instead of just one way as you would with a folder. In addition, you can then create views to display the items according to a wide range of sorting, filtering, and grouping criteria. These metadata terms are also available as Search criteria. There are times when a folder is truly necessary, such as when a group of items has a unique permissions requirement. Otherwise, however, use metadata.

For example, in our Policies and Procedures Library, we might have had one folder for Policies and one for Procedures. Instead, I’ve set up the library with multiple metadata Columns so that each document may be flagged in a number of ways by going to Library

tab > Library Settings button > Create Column link.

For detailed information about using metadata and views, see our free online video SharePoint 101 – What Makes SharePoint Work.

Item Names

It is also helpful to refrain from having document names that are very long. In the example above, there is a document called Employee Dress Code Policy – Revision 06-2012. SharePoint converts spaces in names to %20 for the purposes of a URL, since browsers can’t understand some special characters like spaces. So, the URL for that document would be http://intranet/sites/humanresources/policies/Employee%20Dress%20Code%20Policy%20–%20Revised%2006-2012. Smart document naming and management habits can keep that name shorter and more intelligible. First, the word “policy” could be dropped, since the Category flag already indicates that it’s a Policy. It could also be argued that the word “Employee” could be removed, since it is implied that the policy applies to the company employees. Next, the revision information can be removed. For one thing, each item has a Modified date/time stamp, so the last revision date is easy to view. In addition, by turning on Versioning as we did when we created the library, we can avoid the phenomenon so common to shared drives: Multiple copies of a document are stored, each with a different revision date. All of the revisions are tracked in this single document and can be viewed, or even restored, at any time.

Bonus Tip: Subsites

When you create a subsite, you have an advantage that you don’t have when creating a List or Library: Separate fields for specifying the name and the URL. You might have a site name that’s spelled out (Human Resources) but you are not stuck with that as the URL. In the example above, it makes sense to use the common acronym for Human Resources, HR. So instead of http://intranet/humanresources, I can use http://intranet/hr. Just be sure that if you use acronyms or other shortened names for site URL paths, that it’s something that will be reasonably intelligible to the users.

By combining best practices for naming, document management, and List / Library management, you can keep your URLs to a reasonable size as well as improve the functionality and ease of use for the site as a whole.

In the module on Meeting Workspaces in our Introduction to SharePoint 2010 – Using SharePoint Foundation 2010 class, we teach students that there are three ways to create a new Meeting Workspace:

• From any site, just like you would create any other site (Site Actions > New Site or View All Site Content > Create).
• From any Event on a SharePoint Calendar (check the box beside “Use a Meeting Workspace to organize attendees, agendas, documents, minutes, and other details for this event.”)
• From any Appointment or Meeting in Outlook (click on the “Meeting Workspace” button in the ribbon while editing or creating the Appointment or Meeting).

When I show students the Meeting Workspace button in Outlook, some students have seen it and some have never seen it. In Outlook 2007, the button was on the Meeting ribbon by default in the Attendees section, as you can see in this screenshot.

Unfortunately, for some unknown reason, the Outlook team decided to remove the Meeting Workspace button as a default in Outlook 2010.

However, it’s easy to add it if you want it. J Here are the steps:

1. In Outlook 2010, go the Calendar and click the button to create a New Appointment or New Meeting.
   
2. In the Appointment or Meeting dialog, click File > Options to access the Backstage.
   
3. In the Outlook Options dialog, select Customize Ribbon on the left-hand side.
   
4. In the right-hand side of the Customize the Ribbon panel, click the New Tab button. Unfortunately, it’s not possible to add new buttons to the existing tabs and groups, so you’re going to have to create a new tab to show the Meeting Workspace button.
   
5. Select the New Tab (Custom) that was just created, and click the Rename… button.
   
6. In the Rename
   dialog, for the Display name, type SharePoint, and click the OK button.
   
7. Select the New Group (Custom), and click the Rename button.
   
8. In the Rename
   dialog, for the Display name, type Meeting Workspace, and click the OK button.
   
9. In the left-hand side of the Customize the Ribbon panel, select Popular Commands > Meeting Workspace. In the right-hand side select Meeting Workspace (Custom). In the middle, click Add. At the bottom, click the OK button.
   
10. Notice you have a new SharePoint tab in the ribbon, and a Meeting Workspace button on that tab. It has been added to both the Appointment and Meeting dialogs.
    
11. Enjoy using your new Meeting Workspace button to create Meeting Workspaces in SharePoint right from within Outlook as you are scheduling a new meeting.

Hi, and welcome to my first blog post for SharePoint Solutions. I’m looking forward to meeting more of our students and clients in the coming weeks, but I thought I’d jump in and introduce myself through the blog. Today I want to talk about a basic topic that is nonetheless a little puzzling at first glance – the difference between the Blank Site template and the Team Site template in SharePoint 2010.

You might be saying to yourself, “Well, that’s obvious; the Blank Site is empty, while the Team Site has some features already built out of the box.” That’s true, but the difference goes a little deeper; it can be a significant factor in which template you choose when creating a site collection or subsite.

As you probably know, SharePoint comes with a number of site templates available; the number depends on which edition of SharePoint you are running, but all editions have both the Blank template and the Team template. These templates also existed in previous versions of SharePoint. In the 2007 version and earlier, the Blank and Team templates were identical except for the fact that the Team template had a library and several lists already created and in place. In SharePoint 2010, however, that difference goes deeper.

One of the things we discuss in the class Introduction to SharePoint 2010 – Using SharePoint Foundation 2010 is the existence of three types of web pages in SharePoint: System pages, web part pages, and wiki pages. (In the course Introduction to SharePoint 2010 – Using SharePoint Server 2010 we also use a fourth type, publishing pages.)
In previous versions of SharePoint, the home page for all site templates was a web part page. You edited the home page by adding (or removing) web parts, including perhaps a Content Editor Web Part if you wanted a block of text, or an Image Web Part if you wanted a graphic. In SharePoint 2010, however, the home page of most web sites is a wiki page, and it is stored in a new type of library called Site Pages. These sites also have a library called Site Assets, where files like images for the wiki pages are stored.

The Team Site template is one of the templates that uses the wiki page as a home page. Because of this, you can easily edit the page right in the browser. When you go into Edit mode, you can delete the default text and add new text, just as you would in a word processor. You can add images, tables, and other elements. You also have a number of options for formatting text and changing the page layout, all with tools that appear in the contextual ribbon. This is a WYSIWYG interface that we are all used to in the modern computing world – what you see is what you get, and you can touch and change what you see directly.

In the Blank Site template, though, the home page functions like home pages in previous versions of SharePoint. You must use Web Parts for adding any content, including text and images. On a Blank site, the Site Pages and Site Assets libraries don’t exist. If you want a wiki page for the home page (or any other site content), you have to create the two libraries and the page from scratch, then create a new wiki page and flag it as the new default for the site if desired. Even if you don’t need to change the type of page for the home page, you still have to create the Site Pages and Site Assets libraries (or equivalents) if you want to create any other wiki pages for the site, because pages need to be stored in a library.

So, when should you choose the Blank Site template, and when should you choose the Team Site? My general rule of thumb is that I usually use the Blank Site only if I know that none of the usual Team lists are going to be needed (i.e., Calendar, Announcements) and that having a web part page instead of a wiki page will be acceptable. In these cases the content of the site is often planned to be highly customized by an experienced site owner or developer. Otherwise, I’ll just start with a Team site and just delete or hide those lists and libraries that won’t be used. That’s easier and faster than starting with the Blank Site and building out the wiki pages and associated library if they are desired.

In my role as a SharePoint administrator and consultant, I usually created sites for others to manage. Many of these site owners were new to SharePoint, or at least new to managing a SharePoint site. It was helpful for them to have some elements already in place so they could start to see the range of tools that SharePoint has. Being able to edit the home page without learning the intricacies of Content Editor Web Parts and Image Web Parts was helpful, too. This greatly reduced the learning curve for them and helped them get up and running more quickly.

Two years ago I wrote a blog post about How to Create a Detailed Custom Task Notification with a SharePoint Designer Workflow. That blog post provided step-by-step instructions and screenshots for doing this in SharePoint 2007. I received a lot of questions asking for more information explaining the steps. Back in December I finally decided to record a video of showing how to do this same thing in SharePoint 2010. However, I got tied up with other work and just now realized that I had never posted that video here on the blog. Oh well, better late than never, I guess.

I think you’ll find that I explain things in much more detail in this video and you get to see all the steps that are involved as well. As always, if you have questions, let me know in the comments and I’ll do my best to answer them as time allows.

Click here to watch the video on YouTube.

Wow! I never expected to write so many posts about Column Validation. Yet, here I am with the 5th post in what has become a series about Column Validation. Just to recap, here are the links to my previous posts on Column Validation:

•  How to Validate a Due Date in a SharePoint 2010 List
•  How to Validate Strict Text Formats in SharePoint 2010
•  How to Validate an Email Address Using Column Validation in SharePoint 2010
• How to Use ‘OR’ Type Options in SharePoint 2010 Column Validation

Also, if you would like to learn more about these techniques, with live demonstration and discussion, I’ll be speaking on Column Validation at July. 2012 meeting of the Federal SharePoint User’s Group (FEDSPUG) in Washington, DC. Come on out, I would love to meet you!

The Problem

A reader asked, “I have form that does not require the ‘phone’ to be filled in. However I want to validate the entry if/when it is filled in. Is there a way to add string at end of the phone validation formula to accept a ‘null’ value to avoid validation error?” Yes, this is possible, and in this post I’ll show you how to do it.

In a nutshell, this is an ‘OR’ type of validation that I discussed in my post on How to Use ‘OR’ Type Options in SharePoint 2010 Column Validation, with one of the options being no answer at all. In this post, I’ll look at a slightly different way of doing this than the exact way I did it in that post. The are multiple formulas that may work for many of these complex validation requirements, so there isn’t one single correct answer.

A Simple Non-Required Validation Test

Because some of the formulas for Column Validation can be quite complex, I like to set up a “proof of concept” type validation that is more simple first, then I can work on the more complex details.

So, I’ll first test the concept of validating something only if it exists. Then, once I have that working, I’ll integrate the formula I’ve already written for validating a phone number.

I’ve created a list with a column named “phone”. I’ve chose the “Single line of text” data type, set it to a maximum of 12-characters (three digits, a dash, 3 digits, a dash, and four digits), and made it not required.

Since I only want my column validation to run if the user enters something in the field, that is to say, if the field’s value isn’t NULL, my first thought was to use the ISNULL() function and then wrap it in the  NOT() function. (Note: Microsoft has a section with information about available Formulas and Functions in SharePoint 2010.) This method should work fine on Number columns, however, a column of data type Single line of text isn’t NULL if it’s empty, it just has a string of no length. This moved me back in the direction of using nested IF() functions like I did in my post on How to Use ‘OR’ Type Options in SharePoint 2010 Column Validation.

First, we’ll write a simple formula to only allow a blank phone number. This formula will look like this: =IF(LEN([Phone])=0,TRUE,FALSE) With this formula in place, if there is anything in the Phone field, it won’t validate. However, it validates fine if the field is empty.

Next, we’ll put a simple validation place of the FALSE result. Let’s try to only allow the letter “A” there. So a user can leave it blank, or they can enter a single letter “A”, but nothing else will be allowed. That formula will look like this: =IF(LEN([Phone])=0,TRUE,(IF([Phone]=”A”,TRUE,FALSE))

With this formula, only empty values, or values of a single letter “A” are allowed:

Validating a Phone Number, But Only If One Is Entered

Now that we have the general logic worked out, we need to replace that second condition ([Phone]=”A”) in the formula with the Phone number validation formula. You’ll remember from the post on How to Validate Strict Text Formats in SharePoint 2010 to validate for ###-###-####, the formula is:
=(LEN([Phone])=12)
+(CODE(MID([Phone],1,1))>47)
+(CODE(MID([Phone],1,1))<58)
+(CODE(MID([Phone],2,1))>47)
+(CODE(MID([Phone],2,1))<58)
+(CODE(MID([Phone],3,1))>47)
+(CODE(MID([Phone],3,1))<58)
+(MID([Phone],4,1)="-")
+(CODE(MID([Phone],5,1))>47)
+(CODE(MID([Phone],5,1))<58)
+(CODE(MID([Phone],6,1))>47)
+(CODE(MID([Phone],6,1))<58)
+(CODE(MID([Phone],7,1))>47)
+(CODE(MID([Phone],7,1))<58)
+(MID([Phone],8,1)="-")
+(CODE(MID([Phone],9,1))>47)
+(CODE(MID([Phone],9,1))<58)
+(CODE(MID([Phone],10,1))>47)
+(CODE(MID([Phone],10,1))<58)
+(CODE(MID([Phone],11,1))>47)
+(CODE(MID([Phone],11,1))<58)
+(CODE(MID([Phone],12,1))>47)
+(CODE(MID([Phone],12,1))<58)
=23

To make it easier to read, let’s separate the original formula so the parts are on separate lines. It looks like this:
=IF(
  LEN([Phone])=0
    ,TRUE
    ,(IF([Phone]=”A”
      ,TRUE
      ,FALSE
    )
)

If we replace the second condition with the Phone Number Validation Condition, it looks like this:

=IF(
  LEN([Phone])=0
    ,TRUE
    ,(IF(
(LEN([Phone])=12)
+(CODE(MID([Phone],1,1))>47)
+(CODE(MID([Phone],1,1))<58)
+(CODE(MID([Phone],2,1))>47)
+(CODE(MID([Phone],2,1))<58)
+(CODE(MID([Phone],3,1))>47)
+(CODE(MID([Phone],3,1))<58)
+(MID([Phone],4,1)="-")
+(CODE(MID([Phone],5,1))>47)
+(CODE(MID([Phone],5,1))<58)
+(CODE(MID([Phone],6,1))>47)
+(CODE(MID([Phone],6,1))<58)
+(CODE(MID([Phone],7,1))>47)
+(CODE(MID([Phone],7,1))<58)
+(MID([Phone],8,1)="-")
+(CODE(MID([Phone],9,1))>47)
+(CODE(MID([Phone],9,1))<58)
+(CODE(MID([Phone],10,1))>47)
+(CODE(MID([Phone],10,1))<58)
+(CODE(MID([Phone],11,1))>47)
+(CODE(MID([Phone],11,1))<58)
+(CODE(MID([Phone],12,1))>47)
+(CODE(MID([Phone],12,1))<58)
=23
      ,TRUE
      ,FALSE
    )
)

Although this does correctly validate and only allow phone numbers in the ###-###-#### format, it doesn’t display our nice friendly error message when a user enters in invalid information. It displays:

instead of:

As I discussed in the post on How to Use ‘OR’ Type Options in SharePoint 2010 Column Validation, this is because the formula is trying to evaluate characters that don’t exist. Let’s change our formula to only check the individual characters if it is 12-characters long. With this extra condition, our stub formula will look like this:

=IF(
  LEN([Phone])=0
    ,TRUE
    ,(IF(LEN([Phone])=12
      ,TRUE
      ,FALSE
    )
)

This stub formula will work and only allow blank phone numbers or phone numbers that are 12-digits long. To add the testing for the proper characters, we’ll need to wrap the phone number validation formula in an IF() statement and replace the second TRUE result with it. Because this second statement will only run if it’s 12-characters long, we don’t need to include that part of the formula and the sum will be 22 instead of 23. Our formula will look like this:

=IF(
  LEN([Phone])=0
    ,TRUE
    ,(IF(LEN([Phone])=12
      ,IF((
(CODE(MID([Phone],1,1))>47)
+(CODE(MID([Phone],1,1))<58)
+(CODE(MID([Phone],2,1))>47)
+(CODE(MID([Phone],2,1))<58)
+(CODE(MID([Phone],3,1))>47)
+(CODE(MID([Phone],3,1))<58)
+(MID([Phone],4,1)="-")
+(CODE(MID([Phone],5,1))>47)
+(CODE(MID([Phone],5,1))<58)
+(CODE(MID([Phone],6,1))>47)
+(CODE(MID([Phone],6,1))<58)
+(CODE(MID([Phone],7,1))>47)
+(CODE(MID([Phone],7,1))<58)
+(MID([Phone],8,1)="-")
+(CODE(MID([Phone],9,1))>47)
+(CODE(MID([Phone],9,1))<58)
+(CODE(MID([Phone],10,1))>47)
+(CODE(MID([Phone],10,1))<58)
+(CODE(MID([Phone],11,1))>47)
+(CODE(MID([Phone],11,1))<58)
+(CODE(MID([Phone],12,1))>47)
+(CODE(MID([Phone],12,1))<58)
=22
),TRUE,FALSE)
      ,FALSE
    )
)

Whew! That was a lot of work, but we did it. With that formula, our list doesn’t require a phone number to be entered, but if one is entered, it must be in the proper format.

I’m really enjoying working through these column validation questions and issues and look forward to hearing what others need to validate. Of course, hopefully, the series has been written so that you’ve learned to do this yourself and you’ll be sharing your formulas in the comments instead of asking for new ones.

Happy validating!