[BUG] Add-PnPField - [Regression] throws "Syntax error" when adding Calculated Column with Document ID dependency

[Kiphouder]

Reporting an Issue or Missing Feature

Reporting a regression where Add-PnPField fails to add an existing Calculated Site Column to a list (specifically a calculated column dependent on the 'Document ID' field). While this operation completes successfully via the SharePoint UI, the PnP command now fails.

This script logic, which we use for provisioning each newly created site, functioned correctly for over a year and started failing approximately 2 months ago.

### Expected behavior
When executing Add-PnPField -List -Field , I expect the cmdlet to successfully add the existing site column to the library, automatically resolving the dependency on the Document ID field, just as the SharePoint UI does.

### Actual behavior
The command fails with an error when attempting to add the column.
Error message: "An error occurred while adding the site column 'COLUMNNAME ' to library 'LIBRARY NAME': The formula contains a syntax error or is not supported."
Observation: The issue seems specific to Calculated Columns referencing the Document ID. The cmdlet appears unable to validate the formula context during the add operation (likely due to the Document ID dependency not being resolved yet), whereas the UI handles this correctly.

### Steps to reproduce behavior

1. Activate Document-id-service on site level (_layouts/15/ManageFeatures.aspx?Scope=Site)
2. At the site collection level: Create a Calculated Site Column (e.g. named "TESTBUG") which includes the Value Document ID
3. Run a PnP PowerShell script to add this existing column to the library. The last line will cause the error.

$siteUrl = "https://[tenant].sharepoint.com/sites/[site]"
$listName = "Documents"
$columnName = "TESTBUG"
Connect-PnPOnline -Url $siteUrl -Interactive
# This command now fails (Regression):
Add-PnPField -List $listName -Field $columnName

4. Notice manually adding the column to a library via the UI works fine.

###Workarounds Attempted
To isolate the issue, we attempted several alternative implementation methods, but all resulted in the same failure:

1. XML Provisioning (Add-PnPFieldFromXml): We attempted to add the field using its full XML schema definition.
2. Content Type Approach (Add-PnPFieldToContentType): We attempted to add the field directly to the list's Content Type instead of the list itself.
3. Object-based Approach: We first retrieved the site column object using Get-PnPField and passed the object to the -Field parameter (instead of the string name) to ensure the correct ID/Context was used.
   None of these methods succeeded. This strongly suggests the issue lies in the underlying validation logic for Calculated Columns referencing Document ID during the provisioning phase.

What is the version of the Cmdlet module you are running?

PowerShell-7.2 PnP.PowerShell 2.12.0
Microsoft.Online.SharePoint.PowerShell 16.0.24713.12007

Which operating system/environment are you running PnP PowerShell on?

• Azure Runbooks

[gautamdsheth]

@Kiphouder - did you try with a different column which is not dependent on the Document ID ?

[Kiphouder]

@Kiphouder - did you try with a different column which is not dependent on the Document ID ?

Dear @gautamdsheth , I didn't. Focus was on solving the problem and trying different calls to add the column we really need which includes Document ID.

I assume the community would have reported the regression when it isn't this specific use case.

[reshmee011]

@Kiphouder : Would you be able to share the formula used in that calculated column so we can try to reproduce the issue? It may also be worth testing with the latest 3.1.0 module or the current nightly build to see if the behaviour persists.

[Kiphouder]

@Kiphouder : Would you be able to share the formula used in that calculated column so we can try to reproduce the issue? It may also be worth testing with the latest 3.1.0 module or the current nightly build to see if the behaviour persists.

Dear Reshmee,

Thank you for looking into this.

The error is caused by any formula that references the Document ID column.

Please find the reproduction steps in the original post. Even a minimal formula like =[Waarde van de document-id]* causes the Add-PnPField command to fail.

