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.

Advertisements

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