Using ID Field in Calculated Column in SharePoint

In this article, I will explain

How to use The ID field in Calculated Column in SharePoint?

Scenario

I have tried to add ID field in a calculated column that added successfully without any error in the formula.

But I noticed that the calculated column is not calculated on the item added or updated!

Cause

You should be aware of the ID field in the calculated column is only calculated  when the formula is updated/changed again in the calculated column field settings,

Finally, I realized that the ID field in the calculated column is not supported to use in the calculated column. and what proves that it’s not listed by default in the available column as shown below.

Using ID in a Calaulated Column in SharePointWorkarounds

Using SharePoint Designer Workflow
  • Create a new ID field.
  • In the workflow, Set the new ID field value with the original ID column value on item created or changed.
  • Now you can use the new ID field in the SharePoint calculated column Formula.
Using Code
  • In case you have a development background,  you can also use the Event Receiver to build your formula via code, check my answer at Calculated Column vs. Workflow.

In this article, I will explain the workflow workaround steps.

Steps

  • Open your list, Create a new Field called “ItemID” with single text data type.

Using ID Field in a Calaulated Column in SharePoint

  • Hide the new ID column from showing in the New Form and Edit form Using PowerShell.

Hide Column from new form


$rootWeb = Get-SPWeb http://siteURL

#Get an reference to the  List

$list=$rootWeb.Lists["List Name"]

#Get a reference to the created field

$CalField = $list.Fields["Column Name"]

#Hide the column in New and Edit Mode

$CalField.ShowInEditForm = $false

$CalField.ShowInNewForm = $false

#Push field Update

$CalField.Update()

  • Open SharePoint Designer > Workflow > add a new list workflow > Select your list.
  • Provide an appropriate name for your workflow.
  • In Actions > Below List actions > select Set Field in Current Item.
  • Select your new field “ItemID” and set it to the current List Item ID.
  • Make sure that the workflow will start on Item created/changed.
  • Save & Publish Your Workflow.

Using ID Field in a Calaulated Column in SharePoint

  • Test your workflow, you should note that the ItemID column is equal to the ID column.

Using ID Field in a Calaulated Column in SharePoint

  • Open your List > List Setting > List column > Create a new Calculated Column.
  • Add the listed new field “ItemID” in your formula > save the column setting.
  • Add new List item, you should note that the NewID=ID and reflected to the calculated column.

Using ID Field in a Calaulated Column

Applied To
  • SharePoint Online.
  • SharePoint 2016.
  • SharePoint 2013.
  • SharePoint 2010.
See Also

Check my related answers at StackExchange.

2 thoughts on “Using ID Field in Calculated Column in SharePoint

  1. Hi Mohammed,

    Thanks for this post and solution. I have a bit challenge which surprised me till now. I was able to follow through on the steps for this post and the result came out fine. The ID was showing in the ItemID column.

    I then did the same thing in another site and in adding the ps scripts, I got the following errors below:

    ++++++++++++++++++++++++++++++++++

    $CalField = $list.Fields[“ItemInfo”]
    Cannot index into a null array.
    At line:1 char:26
    + $CalField = $list.Fields[ <<<< “ItemInfo”]
    + CategoryInfo : InvalidOperation: (ItemInfo:String) [], RuntimeException
    + FullyQualifiedErrorId : NullArray

    $CalField.ShowInEditForm = $false
    Property ‘ShowInEditForm’ cannot be found on this object; make sure it exists and is settable.
    At line:1 char:11
    + $CalField. <<<< ShowInEditForm = $false
    + CategoryInfo : InvalidOperation: (ShowInEditForm:String) [], RuntimeException
    + FullyQualifiedErrorId : PropertyNotFound

    $CalField.ShowInNewForm = $false
    Property ‘ShowInNewForm’ cannot be found on this object; make sure it exists and is settable.
    At line:1 char:11
    + $CalField. <<<< ShowInNewForm = $false
    + CategoryInfo : InvalidOperation: (ShowInNewForm:String) [], RuntimeException
    + FullyQualifiedErrorId : PropertyNotFound

    ++++++++++++++++++++++++++++++++++

    I then deleted the first list and workflow I created to confirm if its peculiar to sites, recreated the list in the initial site where it worked and the workflow. Then I tried adding the ps script again. I still got the same error above.

    Please, what am I doing wrong…I need your correction and guidance.

    Many thanks!

    Like

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s