Site Column names with special characters: Errors in Calculated Columns

I ran into an interesting scenario when a colleague of mine was setting up a list in SharePoint 2010.  He was creating a list, with a phone number column, a phone extension column, and a calculated column for phone display.  Things were working fine, until he entered some data and the display column didn’t look right.

When he opened the settings for the phone display, he saw this in the formula: #NAME?-#NAME?After some tinkering, we got it to work by changing the name of the phone extension column.

I’ve looked into this further and here’s how to recreate it: (be sure to create these column as Site Columns)

  1. Create a Phone Number site column as a “single line of text” and name it “Phone Number
  2. Create a Phone Extension site column as a “single line of text”, and name it “4 – Digit Extension
  3. Create a Phone Display site column as a “calculated column”, with the formula: =[Phone Number] & “ x” & [4 – Digit Extension]
  4. Now, go back and look at the definition of the Phone Display column.  What do you see in the formula?

PhoneDisplayBroken

In order to get this idea to work, you’ll need to change the name of the “4 – Digit Extension” to “4DigitExtension”.

PhoneDisplayCorrect

Now, here’s another oddity: create these columns as List Columns instead of Site Columns . Turns out that this works!

I found Microsoft’s documentation on column names with special characters here: http://office.microsoft.com/en-au/windows-sharepoint-services-help/introduction-to-data-calculations-HA010121588.aspx#BM3 

The only thing mentioned on that page is to use brackets around any column names with spaces or other special characters.

One other thing to note: I recreated this using the Information Worker Demo virtual machines that Microsoft offers for download.  This is for SharePoint Server 2010, Enterprise license, build: 14.0.4763.1000, which is the RTM build.  I haven’t checked this bug with more recent builds or updates.

Print | posted @ Sunday, May 8, 2011 1:47 PM