Can't add Calculated columns to a list

[tsekityam]

Notice: many issues / bugs reported are actually related to the PnP Core Library which is used behind the scenes. Consider carefully where to report an issue:

1. Are you using Apply-SPOProvisioningTemplate or Get-SPOProvisioningTemplate? The issue is most likely related to the Provisioning Engine. The Provisioning engine is not located in the PowerShell repo. Please report the issue here: https://github.com/officedev/PnP-Sites-Core/issues.
2. Is the issue related to the cmdlet itself, its parameters, the syntax, or do you suspect it is the code of the cmdlet that is causing the issue? Then please continue reporting the issue in this repo.
3. If you think that the functionality might be related to the underlying libraries that the cmdlet is calling (We realize that that might be difficult to determine), please first double check the code of the cmdlet, which can be found here: https://github.com/OfficeDev/PnP-PowerShell/tree/master/Commands. If related to the cmdlet, continue reporting the issue here, otherwise report the issue at https://github.com/officedev/PnP-Sites-Core/issues

Reporting an Issue or Missing Feature

Can't add calculated column to a list because of empty formula is provided, however, the formula parameter provided is not empty!!

Expected behavior

Can add calculated column to a list

Actual behavior

Can't add calculated column to a list; empty formula error, which is false negative, is shown.

Steps to reproduce behavior

PS C:\Users\tseki\Documents\GitHub\gift-and-floral-arrangement-system> Add-PnPField -Type Calculated -InternalName "C1" -DisplayName "C1" -Formula ="[Title]"

Title InternalName Id
----- ------------ --
C1    C1           e9e5699a-b0a4-4bad-8903-865c7f9a069f

PS C:\Users\tseki\Documents\GitHub\gift-and-floral-arrangement-system> Add-PnPField -Type Calculated -InternalName "C1" -DisplayName "C1" -Formula ="[Title]" -List "Requests"
Add-PnPField : Formula can't be empty.
At line:1 char:1
+ Add-PnPField -Type Calculated -InternalName "C1" -DisplayName "C1" -F ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : WriteError: (:) [Add-PnPField], ServerException
    + FullyQualifiedErrorId : EXCEPTION,SharePointPnP.PowerShell.Commands.Fields.AddField

Which version of the PnP-PowerShell Cmdlets are you using?

• [ ] PnP PowerShell for SharePoint 2013
• [ ] PnP PowerShell for SharePoint 2016
• [x] PnP PowerShell for SharePoint Online

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

PS C:\Users\tseki\Documents\GitHub\gift-and-floral-arrangement-system> Get-Module -Name *pnppowershell* -ListAvailable

    Directory: C:\Program Files\WindowsPowerShell\Modules

ModuleType Version    Name                                ExportedCommands
---------- -------    ----                                ----------------
Binary     2.24.18... SharePointPnPPowerShellOnline       {Add-PnPApp, Add-PnPClientSidePage, Add-PnPClientSidePageSection, Add-PnPClientSideText...}

How did you install the PnP-PowerShell Cmdlets?

• [ ] MSI Installed downloaded from GitHub
• [x] Installed through the PowerShell Gallery with Install-Module
• [ ] Other means

[jagrock]

Same behavior here with SharePointPnPPowerShellOnline with version 2.25.1804.1. I know the -Formula tag was added recently as well.

[jagrock]

https://github.com/SharePoint/PnP-PowerShell/issues/1348 - this is the issue for the -Formula option missing

[scottchristian]

I too am having this issue, just leaving a comment to keep up to date with it's progress

[tsekityam]

I tried with the latest 2.25.1804.1, and the error is still here

PS C:\WINDOWS\system32> Connect-PnPOnline -Url https://esquel.sharepoint.com/teams/demo/tseki -UseWebLogin
PS C:\WINDOWS\system32> New-PnPWeb

cmdlet New-PnPWeb at command pipeline position 1
Supply values for the following parameters:
(Type !? for Help.)
Title: 1488
Url: 1488
Template: STS#3

Title ServerRelativeUrl      Id
----- -----------------      --
1488  /teams/demo/tseki/1488 9cc961ef-a816-4f05-a945-bb2f012e8dbf

PS C:\WINDOWS\system32> Connect-PnPOnline -Url https://esquel.sharepoint.com/teams/demo/tseki/1488 -UseWebLogin
PS C:\WINDOWS\system32> New-PnPList

