In a recent project, "the customer" needed a simple project planning mechanism. Microsoft Project was overkill for their needs, so a Task list was used, and as ContentTypes it had a.o. customized Tasks, Phases and Milestones.
The Phase had to include a phase number, and this number, together with the Title, was required to be available as a reference. For example, the first phase could be "Initiation", with number 1. The reference should be "1 Initiation". That is a simple request, just add a Number field, and a Calculated field that glues the number and title with a space between them. Right?
Not really. Deploying a calculated field all the way from a Site Column into a list instance on a site, using features, seemed impossible. On the internet forums and blogs, I only found questions, and not answers. I will present some of the answers related to Calculated Fields to you in this article.
The solution I created is built using 4 features that can be deployed and activated (or not). These 4 features relate to "steps" the calculated field had to go through:

1. A Field definition. This will appear in the Site Columns Gallery.
2. A ContentType definition, referencing this field. This ContentType will appear in the Site ContentTypes Gallery.
3. A List Definition, referencing this ContentType. This will appear on the "Create List" page inside all sites in the site collection.
4. A List Instance Definition, referencing this List Definition. This will be included in a web scoped feature. When the feature is activated, the list will be instantiated (created).

This is all documented pretty well (Microsoft has done well compared to their SharePoint 2003 documentation), except for "a couple of details". Calculated Fields and Lookup Fields are the most prominent trouble makers in my opinion, and many details I needed are not documented.

Note: This article is based on SharePoint 2010 and Visual Studio 2010, but will very likely apply to other versions of SharePoint. This article is written at an advanced level, for SharePoint developers who know how to "deploy" Fields, ContentTypes and Lists through XML files and features. It is not my goal to give you a "copy and paste" solution. Just insight and some code.

Article overview:
1    Defining the fields
2    Defining the Content Type
3    Defining the List Definition
4    Defining the List Instance
5    A long story short
6    Translating Calculated fields
7    Conclusion

Defining the fields

The Phase number field is straightforward:

<Field
  ID="{a8c7fe64-5791-416b-a837-26e93a3d056b}"
  Type="Number"
  Name="My_PhaseNr"
  DisplayName="Phase number"
  Required="TRUE"
  Min="1"
  Percentage="FALSE"
  Decimals="0"
  Group="My">
</Field>

The Phase Ref field is quite similar. Except the question: what field names should I use? I think this is straightforward too. As the fields here will be Site Columns, the stable name on these fields is the internal name. So I define the formula on Internal Names. The fields are not yet part of a list, so Display Names are not really available yet.

<Field
  ID="{b8581e64-01d1-4bef-9af9-06d190583664}"
  Type="Calculated"
  Name="My_PhaseRef"
  DisplayName="Phase ref"
  ResultType="Text"
  Group="My">
  <Formula>=CONCATENATE(My_PhaseNr," ",Title)</Formula>
  <FieldRefs>
    <FieldRef Name="My_PhaseNr" />
    <FieldRef Name="Title" />
  </FieldRefs>
</Field>

In my opinion, as programmer you KNOW what names you define on fields, Display Names are "pretty names" for the user (a very real requirement) and can change e.g. for localizations, so we don't want to use them in code nor formulae. But Internal Names are steady, so in formulae, especially those deployed in this technical low level XML-file format , I'd always expect Internal Names. But more on this later, this is not always true!

These Field definitions are placed in one or two Elements.xml file (I prefer to add all related field definitions in 1 file), add those in a feature etc. Just make sure the Number field is defined before the calculated field referencing it: SharePoint processed Element files in the order as defined in the feature, and content of the Element files in the order as presented in the file.

When you deploy this Feature, you would get indeed the two new Site Columns. If you'd open the edit page of the "Phase ref" site column, you would see that the formula shown is converted to show the Display Names:

=CONCATENATE([Phase number]," ",Title)