/* My UI is Dutch. Probably in English UI you can add the column by doubleclick something like "Value of the document-id" when adding the Calculated Site Column in step 2 of my original post.

[gautamdsheth]

@Kiphouder - can you please try with another column , not depending on document ID and let us know ? This would help us narrow it down.

[Kiphouder]

@gautamdsheth I have performed extensive testing again to isolate the issue. Here are my findings:

1. Adding Site Columns to a Library using Add-PnPField (Success vs Failure)

$SiteURL = "https://XX.sharepoint.com/sites/XX"
$LibraryName = "Documenten"
$ColumnInternalName = "TESTBUG"

Connect-PnPOnline -Url $SiteURL -ManagedIdentity -ErrorAction Stop

Add-PnPField -List $LibraryName -Field $ColumnInternalName -ErrorAction Stop

• Single Line of Text: Success.
• Choice: Success.
• Calculated Column (Simple): Formula ="1" (No reference to other columns) -> Success.
• Calculated Column (Referencing): Formula referencing to another column (Doc ID, but also Title, Created) -> FAIL.

Result: Add-PnPField fails whenever a Calculated Column references any other field.

2. Testing with Raw CSOM (XML Injection)
   To see if I could bypass the issue, I also tried the raw CSOM approach (which I haven't used before for this process):

$ctx = Get-PnPContext
$list = $ctx.Web.Lists.GetByTitle($LibraryName)
$siteField = $ctx.Web.AvailableFields.GetByInternalNameOrTitle($ColumnInternalName)

$ctx.Load($siteField)
$ctx.ExecuteQuery()

$schemaXml = $siteField.SchemaXml
$addFieldOptions = [Microsoft.SharePoint.Client.AddFieldOptions]::AddToDefaultContentType

$createdField = $list.Fields.AddFieldAsXml($schemaXml, $true, $addFieldOptions)
$ctx.Load($createdField)
$ctx.ExecuteQuery()

Result: Interestingly, this approach also fails with the same error when the formula references another field. However, adding the exact same Site Column via the SharePoint UI works perfectly. This discrepancy between the UI and the code-based methods (both PnP and CSOM) is what I am trying to understand.

3. Side Finding: Regression/Issue with View Cmdlets
   We cannot use -AddToDefaultView in the same command when adding an existing site column (Add-PnPField). This is expected behavior, that is why we always first added the Column to the library and then added it to the default view with below code:

$defaultView = Get-PnPView -List $LibraryName | Where-Object { $_.DefaultView -eq $true } -ErrorAction Stop
Add-PnPViewField -List $LibraryName -Identity $defaultView -Values $ColumnInternalName -ErrorAction Stop

Since the problems, this part also fails. Even if the column is already added to the library via the UI, Add-PnPViewField (and its successor Add-PnPViewColumn) fails. This worked until the problems with Add-PnPField appeared.

Curiously, direct manipulation of the view via CSOM does work. Even when targeting a Calculated Column with reference to another column.

$defaultView = Get-PnPView -List $LibraryName | Where-Object { $_.DefaultView -eq $true } -ErrorAction Stop
$viewFields = $defaultView.ViewFields
$viewFields.Add($ColumnInternalName)
$defaultView.Update()
Invoke-PnPQuery

Result: Direct CSOM works for views, but the PnP cmdlets do not anymore.

My thoughts:

A. Add-PnPField Regression: Maybe the PnP command broke because of a problem/change in the API.
B. Add-PnPViewField Regression: There appears to be a specific regression here as well. The cmdlets fails, but CSOM ViewFields.Add() method works, so the SharePoint functionality is intact. This points towards an issue within the cmdlet logic itself.

I hope this detailed analysis helps you narrow down where these regressions were introduced.

[reshmee011]

I could add a site calculated field referring to Title and Created to a list.

However it is failing for [Document ID Value] with message

"Add-PnPField: The formula refers to a column that does not exist. Check the formula for spelling mistakes or change the non-existing column to an existing column."

Add-PnPField -List $listName -Field $columnName

Did the cmdlet ever worked in the past? The Document ID field is a complex hidden field which may explain why it is failing. The underlying code is using CSOM in the background. I tried with the REST API endpoint and got same error message:
"Error adding field: {"odata.error":{"code":"-2130575273, Microsoft.SharePoint.SPException","message":{"lang":"en-US","value":"The formula refers to a column that does not exist. Check the formula for spelling mistakes or change the non-existing column to an existing column."}}}"

$site_url = "site url"
Connect-PnPOnline -url $site_url

$LibraryName = "Documents"
$ColumnInternalName = "internalcolumnname"
$ctx = Get-PnPContext
$siteField = $ctx.Web.AvailableFields.GetByInternalNameOrTitle($ColumnInternalName)

$ctx.Load($siteField)
$ctx.ExecuteQuery()
$schemaXml = $siteField.SchemaXml

$url= "$site_url/_api/web/lists/GetByTitle('$LibraryName')/Fields/createfieldasxml"

$schemaXml = $siteField.SchemaXml

$url= "$site_url/_api/web/lists/GetByTitle('$LibraryName')/Fields/createfieldasxml"
$jsonPayload = @"
{
  "parameters": {
    "__metadata": { "type": "SP.XmlSchemaFieldCreationInformation" },
    "SchemaXml": '$schemaXml'
  }
}
"@
    $result = Invoke-PnPSPRestMethod -Url $url -Method Post -Content $jsonPayload -ContentType "application/json;odata=verbose" 

I would advise to raise the bug with Microsoft to see if they can help fix the underlying API.