SharePoint Blog – René Hézser

Programmatically creating a SPFieldCalculated

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:

  1. make sure your referenced fieldnames are the display names and not the internal field names
  2. 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:

   1: SPWeb web = SPContext.Current.Web;
   2: SPFieldCollection fields = SPContext.Current.List.Fields;
   3:  
   4: int webLocaleId = web.Locale.LCID;
   5: web.Locale = new CultureInfo(1033);
   6: web.Update();
   7: try
   8: {
   9:     string newInternalFieldName = fields.AddFieldAsXml(newSchema);
  10: }
  11: catch (Exception ex)
  12: {
  13:     // do something
  14: }
  15: finally
  16: {
  17:     web.Locale = new CultureInfo(webLocaleId);
  18:     web.Update();
  19: }

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.

Tags:

2 comments for “Programmatically creating a SPFieldCalculated

  1. 29th December 2011 at 14:22

    Thank you very much for this post!

  2. 13th May 2014 at 13:13

    Cool and elegant solution.

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.