If you check the Field Schema with e.g. SharePoint Designer or the Server Explorer in SharePoint, you will see that the Field Schema still contains the Internal Names. The Field's Schema XML stored in the database is more or less the same as what you entered, with just a couple of default values are added. So somewhere between the database and the Field Editor page, the formula is converted to use display names. All Right, we know that there might be users that don't understand or won't like the GluedTogetherInternalNames (usually those names will be in English too).

However, if you try to update the field, you'd get an error like this:

In the log file, the following is logged:

System.Runtime.InteropServices.COMException: The formula refers to a column that does not exist. Check the formula for spelling mistakes or change the non-existing column to an existing column. at Microsoft.SharePoint.Library.SPRequestInternalClass.CallCalcEngine(Int32 operation, String bstrUrl, String bstrListName, String bstrString1, String bstrString2)

The CallCalcEngine call has a "bstrListName" parameter. Does the "calculation engine" require a list to function properly? If that is true, the error is to be expected, as the Site Column does not live inside a list! This is not documented, and (as the "CalcEngine" apparently lives inside a COM object) more difficult to examine.

Anyway, it appears to be impossible to edit Calculated Site Columns.

Defining the Content Type

This is also a pretty straightforward thing to do.

Ehrrr, in fact it is not really straightforward, but for this article I'll just stick to the "add a couple of fields" scenario, which appears straightforward. There are enough interesting and undocumented details in defining and deploying ContentTypes for a couple of more articles!

My ContentType just adds the 2 fields to a standard SharePoint Task (ContentType ID 0x0108). It also inherits all the standard Task fields (Inherits="TRUE").

<ContentType
  ID="0x010800D64E12CD4037CFD68E1DAB710EAAB0DF"      
  Name="Project Phase"
  Group="My"
  Description="Phase in the project planning"
  Inherits="TRUE"
  Version="0">
  <FieldRefs>
    <FieldRef ID="{a8c7fe64-5791-416b-a837-26e93a3d056b}" Name="My_PhaseNr"/>
    <FieldRef ID="{b8581e64-01d1-4bef-9af9-06d190583664}" Name="My_PhaseRef"/>
  </FieldRefs>
</ContentType>

Oh, and yes, a couple of remarks:

• SharePoint 2010 only checks for "TRUE" (all caps) as the value of the Inherits and Overwrites attributes.. The attribute values "True", "true" or anything else is considered as FALSE, i.e. the same as an absent attribute. (This is true for other attributes as well.)
• Do not add comments or anything but <FieldRef> and <RemoveFieldRef> elements inside the FieldRefs section! The code processing the FieldRefs stops when it encounters something different from an XML element inside this section
• <RemoveFieldRef> seems to be ignored completely in SharePoint 2010. I am not sure about this, but for me it does not work. (In 2007 it did)

Of course, calculated fields will only work if the referenced fields will be present on the list, so the referenced fields should be present in the ContentType definition. For the rest, nothing special here.

Defining the List Definition

Using the Add New Item / List Definition from Content Type in Visual Studio, this is fairly straightforward. I uncheck the "Add list instance for this list definition" box, as my list will be instantiated through another feature, not the feature containing the List Definition.

I call my List Definition "ProjectPlan", and get 2 files. After some minimal edits, they look like this:

Elements.xml:

<?xml version="1.0" encoding="utf-8" ?>
<Elements xmlns="http://schemas.microsoft.com/sharepoint/">
    <!-- Do not change the value of the Name attribute below. If it does not match the folder name of the List Definition project item, an error will occur when the project is run. -->
    <ListTemplate
        Name="ProjectPlan"
        Type="10007"
        BaseType="0"
        OnQuickLaunch="TRUE"
        SecurityBits="11"
        Sequence="360"
        DisplayName="Project Plan"
        Description="My Project Plan "
        Image="/_layouts/images/ittask.png"/>
</Elements>

Schema.xml (abbreviated – deleted sections indicated with […])

