Jesper M. Christensen

SharePoint and Security

SharePoint calculated columns do not show ID og time-date


I saw this "problem" when I was creating a autonumber function for SharePoint List items. I wanted to have a project ID and an autonumber-field to every list item.

When searching the web I discovered that the hidden ID field could be used and I made the following formula:

="P2100-"&ID

Every item got the new project-ID and I was thrilled….for a while…

Then I created new items and none of these got any value in the end of the field! Only when I updated the calculated column definition (edit, no change and save) the ID’s was calculated and put on the items. A new web-search made me realize that the ID (and also Time & Date if that is used) is processed AFTER the calculation and save of this information. The sites suggested that I programmed an event handler that recalculated the item column, but with many different sites and manual work this was not an option.

I came up with an idea that worked for me with Windows Powershell. Not a pretty solution and perhaps this generates too much overhead on your system, so please evaluate other options if you have a lot of lists/items and performance is an issue.

The Windows Powershell script connects to the site, list and updates the column definition without changing anything.


#Update the field to recalculate the calculated field
System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
$spSite = new-object Microsoft.SharePoint.SPSite("http://intranet.domain.local")
$spWeb = $spSite.OpenWeb("/")
$spList = $spWeb.Lists["Projects"]
$field = $spList.Fields["ProjectID"]
 
$field.Update($true)
$spSite.dispose()

I made my solution a bit more creative and made a list of definitions and looped through the items with the script.

Advertisements

8 responses to “SharePoint calculated columns do not show ID og time-date

  1. Shewin McGhee February 23, 2011 at 15:23

    Jesper,

    I was wondering, where is this stored? I am asking because I am currently building a database/list within SharePoint and I would like to use the unique ID without using SPD. I know this article is old but I would appreciate your help. IF you can’t help can you point me in the right direction.

    • jespermchristensen February 25, 2011 at 13:18

      Hi Shewin,

      This information is stored in a hidden column and in SharePoint 2010 you have the possiblity toshow/interact with this. The ID is not generated until the item is created/saved- therefore this post on how to re-read and store the information. If you use Visual Studio to create a solution, you can get the created-item-ID back when you create/save an item.

      Hope you can use this answer
      Jesper

      • Sherwin McGhee February 25, 2011 at 13:30

        I think I now know why the information you presented, slightly didn’t make sense. I am using SP2007, and this is for SP2010. So, I am wondering if I create a CEWP and apply the code will it work or should I do something else?

      • jespermchristensen March 3, 2011 at 00:58

        Hi again Sherwin,

        Sorry about my “mistyped” answer. The code is actually for MOSS 2007. The ID field is actually hidden in both MOSS 2007 and SP2010, but in SharePoint 2010 you have the possibility to show eg. the ID column.

        My blog is about creating a calculated column using this hidden ID column. When a new item is added, the ID field isn’t appearing until you “touch” the Column definition again (open and save it). The powershell script is used to “force” this behavior in the background, and can be scheduled if needed.

        If you need to see this hidden field, perhaps you can use the following link: http://www.endusersharepoint.com/2010/04/23/sharepoint-toggle-column-visibility-in-list-view/

        So actually the ID column is stored as a normal column – it is just hidden in the browser, but you can use the information from it.
        When you add an item, the ID-fiel

  2. Tuhin March 24, 2011 at 08:52

    Hi

    May i know where can i put this script? I am also looking to create running ID but i have no idea where to put this script so that the code will change the ID field

    Thanks
    Tuhin

    • jespermchristensen March 31, 2011 at 21:23

      Hello,

      The script is a powershell-script, and you can save the text in a file eg: spupdate.ps1

      The powershell script can be scheduled using Windows 2003 Scheduler with the following command:
      %windir%\system32\WindowsPowerShell\v1.0\powershell.exe -command “& C:\SPBackup\SPBackup.ps1 ‘C:\SPBackup\SPBackup.xml’

      -I cannot remember the command in Windows Server 2008, but try to google this:)

      Cheers
      Jesper

  3. Donna Beck August 8, 2011 at 20:05

    I’m trying to set up an list where end users will enter their issues and it will automatically assign them a number. Is there a way to do this in a simple way that I can understand? This is not my area of expertise. For instance, we would want to start out with CSGEL100 and then the next person would be CSGEL101. Does that make sense?

    • jespermchristensen August 31, 2011 at 23:12

      Hi Donna,

      Sorry for my late response on this post – I haven’t checked my wordpress and the mail notification ended up in the unwanted-folder.

      You could create a calculated field where the formula is made like this post and make use of a scheduled powershell script as the one above.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: