How to handle ‘multicultural’ issues with the formula of a SharePoint SPFieldCalculated

Setting a formula in code for a SharePoint SPFieldCalculated is a real nasty task. I spent a lot of time figuring out why some formula’s did work and others did not in various cases.

When designing a formula you may think you need to take a few things into account like language and separator. During testing I came to the conclusion that the following has impact:
- thread culture
- separator character used for separating parameters in the formula
- SPContext.Current == null or not
- SharePoint default language (what language was the original SharePoint setup)
- and that the language of the formula method (like CONCATENATE) is not important at all.

Simple Test Case
First let me tell you about my test case I used. Two text fields, one is a company tag, the other is the first name of a contact person. A third will be the calculated field. It will need to show the following: ‘<Company Tag> - <First Name>’, without the ‘.
SPFieldText internalName: companyTag, DisplayName (English) = “Company Tag”, DisplayName (Dutch) = “Tag bedrijf”
SPFieldText internalName: contactFirstName, DisplayName (English) = “First Name”, DisplayName (Dutch) = “Voornaam”,
The creation of the calculated field should be in code (in this case a feature receiver). The deployment should work in various cases like:
Case 1: Activating feature by hand in UI.
Case 2: Feature is activated by onet.xml of a web template.

The problem:
The problem arises when you need it to work for case 1 and case 2 in different cultures. In my case in English and Dutch. By testing the creation using the following scenario’s you will see where it fails.

Scenario 1: Case 1 in English thread culture
Formula: “=CONCATENATE([Company Tag],\” – \”,[First Name])”
Separator: , (English)
Display names:  all English
Result: It works…

Scenario 2: Case 1 in Dutch thread culture
Formula: “=CONCATENATE([Tag bedrijf];\” – \”;[Voornaam])”
Separator: ; (Dutch)
Display names:  all Dutch
Result: It works…

Scenario 3: Case 2 in English thread culture
Same as Scenario 1:
Result: It works…

Scenario 4: Case 2 in Dutch thread culture
Same as Scenario 2:
Result: It does NOT work. 

Why doesn’t scenario 4 work? Threads are all Dutch, passing all the same parameters for the formula as scenario 2. So why…?
Also tried various combinations on the formula like:
- English display names, English and Dutch separator,
- Using Internal names, English and Dutch separator,
- Using Dutch formula method names like “=TEKST.SAMENVOEGEN([Tag bedrijf];\” – \”;[Voornaam])”

Only thing that seems to work was setting the separator to the default English (the default SharePoint installation culture) variant:
Formula: “=CONCATENATE([Tag bedrijf];\” – \”;[Voornaam])”
Separator: , (English)
Display names:  all Dutch

The main difference I detected between case 1 and case 2 was the SPContext.Current was null in case 2.
In case you design a formula and need to determine the separator, do not only check the Thread culture, but also the existence of SPContext.Current.

Guidelines regarding formula
Adding all variations of testing and failures together I came up with the following guideline when designing the formula for the SPFieldCalculated.
- Formula string should start with a '=' (not shown in above cases)
- always encapsulate the display name with brackets [], better safe than sorry (not shown in above cases)
- in case SPContext.Current is NOT null
* use field display names for the Thread.CurrentThread.CurrentUICulture
* use separator for the Thread.CurrentThread.CurrentUICulture  (Dutch= ‘;’ English=”,”)
- in case SPContext.Current==null (e.g.: when feature code via activated by an onet.xml of a webtemplate)
* use field display names for the Thread.CurrentThread.CurrentUICulture
* use separator that matched the SharePoint Initial Installation Language (English on my development machine. Contact me if you know how to determine this in code!)
- Methods names in the formula are culture independent, you can use English/Dutch/Whatever without a problem. (e.g. CONCATENATE and TEKST.SAMENVOEGEN work in English and Dutch thread culture)

I hope I saved you some valuable time in creating formula’s for SPFieldCalculated fields in code.
New insights on the formula are welcome.