<?xml version="1.0" encoding="utf-8"?>
<List xmlns:ows="Microsoft SharePoint" Title="Project Plan" FolderCreation="FALSE" Direction="$Resources:Direction;" EnableContentTypes="TRUE" Url="Lists/ProjectPlan" BaseType="0" xmlns="http://schemas.microsoft.com/sharepoint/">
  <MetaData>
    <ContentTypes>
      <ContentType ID="0x010800D64E12CD4037CFD68E1DAB710EAAB0DF" Name="Project Phase" Group="My" Inherits="TRUE" Version="0">
        <FieldRefs>
          <FieldRef ID="{c042a256-787d-4a6f-8a8a-cf6ab767f12d}" Name="ContentType" />
          <FieldRef ID="{fa564e0f-0c70-4ab9-b863-0177e6ddd247}" Name="Title" />
[... lots of other field references ...]
          <FieldRef ID="{a8c7fe64-5791-416b-a837-26e93a3d056b}" Name="My_PhaseNr" />
          <FieldRef ID="{b8581e64-01d1-4bef-9af9-06d190583664}" Name="My_PhaseRef" />
        </FieldRefs>
      </ContentType>
    </ContentTypes>
    <Fields>
      <Field ID="{c3a92d97-2b77-4a25-9698-3ab54874bc6f}" Name="Predecessors" Type="LookupMulti" Mult="TRUE" Required="FALSE" DisplayName="$Resources:core,Tasks_Predecessors;" StaticName="Predecessors" List="Self" ShowField="Title" DisplaceOnUpgrade="TRUE" SourceID="http://schemas.microsoft.com/sharepoint/v3">
      </Field>
      <Field ID="{a8eb573e-9e11-481a-a8c9-1104a54b2fbd}" Type="Choice" Name="Priority" DisplayName="$Resources:core,Priority;" SourceID="http://schemas.microsoft.com/sharepoint/v3" StaticName="Priority">
        <CHOICES>
          <CHOICE>$Resources:core,Priority_High;</CHOICE>
          <CHOICE>$Resources:core,Priority_Normal;</CHOICE>
          <CHOICE>$Resources:core,Priority_Low;</CHOICE>
        </CHOICES>
[... lots and lots of other field definitions ...]
     <Field ID="{a8c7fe64-5791-416b-a837-26e93a3d056b}" Type="Number" Name="My_PhaseNr" DisplayName="Phase number" Required="TRUE" Min="1" Percentage="FALSE" Decimals="0" Group="My">
  </Field>
      <Field ID="{b8581e64-01d1-4bef-9af9-06d190583664}" Type="Calculated" Name="My_PhaseRef" DisplayName="Phase Ref" ShowInDisplayForm="FALSE" ResultType="Text" Format="DateOnly" ReadOnly="TRUE" Group="My">
    <Formula>=CONCATENATE(My_PhaseNr," ",Title)</Formula>
    <FieldRefs>
      <FieldRef Name="My_PhaseNr" />
      <FieldRef Name="Title" />
    </FieldRefs>
  </Field>
    </Fields>
    <Views>
      [...]
  </MetaData>
</List>

If I go through the schema, I recognize all my bits, and there is a whole lot more. My Calculated Field has still its internal names. That looks right! But why is this all copied in here, and not referenced? Again couple of thoughts here:

• The ContentType is copied into the List Schema.xml. This means that you would need to make changes in 2 places! That is not nice! Luckily, you can replace your <ContentType> element, including all its contents, with a <ContentTypeRef>.
• All Field definitions (Site Column definitions) are copied. So I'd need to maintain those in two places too? No, luckily not! I can use the ContentTypeRef element. But beware, there is a little problem in some versions of SharePoint, I need to "Prime" my ContentType(s). Charles Chen (charliedigital) has written a great article about this in http://charliedigital.com/2011/02/18/most-annoying-thing-about-sharepoint-2010/ . I won't repeat this here.

So, using Charles' fix, the above schema can be simplified to (again, abbreviated, but now I've only cut the Views section):

<?xml version="1.0" encoding="utf-8"?>
<List xmlns:ows="Microsoft SharePoint" Title="My - ProjectPlan" FolderCreation="FALSE" Direction="$Resources:Direction;" EnableContentTypes="TRUE" Url="Lists/My- ProjectPlan" BaseType="0" xmlns="http://schemas.microsoft.com/sharepoint/">
  <MetaData>
    <ContentTypes>
      <!-- Project Phase -->
      <ContentTypeRef ID="0x010800D64E12CD4037CFD68E1DAB710EAAB0DF" />
    </ContentTypes>
    <Fields>
    </Fields>
    <Views>
      [...]
  </MetaData>
</List>

Notice the simple and clean ContentTypeRef element, and the empty <Fields> element! Thanks Charles!

This works well. For most field types, that is. It does not work for Calculated Fields. Sigh. More about this further on.

Defining the List Instance

Again, using the Add New Item dialog in Visual Studio, adding a list instance is straight forward. All I need to do is add a reference to the feature where my List Definition is in.

<?xml version="1.0" encoding="utf-8"?>
<Elements xmlns="http://schemas.microsoft.com/sharepoint/">
  <ListInstance Title="Project Plan"
                OnQuickLaunch="TRUE"
                TemplateType="10007"
                FeatureId="45e73274-2700-4a40-a217-af6a604d02ea" 
                Url="Lists/ProjectPlan"
                Description="Project Plan">
  </ListInstance>
</Elements>

