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:
- 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:
1: SPWeb web = SPContext.Current.Web;
<pre style="padding-right:0px;padding-left:0px;font-size:8pt;padding-bottom:0px;margin:0em;overflow:visible;width:100%;color:black;border-top-style:none;line-height:12pt;padding-top:0px;font-family:consolas, 'Courier New', courier, monospace;border-right-style:none;border-left-style:none;background-color:#f4f4f4;border-bottom-style:none"><span style="color:#606060"> 2:</span> SPFieldCollection fields = SPContext.Current.List.Fields;</pre>
<pre style="padding-right:0px;padding-left:0px;font-size:8pt;padding-bottom:0px;margin:0em;overflow:visible;width:100%;color:black;border-top-style:none;line-height:12pt;padding-top:0px;font-family:consolas, 'Courier New', courier, monospace;border-right-style:none;border-left-style:none;background-color:white;border-bottom-style:none"><span style="color:#606060"> 3:</span> </pre>
<pre style="padding-right:0px;padding-left:0px;font-size:8pt;padding-bottom:0px;margin:0em;overflow:visible;width:100%;color:black;border-top-style:none;line-height:12pt;padding-top:0px;font-family:consolas, 'Courier New', courier, monospace;border-right-style:none;border-left-style:none;background-color:#f4f4f4;border-bottom-style:none"><span style="color:#606060"> 4:</span> <span style="color:#0000ff">int</span> webLocaleId = web.Locale.LCID;</pre>
<pre style="padding-right:0px;padding-left:0px;font-size:8pt;padding-bottom:0px;margin:0em;overflow:visible;width:100%;color:black;border-top-style:none;line-height:12pt;padding-top:0px;font-family:consolas, 'Courier New', courier, monospace;border-right-style:none;border-left-style:none;background-color:white;border-bottom-style:none"><span style="color:#606060"> 5:</span> web.Locale = <span style="color:#0000ff">new</span> CultureInfo(1033);</pre>
<pre style="padding-right:0px;padding-left:0px;font-size:8pt;padding-bottom:0px;margin:0em;overflow:visible;width:100%;color:black;border-top-style:none;line-height:12pt;padding-top:0px;font-family:consolas, 'Courier New', courier, monospace;border-right-style:none;border-left-style:none;background-color:#f4f4f4;border-bottom-style:none"><span style="color:#606060"> 6:</span> web.Update();</pre>
<pre style="padding-right:0px;padding-left:0px;font-size:8pt;padding-bottom:0px;margin:0em;overflow:visible;width:100%;color:black;border-top-style:none;line-height:12pt;padding-top:0px;font-family:consolas, 'Courier New', courier, monospace;border-right-style:none;border-left-style:none;background-color:white;border-bottom-style:none"><span style="color:#606060"> 7:</span> <span style="color:#0000ff">try</span></pre>
<pre style="padding-right:0px;padding-left:0px;font-size:8pt;padding-bottom:0px;margin:0em;overflow:visible;width:100%;color:black;border-top-style:none;line-height:12pt;padding-top:0px;font-family:consolas, 'Courier New', courier, monospace;border-right-style:none;border-left-style:none;background-color:#f4f4f4;border-bottom-style:none"><span style="color:#606060"> 8:</span> {</pre>
<pre style="padding-right:0px;padding-left:0px;font-size:8pt;padding-bottom:0px;margin:0em;overflow:visible;width:100%;color:black;border-top-style:none;line-height:12pt;padding-top:0px;font-family:consolas, 'Courier New', courier, monospace;border-right-style:none;border-left-style:none;background-color:white;border-bottom-style:none"><span style="color:#606060"> 9:</span> <span style="color:#0000ff">string</span> newInternalFieldName = fields.AddFieldAsXml(newSchema);</pre>
<pre style="padding-right:0px;padding-left:0px;font-size:8pt;padding-bottom:0px;margin:0em;overflow:visible;width:100%;color:black;border-top-style:none;line-height:12pt;padding-top:0px;font-family:consolas, 'Courier New', courier, monospace;border-right-style:none;border-left-style:none;background-color:#f4f4f4;border-bottom-style:none"><span style="color:#606060"> 10:</span> }</pre>
<pre style="padding-right:0px;padding-left:0px;font-size:8pt;padding-bottom:0px;margin:0em;overflow:visible;width:100%;color:black;border-top-style:none;line-height:12pt;padding-top:0px;font-family:consolas, 'Courier New', courier, monospace;border-right-style:none;border-left-style:none;background-color:white;border-bottom-style:none"><span style="color:#606060"> 11:</span> <span style="color:#0000ff">catch</span> (Exception ex)</pre>
<pre style="padding-right:0px;padding-left:0px;font-size:8pt;padding-bottom:0px;margin:0em;overflow:visible;width:100%;color:black;border-top-style:none;line-height:12pt;padding-top:0px;font-family:consolas, 'Courier New', courier, monospace;border-right-style:none;border-left-style:none;background-color:#f4f4f4;border-bottom-style:none"><span style="color:#606060"> 12:</span> {</pre>
<pre style="padding-right:0px;padding-left:0px;font-size:8pt;padding-bottom:0px;margin:0em;overflow:visible;width:100%;color:black;border-top-style:none;line-height:12pt;padding-top:0px;font-family:consolas, 'Courier New', courier, monospace;border-right-style:none;border-left-style:none;background-color:white;border-bottom-style:none"><span style="color:#606060"> 13:</span> <span style="color:#008000">// do something</span></pre>
<pre style="padding-right:0px;padding-left:0px;font-size:8pt;padding-bottom:0px;margin:0em;overflow:visible;width:100%;color:black;border-top-style:none;line-height:12pt;padding-top:0px;font-family:consolas, 'Courier New', courier, monospace;border-right-style:none;border-left-style:none;background-color:#f4f4f4;border-bottom-style:none"><span style="color:#606060"> 14:</span> }</pre>
<pre style="padding-right:0px;padding-left:0px;font-size:8pt;padding-bottom:0px;margin:0em;overflow:visible;width:100%;color:black;border-top-style:none;line-height:12pt;padding-top:0px;font-family:consolas, 'Courier New', courier, monospace;border-right-style:none;border-left-style:none;background-color:white;border-bottom-style:none"><span style="color:#606060"> 15:</span> <span style="color:#0000ff">finally</span></pre>
<pre style="padding-right:0px;padding-left:0px;font-size:8pt;padding-bottom:0px;margin:0em;overflow:visible;width:100%;color:black;border-top-style:none;line-height:12pt;padding-top:0px;font-family:consolas, 'Courier New', courier, monospace;border-right-style:none;border-left-style:none;background-color:#f4f4f4;border-bottom-style:none"><span style="color:#606060"> 16:</span> {</pre>
<pre style="padding-right:0px;padding-left:0px;font-size:8pt;padding-bottom:0px;margin:0em;overflow:visible;width:100%;color:black;border-top-style:none;line-height:12pt;padding-top:0px;font-family:consolas, 'Courier New', courier, monospace;border-right-style:none;border-left-style:none;background-color:white;border-bottom-style:none"><span style="color:#606060"> 17:</span> web.Locale = <span style="color:#0000ff">new</span> CultureInfo(webLocaleId);</pre>
<pre style="padding-right:0px;padding-left:0px;font-size:8pt;padding-bottom:0px;margin:0em;overflow:visible;width:100%;color:black;border-top-style:none;line-height:12pt;padding-top:0px;font-family:consolas, 'Courier New', courier, monospace;border-right-style:none;border-left-style:none;background-color:#f4f4f4;border-bottom-style:none"><span style="color:#606060"> 18:</span> web.Update();</pre>
<pre style="padding-right:0px;padding-left:0px;font-size:8pt;padding-bottom:0px;margin:0em;overflow:visible;width:100%;color:black;border-top-style:none;line-height:12pt;padding-top:0px;font-family:consolas, 'Courier New', courier, monospace;border-right-style:none;border-left-style:none;background-color:white;border-bottom-style:none"><span style="color:#606060"> 19:</span> }</pre>
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.