In this article, I will explain
How to use The ID field in Calculated Column in SharePoint?
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!
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 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.
- 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.
- Open your list, Create a new Field called “ItemID” with single text data type.
- Hide the new ID column from showing in the New Form and Edit form Using PowerShell.
$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.
- Test your workflow, you should note that the ItemID column is equal to the ID column.
- 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.
- SharePoint Online.
- SharePoint 2016.
- SharePoint 2013.
- SharePoint 2010.
- The Supported and Unsupported Columns In SharePoint Calculated Column Formula
- Using Lookup Field in SharePoint Calculated Column Formula.
Check my related answers at StackExchange.