So why do I need the FeatureId here? I think of it like this: the Template ID points to the List Template ID (the "Type" attribute in the List Definition's Element.xml). But other developers could use the same ID for a List Definition. Together with the feature ID, it should form a unique key to the List Definition. The Feature Id can be found in Visual Studio if you open the Feature Editor (just to make sure: the feature where the List Definition is in), and press F4. The property sheet is opened, and then you can copy the Feature ID and add it in the List Instance Element.xml file.

Deploy and activate this feature. Does it work? Yes and no. The number field is in, the calculated field didn't make it.

A long story short

Internally, the Feature activation code for List Instances utilizes the SPList.Fields.AddFieldAsXml() function. For calculated fields, this requires Display Names!

Yes, that is correct. Display names in the Field's schema XML. And our Site Column needed Internal Names.

In short: The Calculated Site Columns are only accepted with formulae using Internal Names, List Columns only using Display Names.

Of course, between the UI and the database, the Display Names need to be converted to Internal Names, but i.m.h.o. this should be done very close to the UI, and not somewhere deep in the SPFieldCollection or lower. Microsoft, why do you make us suffer!?

So this is why it seems to be impossible to deploy Calculated fields using feature definitions?

Translating Calculated fields

The solution.Microsoft fixes this. In the mean time, what should happen is that, just before the field is added to the list, the Internal Names in the Site Column are translated to Display Names in the Formula. I've found no events that are triggered during feature activation in SharePoint. So we are stuck with the Field missing in the list.

What I found is that I needed to do 2 things.

1. Copy the Calculated Field Definition to the List Definition schema.xml (Internal Names). Yes, even using the Metadata Primer from Charles, I found I needed this.
2. Add a FeatureActivated event handler to add a fixed version of the calculated field to the list (translated to Display Names)

Surely, somewhere in SharePoint there must be the code that translates formulae between Internal Names and Display Names: this is what happens in the UI. Searching the documentation revealed nothing. Digging through the classes using VS' Intellisense nothing either. Digging deeper using reflection revealed that there are methods for this on the SPFieldCalculated class, but they are marked internal! Why is this so secret?

This is the method I need to convert a formula:

SPFieldCalculated.DisplayFormulaFromListFields(SPFieldCollection);

But Microsoft in its wisdom decided that no-one outside the SharePoint DLLs needed this method, ever.

On the SPList class, there is a ProvisionField function that also translates the calculated field formula for us. Great! Oh, Microsoft also marked this as internal. (Hmmm. Why doesn't the List Instance activator utilize this method!?)

So I have two options:

1. Write my own parser, translator, etc. Costly!
2. Go the undocumented and unsupported way, and use reflection to link to the internal method.

A bit of code for option 2 is shown below. It would go in the Feature Event Receiver of the Feature containing the List Instance. Note that this code is both generalised to be "one solution fits all calculated fields", and at the same time simplified just to show the mechanics. You need to add more error handling and reporting!

public override void FeatureActivated(SPFeatureReceiverProperties properties)
{
   SPWeb web = properties.Feature.Parent as SPWeb;
   FixCalculatedFields(web.Lists["Project Plan"]);  
}


public void FixCalculatedFields(SPList list)
{
    if (list.ContentTypesEnabled)
    {
        SPFieldCollection availableFields = list.ParentWeb.AvailableFields;
        Dictionary<Guid, bool> dictAdd = null;
        MethodInfo miProvisionField = null;
        foreach (SPContentType ct in list.ContentTypes)
        {
            foreach (SPField fld in ct.Parent.Fields)
            {
                SPFieldCalculated calcFld = fld as SPFieldCalculated;
                if (calcFld != null)
                {
                    // Safety check, field must be in site columns.
                    // Note: Could add another safety check, field must not yet be 
                    // on the list nor on the ContentType
                    if (availableFields.Contains(fld.Id))
                    {
                        if (miProvisionField == null)
                            miProvisionField = list.GetType().GetMethod("ProvisionField", BindingFlags.NonPublic | BindingFlags.Instance);
                        if (dictAdd == null)
                            dictAdd = new Dictionary<Guid, bool>();
                        if (miProvisionField != null)
                        {
                            try
                            {
                                miProvisionField.Invoke(list, new object[] { fld, SPAddFieldOptions.AddToNoContentType, true, false, dictAdd });
                            }
                            catch (Exception ex)
                            {
                                ULS.Error(String.Format("Cannot update calculated field {0} in list {1}", fld.Title, list.Title), ex);
                            }
                        }
                        else
                        {
                            ULS.Warning("Could not access the method SPList.ProvisionField()");
                        }
                    }
                }
            }
        }
    }
}

Conclusion

Deploying Calculated fields can seem like a nightmare, and much can indeed go wrong. But it is not impossible if you go "unsupported" (using reflection) or do "hard work" (write a parser).

All of this work is needed just because of some design decisions from Microsoft:

• have 2 types of formulae in field xml schema (or: wrong place in the "stack" for converting a formula from display names to internal names (i.m.h.o. a real design flaw))
• have an inflexible field processor that cannot accept both Internal Names and Display Names

And besides that, why is it that many useful, strong and stable classes and methods are marked as internal?

It took me long, long hours to discover all this, I hope this will serve the community. If you find any improvements, please let me know! In particular, I would like to think that using the <FieldRefs> section in the Calculated Field schema would allow the formula parser to be smarter than what I encountered. Could attributes of the FieldRef elements help the parser?

Cheers!

Alex Hekstra

update: Lee sent me a snippet using <FormulaDisplayNames>, a tag that appears to be included in SP 2010. I find it sad that Microsoft decided not to solve the real problem, but perhaps this helps some:

<?xml version="1.0" encoding="utf-8"?>
<Elements xmlns="http://schemas.microsoft.com/sharepoint/">
  <Field
    Type="Calculated"
    DisplayName="My Calculated Field"
    EnforceUniqueValues="FALSE"
    Indexed="FALSE"
    Format="DateOnly"
    LCID="1033"
    ResultType="Text"
    ReadOnly="TRUE"
    Group="My Custom Group"
    ID="{835d7674-f54e-4196-ae0b-fa67edca1250}"
    StaticName="MyCalculatedField"
    Name="MyCalculatedField">
    <Formula>=OtherField</Formula>
    <FormulaDisplayNames>=[Other Field]</FormulaDisplayNames>
    <FieldRefs>
      <FieldRef Name="OtherField" />
    </FieldRefs>
  </Field>
</Elements