Calculated Field Formula localization | AddFieldAsXml

[Tanddant]

Target SharePoint environment

SharePoint Online

What SharePoint development model, framework, SDK or API is this about?

SharePoint CSOM

Developer environment

None

What browser(s) / client(s) have you tested

• 💥 Internet Explorer
• 💥 Microsoft Edge
• 💥 Google Chrome
• 💥 FireFox
• 💥 Safari
• mobile (iOS/iPadOS)
• mobile (Android)
• not applicable
• other (enter in the "Additional environment details" area below)

Additional environment details

Latest version of CSOM, PnP PowerShell or just REST API

Describe the bug / error

Something has changed in the behavior when adding SharePoint Fields (in this case using a provisioning engine) - I have a site with LCID 1030 (Danish) - and normally I would add the following field to the site

SchemaXML

<Field Type="Calculated" DisplayName="Week" EnforceUniqueValues="FALSE" Indexed="FALSE" Format="DateOnly" LCID="1030" ResultType="Text" Group="REDACTED" ID="{cb738292-b734-4866-8d94-edf6886348f6}" SourceID="{389dbd9b-06d5-4681-aa62-a996395fffdc}" StaticName="REDACTED_Week" Name="REDACTED_Week" CustomFormatter="" Required="FALSE" AllowDeletion="TRUE" Version="8" ReadOnly="TRUE"><Formula>=INT(([Report Date]-DATE(YEAR([Report Date]-WEEKDAY([Report Date]-1)+4),1,3)+WEEKDAY(DATE(YEAR([Report Date]-WEEKDAY([Report Date]-1)+4),1,3))+5)/7)</Formula></Field>

However when I now add that SiteField to a list I get an exception, investigating a bit further, it turns out that the ListFields formulas are now translated to the locale of the site, while the site fields aren't

Resulting in the following List field SchemaXML

<Field Type="Calculated" DisplayName="Week" EnforceUniqueValues="FALSE" Indexed="FALSE" Format="DateOnly" LCID="1030" ResultType="Text" Group="REDACTED" ID="{cb738292-b734-4866-8d94-edf6886348f6}" SourceID="{389dbd9b-06d5-4681-aa62-a996395fffdc}" StaticName="REDACTED_Week" Name="REDACTED_Week" CustomFormatter="" Required="FALSE" AllowDeletion="TRUE" Version="6" ReadOnly="TRUE" ColName="sql_variant1" RowOrdinal="0"><Formula>=INT((REDACTED_ReportDate-DATE(YEAR(REDACTED_ReportDate-WEEKDAY(REDACTED_ReportDate-1)+4),1,3)+WEEKDAY(DATE(YEAR(REDACTED_ReportDate-WEEKDAY(REDACTED_ReportDate-1)+4),1,3))+5)/7)</Formula><FieldRefs><FieldRef Name="REDACTED_ReportDate" /></FieldRefs></Field>

The key difference I notice is the formula is now translated

SiteField

=INT(([Report Date]-DATE(YEAR([Report Date]-WEEKDAY([Report Date]-1)+4),1,3)+WEEKDAY(DATE(YEAR([Report Date]-WEEKDAY([Report Date]-1)+4),1,3))+5)/7)

ListField

=HELTAL(([Report Date]-DATO(ÅR([Report Date]-UGEDAG([Report Date]-1)+4);1;3)+UGEDAG(DATO(ÅR([Report Date]-UGEDAG([Report Date]-1)+4);1;3))+5)/7)

This unpredictable experience makes it really hard to work with, I know from chatting with @kasperbolarsen he's been struggling with the same thing when pulling content-types from the content-type hub

Steps to reproduce

1. Create a site column and content-type in the content-type hub
2. Use them on a site in a different language than the hub
3. Cry

Expected behavior

I would expect consistent formulas within the site, I believe in the past the API would always just accept english, this seems like a change (perhaps related to knowledge agent) on the lists/library columns

[Ashlesha-MSFT]

Hello @Tanddant,
Thank you for bringing this issue to our attention. We will look into it and get back to you shortly.

[Ashlesha-MSFT]

I was able to reproduce this behavior on a Danish (LCID 1030) site.

I will engage the engineering team to confirm whether this is expected localization behavior (UI/server-side) or if it should be considered a regression.