Use SharePoint ID Field in Calculated Column

Use SharePoint ID Field in Calculated Column

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

Use ID Field in Calculated Column SharePoint

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:

Use ID Field in Calculated Column SharePoint

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

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.
Using ID Field in a Calaulated Column in SharePoint
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.
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()

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.
Using ID Field in a Calaulated Column in SharePoint
Test SharePoint Designer Workflow Workflow
  • Make sure that, the value of newly created column “ItemID” 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 the calculated column.
Using ID Field in a Calaulated 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:

auto generate sequence number column in SharePoint List

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

3 thoughts on “Use SharePoint ID Field in Calculated Column”

  1. Pingback: Auto Serial Number in SharePoint New Form using JSOM | SPGeeks

  2. Nnaemeka Nebechi

    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!

  3. Hello, there is a workaround in using [Document ID Value] instead with MID function to strip out text before the actual ID

Leave a Reply