Use SharePoint ID Field in Calculated Column

In this post, we’ll explain How to use SharePoint ID field in Calculated Column?

We will also go through the following:

You might also like to read Auto Serial Number in SharePoint New Form using JSOM

Why SharePoint ID Field in Calculated Column is not Calculated Automatically?

If you tried to use the SharePoint ID field in Calculated Column Formula, you will note that the [ID] column is not listed in calculated column formula in SharePoint as shown below:

Meanwhile, if you tried to use SharePoint ID field in a calculated column formula as [ID], it will be added successfully without any error in the calculated column formula. but you’ll note that the ID field in SharePoint calculated column is not calculated automatically when a new item added or updated!

Actually, the SharePoint ID field is not calculated in the calculated column until you manually update the formula in the calculated column field settings.

It’s by design, the SharePoint ID Field in Calculated Column [ID] is NOT supported and it can’t be referenced in

• SharePoint Calculated Column Formula, and
• SharePoint Column Validation.

There are also other SharePoint fields that unsupported to use in calculated column like

• Lookup field (Check how to use Lookup Field in Calculated Column in SharePoint)
• Multiple lines of text
• Choice field with multiple selections.

Check the full list of supported and unsupported fields in SharePoint Calculated Column Formula.

Use SharePoint ID Field in Calculated Column

In this section, we’ll provide multiple workarounds to use ID Field in Calculated Column in SharePoint.

1. Use SharePoint ID Field in Calculated Column Using SharePoint Designer Workflow.
2. Create an Auto-Incremental ID field in Calculated Column Using JSOM.
3. Build a Calculated Column formula in SharePoint using an Event Receiver.

Use SharePoint ID Field in Calculated Column Using SharePoint Designer Workflow

here, we’re gonna create a SharePoint Designer workflow as a workaround to use SharePoint ID Field in Calculated Column formula by doing the following:

• 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.

Steps in details

Create a new ID field in SharePoint

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

Hide a new SharePoint field in new and edit forms

• 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()

You might also like to read Show and Hide Columns in SharePoint List Forms Using PowerShell

Create a SharePoint Designer Workflow to use SharePoint ID Field in Calculated Column

• 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 SharePoint Designer Workflow Workflow

• Make sure that, the value of newly created column “ItemID” 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 the calculated column.

Create Auto-Incremental ID field in Calculated Column Using JSOM

You can also create a custom ID field that bundle your calculation by using JSOM as shown below:

Check the detail steps for this solution at Auto Serial Number in SharePoint New Form using JSOM.

Build Calculated Column formula in SharePoint using an Event Receiver

In case, you have a development background, you can also create the Event Receiver on item added or updated to

• Build your formula via code instead of using the calculated column.
• Or, to set the value of the ID field to another text field that can be used in your calculated column formula.

Check how to create SharePoint receiver at Create an event receiver.

Conclusion

In conclusion, we have explained How to use SharePoint ID field in Calculated Column Formula by providing the below workarounds:

• Use SharePoint ID Field in Calculated Column Using SharePoint Designer Workflow.
• Create an Auto-Incremental ID field in Calculated Column Using JSOM.
• Build your Calculated Column formula in SharePoint using an Event Receiver.

Applied To

• SharePoint 2019.
• SharePoint 2016.
• SharePoint 2013.
• SharePoint 2010.

You might also like to read

• Supported and Unsupported Columns In SharePoint Calculated Column Formula
• Using Lookup Field in SharePoint Calculated Column Formula.