cmdlet New-PnPList at command pipeline position 1
Supply values for the following parameters:
(Type !? for Help.)
Title: test
Template: generic
PS C:\WINDOWS\system32> Add-PnPField -Type Calculated -InternalName "C1" -DisplayName "C1" -Formula ="[Title]" -List test
Add-PnPField : Formula can't be empty.
At line:1 char:1
+ Add-PnPField -Type Calculated -InternalName "C1" -DisplayName "C1" -F ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : WriteError: (:) [Add-PnPField], ServerException
    + FullyQualifiedErrorId : EXCEPTION,SharePointPnP.PowerShell.Commands.Fields.AddField

PS C:\WINDOWS\system32> Get-Module SharePointPnPPowerShell* -ListAvailable | Select-Object Name,Version | Sort-Object Version -Descending

Name                          Version
----                          -------
SharePointPnPPowerShellOnline 2.25.1804.1

[calbert82uhah]

I have the same issue with 2.26.1805.1

[pedropablo]

still not working on 2.28.1807.0

I tried to create it as a Site column (Because the site column works, it gets created). But when i try to add it to a list it shows another error:

Add-PnPField -List "MyList" -Field "MyCalculatedSiteColumn"
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.
At line:1 char:1
+ Add-PnPField -List "MyList" -Field "MyCalculatedSiteColumn"
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : WriteError: (:) [Add-PnPField], ServerException
    + FullyQualifiedErrorId : EXCEPTION,SharePointPnP.PowerShell.Commands.Fields.AddField

If I add the site column (created by add-pnpfield) manually to the list it works.

[masterl1983]

still not working with 3.0.1808.1

The whole storry around calculated fields is really frustrating me.

I am still looking for a working Solution for a scenario like this:

1. Create a SiteCollection with default Language "German"
2. Create a List
3. Add a calculated field (Not a SiteColumn) to a List with formula '=Links("SomeValueOrExistingColumn";4)' ==> English Formula would be '=Left("SomeValueOrExistingColumn",4)'

Anybody here who knows how to achieve this?

[pedropablo]

@masterl1983 I had to create the column by provisioning schema. For my case was a workaround but for you it may be the best approach because that thing is for creating sites and lists. Check around Get-PnPProvisioningTemplate on a site configured like you want and tweak the XML for your needs.

[BaltazarBugs]

@masterl1983 You need to get the calculated field from your template and change by the english version.

ProvisioningTemplate template = web.GetProvisioningTemplate(ptci);
var calculatedFields = template.SiteFields.Where(x => x.SchemaXml.Contains("Type=\"Calculated\""));

Or from Lists

foreach (var lst in template.Lists)
{
    calculatedFields = lst.Fields.Where(x => x.SchemaXml.Contains("Type=\"Calculated\""));
    //insert code
}
var calculatedFields = template.SiteFields.Where(x => x.SchemaXml.Contains("Type=\"Calculated\""));

foreach (var cField in calculatedFields)
{
    cField.SchemaXml = ReplaceCalculatedField(cField.SchemaXml);
    Console.WriteLine(cField.SchemaXml);
}

private static string ReplaceCalculatedField(string schemaXml)
        {
           // List of function FR,EN
            string formula = ConfigurationManager.AppSettings[KEY_FORMULA_CONVERSION];

            string returnValue = schemaXml;

            foreach (var item in formula.Split(';'))
            {
                var valFRUS = item.Split(',');
                if (valFRUS.Length==2)
                {
                    string pattern = String.Format(@"\b{0}\b", valFRUS[0]); // Match whole word
                    returnValue = Regex.Replace(returnValue, pattern, valFRUS[1], RegexOptions.IgnoreCase);
                }

            }
            returnValue = Regex.Replace(returnValue, ";", ",", RegexOptions.IgnoreCase);

            return returnValue;
        }

[tsekityam]

I opened a support case for this issue. MS Support team told me that the engineer team is aware of the issue and working on it, however, no ETA yet.

[Zerg00s]

Still getting the same error :(

Name Version

---

SharePointPnPPowerShellOnline 3.1.1809.0

[Melfear]

@BaltazarBugs What formula do you put in the [KEY_FORMULA_CONVERSION] settings?

[BaltazarBugs]

@Melfear it’s just a string like moyenne(fr),average(us);xxxxx(fr),xxxxx(us) And I split all terms and replace