in

SharePoint Blogs

The Best Place for SharePoint-related Blogs

JoeD

Avoid using hyphens in site column names!

There appears to be a bug in MOSS 2007 when you use site columns with hyphens in the name of calculated fields.

This only seems to occur with site columns, not in custom list columns (created in the list, not from site columns).

Here is an example - I have a custom list with a column named Test-Field (defined as Number, although it doesn't matter).  Then I create another column named DoubleIt, defined as [Test-Field]*2.

Then I edit the properties of the DoubleIt field and it shows me my original formula: =[Test-Field]*2.  Looks good.

Now instead, I want to create Site Columns.  I create a site column named Test-SiteCol (again defined as Number, but doesn't matter).  Then I create another site column named SiteDoubleIt, defined as [Test-SiteCol]*2.

Then I edit the properties of my new SiteDoubleIt site column that I just created (above) and it shows the formula: =#NAME?-#NAME?*2  !!!

Yikes!  It strips out the [ ] delimiters and appears to view the hyphen as a minus operator and thinks each side of the hyphen is an invalid column name reference.

So... don't use hyphens in site column names if you plan on ever using them in calculated columns.

 

Published Feb 06 2008, 10:06 PM by joed
Filed under: , , ,

Comments

 

Links (2/7/2008) « Steve Pietrek’s SharePoint Stuff said:

Pingback from  Links (2/7/2008) « Steve Pietrek’s SharePoint Stuff

February 7, 2008 6:26 PM
 

Craig Foote said:

Makes perfect sense to me...as a longtime Excel user, I can see that SharePoint is viewing your hyphenated column name as a formula consisting of two values with a "minus" sign between them.  Since it can't recognize either of the strings, it shows the #NAME? error on each side of the hyphen.  So, your advice is quite correct:  don't use a hyphenated name if you're going to use it as part of a formula in a calculated field.  It's not a bug, it's a feature!

May 6, 2008 1:15 PM
 

joed said:

Nope, it's a bug...

As I explained in my post, I can create a calculated column in a list with hyphens in the name and Sharepoint correctly interprets it.  Hyphens ARE allowed because you use the square brackets as delimiters around the name, just like SQL Server.

[Test-Field] means that the hyphen between the brackets is to be interpreted as part of the field name, not a minus sign.  This works perfectly fine as a calculated column.  For example, the formula [Test-Field]*2 means to multiply the column "Test-Field" by 2.  No subtraction is attempted.

But when you set up the exact same formula as a Site Column, Sharepoint incorrectly ignores the square bracket delimiters and tries to interpret the hyphen as a minus sign.

My advice was not to avoid using hyphens in calculated columns.  That works.  My advice is to avoid using hyphens in calculated *Site Columns*, because there is a bug in Sharepoint where it ignores the delimiters around column names in Site Columns.

It really doesn't relate to Excel - with Excel, it will not allow you to create named ranges with hyphens in the name so it's impossible for Excel to misinterpret - the name can't exist.  Sharepoint DOES allow you to create column names with hyphens, and provides the ability to use those columns in formulas using the square bracket delimiters.  But it doesn't work right with Site Columns.

May 6, 2008 9:13 PM

Leave a Comment

(required )  
(optional )
(required )  
Add
Need SharePoint Training? Attend a SharePoint Bootcamp!
Posts (c) their respective authors. Everything else (c) 2007 SharePoint Experts