Pages

Thursday, May 30, 2013

Calculated Column #NAME? Error

Symptoms:
On calculated columns, you get an error in at least one of two places.  One is in the list view or datasheet view where the result of the calculation is supposed to appear.  It shows as #NAME? instead of the expected value.  The second place it appears is in the formula in the calculated column instead of the column name.

When you try and edit the formula in SharePoint, you get an error when saving the edits: "SQL Server Error.  The SQL Server might not be started."


 (Which is a useless error because if the SQL server weren't started, this page would not appear and you would get a "cannot connect to configuration database" error instead. But those are complaints for another time.)

The calculated columns worked at one point, but now they do not.  Not working was independent of any change in the list or columns.

Issue:
Special characters in column names.  This was difficult to discern because the calculations worked, then one day, randomly, it did not.  Just one of the little mysteries of SharePoint.  In my case, it was spaces causing issues.
SharePoint has two ways of referring to columns:
  1. Display name: this is what is sounds like.  When you have a column called “Courses Completed”, you use the name that appears as the display, with the space.  You can change it after you have created the column.
  2. Internal name: this is what they system uses to refer to the column in the programming, since special characters can’t be used behind the scenes.  It cannot be changed even if you change the display name of a column. We had a column called  "AY PP1 End".  In the URL, the column name is referred to like this: “AY%5Fx0020%5F2%5Fx0020%5FPP%5Fx0020%5F1%5Fx0020”
I suspect there may have been two issues at play.  One was the spaces and the other was the length with the encoding.  Apparently, anything over 32 characters is an issue.  This one was 47.  Although, it is 47 the way it transcodes it in the URL.  Maybe it was less for the real internal column name.  In any case, there was an issue with at least one of these items.

Solution:
Delete the column names with spaces or other special characters and make new ones without them.  I turned "AY PP1 End" to "AYPP1End".   Then, I redid the calculations and everything is happy again.

No comments:

Post a Comment