Using Add-PnPFieldFromXml to add a calculated column to SharePoint

[renewtx]

Hello All,

Wondering if any one can shed some light ... I have a calculated column that uses the following formula:

=IF([Archive File]<>"Yes","Archive",(IF(AND([Archive File]="Yes",[Archive Processed]=FALSE),"Archiving in Progress","Archived")))

As you can see it references two additional (custom) columns (Archive File and Archive Processed). This formula works fine. I am now trying to use PnP to provision my columns and associated content types, and so I am using Add-PnPFieldFromXml to create my calculated column. Within my code the XML for the column is generated as follows:

=IF([Archive File]<>"Yes","Archive",(IF(AND([Archive File]="Yes",[Archive Processed]=FALSE),"Archiving in Progress","Archived")))

When I deploy the column using Add-PnPFieldFromXml the cmdlet executes without error and the column is created. However, when I look at the formula after creation it appears as follows:

Note how the referenced columns don't seem to resolve, yet they are clearly there (visible in the column selector list -- and they were created BEFORE I try to deploy my column that references them). If I modify my formula to use the out of the box Title instead, the deployment works fine. Any one know why I cant seem to reference custom columns via Add-PnPFieldFromXml or is there a special way they need to be referenced? I have tried using both the internal and the display names of the referenced columns and I get the same result either way.

Thanks in advance for any insights!

[JoshuaSHenderson]

I am running into this same error. What does your PNP code look like?

[renewtx]

Hi,

So what I am doing is reading my column definitions (including the formulas) from a CSV file (via the $row variable below) and creating the column based on that information. In my code, for the calculated columns, I have this:

=======================================================
#Remove leading '.'
$formula = $row.Formula.substring(1)
$group = $row."Group"
$internalName = $row."InternalName"
$displayName = $row."ColumnName"

    if($row."InternalName" -eq "NNKiStatus"){

        $fieldXML = "<Field Type='Calculated' Group='$group' StaticName='$internalName' Name='$internalName' DisplayName='$displayName' ID='$newGUid' ResultType='Text' ReadOnly='TRUE'><FieldRefs><FieldRef Name='Archive File' /><FieldRef Name='Archive Processed' /></FieldRefs><Formula>$formula</Formula></Field>"

    }elseif($row."InternalName" -eq "NNKiRecordFinalDate"){

        $fieldXML = "<Field Type='Calculated' Group='$group' StaticName='$internalName' Name='$internalName' DisplayName='$displayName' ID='$newGUid' ResultType='Text' ReadOnly='TRUE'><FieldRefs><FieldRef Name='Exception Date' /><FieldRef Name='Record Archive Date' /></FieldRefs><Formula>$formula</Formula></Field>"

    }else {

        write-host ("XML field defintion for calculated column " + $row."InternalName" + " not specified ... exiting! ")
        exit
    }

    $curColumn = Add-PnPFieldFromXml -FieldXml $fieldXML

=======================================================

This creates the "plumbing" for the column correctly, but does not parse the column references in the formula correctly as I mention above. So for now I am manually updating the formula after creation, which is not ideal, but gets me past the issue.

All that said, originally I used just Add-PnPField to create the calculated column and that did not work (apparently because that cmdlet did not support specifying the '... a ReturnType to be set for calculated fields when created on the site level ...'). So then I switched to Add-PnPFieldFromXml and encountered the above issue. I then found out that a fix for Add-PnPField was implemented which seems to fix the issue when I tested it. Unfortunately, for me, it is in a pre-release state and requires the use of a newer version of PowerShell PnP which in turn requires PowerShell v7 (and I can't use that in my current project). If you want to test the updated Add-PnPField:

• Download PS7 x64 from here
• Open PowerShell through Windows Start search and enter PowerShell ... open a PowerShell 7 window. It has a black background color versus a blue background color for PowerShell 5. That’s the easiest way to distinguish them both.
• Once you have a PowerShell 7 window open, install the updated PnP PowerShell using:

Install-Module PnP.PowerShell -AllowPrerelease

You can then use the usual Connect-PnPOnline, then issue the command:
Add-PnPField -Type Calculated -InternalName "TestSomething1" -DisplayName "Test Something1" -Formula "=IF([Archive File]<>'Yes','Archive',(IF(AND([Archive File]='Yes',[Archive Processed]=FALSE),'Archiving in Progress','Archived')))]" -ResultType Text

When you check the column in the site (in my case i did it on the content type hub), it appears correctly as expected:

Hope this helps!

[dimzava]

@renewtx

If you still want to use Add-PnPFieldFromXml, try with the internal field names rather than their display names. For example, <FieldRef Name='InternalNameOfArchiveProcessedFieldHere' /> instead of <FieldRef Name='Archive Processed' />.

Then, in your formula, also use the internal field names but without the square brackets, e.g. something like this:
=IF(ArchiveFileInternal<>"Yes","Archive",(IF(AND(ArchiveFileInternal="Yes",ArchiveProcessedInternal=FALSE),"Archiving in Progress","Archived")))

This did the trick for me.

[raviraj-bhalerao]

Use field internal name in formula and field ref