Provisioning error when re-applying list calculated fields: The formula refers to a column that does not exist.

[chaoshades]

Which PnP repository should you use to report the issue?

PnP Sites Core - https://github.com/OfficeDev/PnP-Sites-Core
Version: 3.6.1902.2

I'm using the PowerShell cmdlets. I installed it with :
Install-Module SharePointPnPPowerShellOnline

Category

[x] Bug
[ ] Enhancement

Environment

[x] Office 365 / SharePoint Online
[ ] SharePoint 2016
[ ] SharePoint 2013

Expected or Desired Behavior

I have manually created a site collection with SharepointOnline and exported a template from it with the Powershell cmdlet. I am trying to apply this template to a blank site collection. I have a calculated field on one of my lists, which, for simplicity, have been reduced to only copy the value from another column.
Formula looks like this in the template: =[{fieldtitle:EndDate}]
And the names in the definition of the field are:
<Field Type="DateTime" DisplayName="End" [...] StaticName="EndDate" Name="EndDate" [...] />
I expected the list to be updated on the blank site, like the other ones with no calculated fields.

Note : I know (from issue #633) about the fields order and so far, my calculated fields are always after the fields they refers to.

Observed Behavior

Creating the list the first time, all was good.
When I re-applied the template, without changing anything (just to test if I could update my list by re-provisioning), I received the error : "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." After some research in the issue tracker, I have found this resolved issue #849. So, I then tried by removing {fieldtitle} and replacing the name with the DisplayName which give this formula instead : =[End] and it worked.
I deleted the list, re-applied the template and it worked on the initial creation too.

Steps to Reproduce

• Create a list with a calculated field in it that refers another a column within the list
  • Generate a template using PnP provisioning framework
  • Apply template on a newly created site collection
  • Apply again the same template on the same created site collection

[dstretch]

I have exactly the same problem reapplying a template and the solution above worked for me too.

Change formula references from [{fieldtitle:InternalName}] to [Display Name].

Thanks

[nilang-shah]

@chaoshades recommendation also solved my problem!

[bhlaws]

I just hit the same issue. I agree: the solution is to replace the reference to the internal name with the field's Display Name. To assist in this, I created the following PowerShell script. I run this after the Get-PnPProvisioningTemplate.

Sorry - I know it's not the most efficient code and doesn't cover all edge cases or potential errors, but it did the trick for me. :-) Hopefully this will help someone else, too.

# We're using a for loop instead of a foreach so that we can have an index that we can use to update the original $template object
for($i = 0; $i -lt $template.Lists.Count; $i++)
{
    $list = $template.Lists[$i]

    # Find all calculated fields. Because of a PnP bug, a template uses [{fieldtitle:<internalFieldName>}] in calculated field formulas and not the field's Display Name
    # The code below finds all instances of {fieldtitle:xxxx} in the field's formula, figures out all of the internal names used (the xxxx after the "fieldtitle:"),
    # looks up the field's Display Name, and replaces {fieldtitle:xxxx} in the calculation with the field's Display Name.
    for($j = 0; $j -lt $list.Fields.Count; $j++)
    {
        if($List.Fields[$j].SchemaXml -like '*Type="Calculated"*')
        {
            $fields = @()
            $xml = [xml]$list.Fields[$j].SchemaXml
        
            # Find all the field references
            $chunksWithFieldTitles = $xml.Field.Formula -split "{fieldtitle:" -like "*}]*"

            # Get all of the internal field names being used
            foreach($chunk in $chunksWithFieldTitles)
            {
                $fields += $chunk.Split("}") | Select-Object -First 1
            }

            # Look up the Display Name based on the internal field name, then update the formula
            foreach($field in $fields | Select-Object -Unique)
            {
                $lookupField = [xml]($list.Fields | Where-Object {$_.SchemaXml -like "*Name=""$($field)""*"}).SchemaXml
                $displayName = $lookupField.Field.DisplayName
                $template.Lists[$i].Fields[$j].SchemaXml = $template.Lists[$i].Fields[$j].SchemaXml.Replace("{fieldtitle:$($field)}", $displayName)
            }
        }
    }
}