As you might already know, you can create new fields with SPFieldCollection.AddFieldAsXml(string schema). The schema contains the formula for the calculated field. There are some points to take care of, before you can add the field:
- make sure your referenced fieldnames are the display names and not the internal field names
- the formula has to be in the English format
Changing the fieldnames is an easy task. If you read the schema from a field and want to create a new one with the same formula, you will get something like "=if(fieldA,1,2)".
But what do you do if you want to create the new calculated field in a SPWeb which is not using English as its regional settings? You will get an error that the formula has errors and the field will not be created.
In my case the web was using German regional settings. The formula should look like "=wenn(fieldA;1;2)". Rewriting the formula was not an option.
Here comes the solution:
The trick is to set the regional settings of the web to English (1033) prior to creating the field!
Remember to change the locale back to the original one after adding the field.