Wednesday, 11 April 2012

Create a Calculated Field programmatically in SharePoint 2010 using XML

There are a few different blog posts on the web showing how to create a calculated field programmatically in SharePoint 2010, however, I ran into a specific scenario that I thought was worth sharing.
I had a situation where I was creating a number of fields and content types programmatically and needed to control the GUIDs of the fields as they were being provisioned across multiple site collections which I wanted to be consistent.
The first trick is finding out what the required schema should be when creating the field.  Now, if you want, you can refer to the following MSDN article that will go through how the schema is defined from which you can then work out what the calculated field schema should be:
http://msdn.microsoft.com/en-us/library/ms196289.aspx
Or, if you want a quicker, easier way to work out what it should be then you can create the calculated field in the UI in the way that you need, then knock up a quick console app to read the schema (or use SharePoint Manager 2010)


Example

For this demonstration I have created an Issue Tracking list, within which I have created a new column called “Complexity” which is a choice column defined as follows:

ComplexityDefinition
I now want to create the calculated column, called “Issue Factor”, that will represent the combination of the Priority (a pre-existing Issue Tracking field) and the newly created Complexity as a numerical value ranging from 1 (lowest) to 9 (highest).
ComplexityDefinition

The Calculation


Whilst this is not meant to be a post about all the various calculations available in SharePoint, it is worth just spending a quick paragraph on the calculation that has been used above:

=10-(RIGHT(LEFT(Priority,2),1)*RIGHT(LEFT(Complexity,2),1))

The purpose of the calculation is to extract the numerical value from the Complexity and Priority fields, multiply them together, then subtract them from 10 (so that a more complicated, high priority issue is a greater number than a lower one).
The MSDN article Calculated Field Formulas indicates that there is no MID function, therefore I have used a combination of RIGHT and LEFT to retrieve the numerical part of the value.

The Schema


To access the schema of the field I have created a simple Console Application that uses the SharePoint OM to view the properties of the field.
Note: Remember to change the “Platform Target” property for the Console Application to “x64” otherwise you will get an unpleasant and unhelpful message similar to that shown below
FileNotFoundException
The code to access this property is shown below:
static void Main(string[] args)
{
    try
    {
        using (SPSite site = new SPSite(ConfigurationManager.AppSettings["SiteCollectionURL"]))
        {
            using (SPWeb web = site.OpenWeb())
            {
                SPList issueList = web.Lists[ConfigurationManager.AppSettings["ListName"]];
                SPField issueFactor = issueList.Fields[ConfigurationManager.AppSettings["FieldName"]];
 
                string schemaXML = issueFactor.SchemaXml;
                Console.WriteLine(schemaXML);
            }
        }
    }
    catch (SPException spex)
    {
        Console.WriteLine(string.Format("{0}\r\n{1}", spex.Message, spex.StackTrace));
    }
 
    Console.ReadLine();
}

Using this code we can see that the Schema for the field is defined as:


<Field 
  Type="Calculated" 
  DisplayName="Issue Factor" 
  EnforceUniqueValues="FALSE" 
  Indexed="FALSE" 
  Format="DateOnly" 
  LCID="1033" 
  ResultType="Number" 
  ReadOnly="TRUE" 
  ID="{85353f64-8c8f-4424-ba81-bdc07b9880d6}" 
  SourceID="{b9c7c8c2-7106-4219-84f9-b3d7cd0a9760}" 
  StaticName="Issue_x0020_Factor" 
  Name="Issue_x0020_Factor" 
  ColName="sql_variant1" 
  RowOrdinal="0" 
  Required="FALSE" 
  Percentage="FALSE" 
  Version="3" 
  Decimals="0"
>
  <Formula>=10-(RIGHT(LEFT(Priority,2),1)*RIGHT(LEFT(Complexity,2),1))</Formula>
</Field>

The items to focus on in the above schema (and thus use when defining the field programmatically) are:

  • Type
  • Name
  • DisplayName
  • ID
  • ResultType
  • Decimals
  • Percentage
  • Formula

Note: If you had selected “Automatic” for the Number of Decimal Places property, there would be no Decimals attribute.

Thus, the resulting schema that we need to use when creating the field is:

<Field 
  Type="Calculated" 
  DisplayName="Issue Factor Programmatic" 
  ResultType="Number" 
  ID="{2BCF6739-3B59-4ABD-A4E8-C59729276FAB}" 
  Name="IssueFactorProgrammatic" 
  Percentage="FALSE" 
  Decimals="0"
>
  <Formula>=10-(RIGHT(LEFT(Priority,2),1)*RIGHT(LEFT(Complexity,2),1))</Formula>
</Field>

Where ID is a new GUID that I have generated and Name is string with no spaces (that is tidier than the previous “Issue_x0020_Factor” format).

Creating the Field Programmatically

Now, I would generally not recommend simply using a Console Application to actually create fields as it is not easy to track what changes have been made and when.  Instead, I would create a Feature with a Receiver and override the FeatureActivated method (and ideally the FeatureDeactivating method so you can undo the changes you have made).

Once you have created your SharePoint project within VS 2010, added your Feature and selected the “Add Event Receiver” you will be presented with the methods to be overridden and simply need to uncomment the appropriate methods.  The code for the FeatureActivated method is shown below:

public override void FeatureActivated(SPFeatureReceiverProperties properties)
{
    SPWeb web = properties.Feature.Parent as SPWeb;
    if (web == null)
        return;
 
    try
    {
        SPListCollection lists = web.Lists;
        var listsArray = lists.OfType<SPList>().Where(l => l.BaseTemplate == SPListTemplateType.IssueTracking).Select(l => l).ToArray<SPList>();
 
        
        for (int i = 0; i < listsArray.Length; i++)
        {
            SPList list = listsArray[i];
            string fieldName = list.Fields.AddFieldAsXml(CalculatedFieldFeature.Resources.IssueFactorXML);
            list.Update();
        }
 
        web.Update();
    }
    catch (Exception ex)
    {
        // Handle exception
    }
}

A couple of points about the above example:

  • I am applying the changes to all Issue Tracking lists by using a bit of LINQ to select from the Lists Collection.
  • CalculatedFieldFeature.Resources.IssueFactorXML is a string resource that I have added to the Feature Assembly
  • I have needed to use a for instead of a foreach loop to avoid errors resulting from modifying the collection during iteration.
Unfortunately, there is an issue with the above scenario that needs some modification due to a bug/quirk/feature of SharePoint.  For some reason, if the DisplayName attribute is different from the Name attribute, the Name attribute is overridden and becomes:

Issue_x0020_Factor_x0020_Program

Since it isn’t possible to create the field without a DisplayName attribute (an exception is thrown) we have to find an alternative.  Although it is a bit hacky, the approach I came up with is to modify the Title property of the SPField object after it has been created.  The above code example now becomes:

public override void FeatureActivated(SPFeatureReceiverProperties properties)
{
    SPWeb web = properties.Feature.Parent as SPWeb;
    if (web == null)
        return;
 
    try
    {
        SPListCollection lists = web.Lists;
 
        var listsArray = lists.OfType<SPList>().Where(l => l.BaseTemplate == SPListTemplateType.IssueTracking).Select(l => l).ToArray<SPList>();
 
        XDocument doc = XDocument.Parse(CalculatedFieldFeature.Resources.IssueFactorXML);
        XElement root = doc.Root;
        XAttribute displayNameAttr = root.Attribute("DisplayName");
        string displayName = displayNameAttr.Value.ToString();
        displayNameAttr.SetValue(root.Attribute("Name").Value);
 
        string schemaXML = root.ToString();
        
        for (int i = 0; i < listsArray.Length; i++)
        {
            SPList list = listsArray[i];
            string fieldName = list.Fields.AddFieldAsXml(schemaXML);
            list.Update();
 
            SPField field = list.Fields[fieldName];
            field.Title = displayName;
            field.Update();
        }
 
        web.Update();
    }
    catch (Exception ex)
    {
        // Handle exception
    }
}

Finally, now that the Name and DisplayName are being created as we originally intended, we can happily implement the FeatureDeactivating method which is shown below:

public override void FeatureDeactivating(SPFeatureReceiverProperties properties)
{
    SPWeb web = properties.Feature.Parent as SPWeb;
    if (web == null)
        return;
 
    try
    {
        SPListCollection lists = web.Lists;
 
        var listsArray = lists.OfType<SPList>().Where(l => l.BaseTemplate == SPListTemplateType.IssueTracking).Select(l => l).ToArray<SPList>();
        for (int i = 0; i < listsArray.Length; i++)
        {
            SPList list = listsArray[i];
            if (list.Fields.ContainsField("IssueFactorProgrammatic"))
            {
                SPField field = list.Fields.GetFieldByInternalName("IssueFactorProgrammatic");
                field.Delete();
            }
            list.Update();
        }
 
        web.Update();
    }
    catch (Exception ex)
    {
        // Handle exception
    }
}

What about Site Columns?

The approach highlighted above can also be used when creating Site Columns (i.e. creating a calculated Site Column based upon other Site Columns), however a small change to the XML Schema is required.  The XML shown above now becomes:

<Field
  Type="Calculated"
  DisplayName="Issue Factor Programmatic"
  ResultType="Number"
  ID="{5198652E-F7F9-473B-8BD6-5257061109AE}"
  Name="IssueFactorProgrammatic"
  StaticName="IssueFactorProgrammatic"
  Percentage="FALSE"
  Decimals="0"
>
  <Formula>=10-(RIGHT(LEFT(Priority,2),1)*RIGHT(LEFT(Complexity,2),1))</Formula>
  <FieldRefs>
    <FieldRef Name="Priority" />
    <FieldRef Name="Complexity" />
  </FieldRefs>
</Field>

The key difference above is the additional FieldRefs element that references all of fields used in the calculation using their InternalName.  The code example now becomes:

public override void FeatureActivated(SPFeatureReceiverProperties properties)
{
    SPWeb web = properties.Feature.Parent as SPWeb;
    if (web == null)
        return;
 
    try
    {
        XDocument doc = XDocument.Parse(CalculatedFieldFeature.Resources.IssueFactorXML);
        XElement root = doc.Root;
        XAttribute displayNameAttr = root.Attribute("DisplayName");
        string displayName = displayNameAttr.Value.ToString();
        displayNameAttr.SetValue(root.Attribute("Name").Value);
 
        string schemaXML = root.ToString();
        string fieldName = web.Fields.AddFieldAsXml(schemaXML);
        web.Update();
 
        SPField field = web.Fields[fieldName];
        field.Title = displayName;
        field.Update();
    }
    catch (Exception ex)
    {
        // Handle exception
    }
}

Summary

Creating a calculated field programmatically isn’t too complicated as other blogs have shown, however if you find yourself needing more control over how the field is created then use the AddFieldAsXml method of the SPFieldCollection class.

In addition, this method becomes particularly useful when creating Site Columns as you can reference the additional fields that are required with a simple modification to the schema.

If you are using simple DisplayNames (i.e. with no spaces), or you don’t care about the InternalName then this is fine, otherwise you will need to re-apply the DisplayName after the field is created.

If you would like to see the example code that I have used for this solution then leave your email address in the comments box and I will send it over to you.

Happy Coding!

2 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete