Creating a Custom Expiration Formula Based on Metadata in SharePoint 2010/SharePoint 2007 – Part 2

SharePoint Products and Technologies provide an extensible framework for implementing and extending policy features for information management policy. The existing policy features in Microsoft Office SharePoint Server 2007 provide functionality for document bar codes, document labels, auditing, and expiration.

In Creating a Custom Expiration Formula Based on Metadata in SharePoint 2010/SharePoint 2007 – Part 1, you learn how to extend the existing expiration policy feature in Office SharePoint Server 2007 by creating a policy resource and implementing a custom expiration formula. In this Microsoft Office Visual How To, you find out how to implement logic within the expiration formula to expire records based on information that is contained in a secondary list. The expiration is calculated based on a shared event among multiple records. When a project is defined as complete, all the related project records start their expiration based on the project completion date. This enables the project records to expire as a batch, and multiple expirations can be triggered based on a single action, such as project completion.

The scenario for this Visual How To involves retrieving information from a secondary list for use in the expiration formula. Specifically, the goal is to create a formula that causes project records to expire two years after the project completion date. The issue is that the project completion date is stored in a secondary list that contains information about the project and is not on the project records. Instead, the project records contain a metadata field that defines the project to which they are related. To correctly compute the expiration date, a custom formula is created to identify the project for the record, retrieve the project completion date from the project information list, and compute the correct expiration date.

Several items are set up in the records center for this example.

  • First, in a records center site, a custom list named Policy Info is created, and a DateTime field named Completion Date is added. The list is populated with the items shown in Table 1.
    Table 1. Policy Info list items

    Title Completion Date

    Project A12/31/2007

    Project B12/31/2004

  • Next, a library named Project Records is created, and a lookup field named Project is added. The Project field is set to retrieve information from the Policy Info list in the Title field.
  • Two documents are uploaded to the library. The Project field of one document is set to Project A, and the Project field of the second document is set to Project B.
  • Finally, the custom expiration formula from the example in Creating a Custom Expiration Formula Based on Metadata in SharePoint 2010/SharePoint 2007 – Part 1 is implemented, and an expiration policy that uses this formula is created on the Project Records library.

The logic for the custom expiration calculation is implemented in the ComputeExpireDate method of the CustomExpirationFormula class. In the method, the first step is to ensure that the record that is being processed contains a Project field that contains a value. If it does, you continue to retrieve the project completion date. To obtain this date, you determine which list the Project lookup field is using as a lookup list. You obtain a string representation of the GUID that identifies the lookup list by casting the Project field as an SPFieldLookup object and retrieving the LookupList property. You convert this string to a GUID and use it to retrieve the lookup list (the Project Info list).

The next step is to retrieve the corresponding item from the lookup list that contains the completion date for the project. The value of the Project lookup field contains the ID of the corresponding item in the Project Info list. You use this ID value to retrieve the corresponding item by passing it into the GetItemById method on the lookup list.

Finally, you retrieve the value of the Completion Date from the corresponding list item, and add two years. The method returns this value.

public Nullable<DateTime> ComputeExpireDate(SPListItem item, System.Xml.XmlNode parametersData)
DateTime? expirationDate = null;

// Make sure the project field has a value.
if (item["Project"] != null && (!string.IsNullOrEmpty(item["Project"].ToString())))

// Get the value from the corresponding list item.
using (SPWeb web = item.Web)

// Get the lookup list.
string lookupListId
= ((SPFieldLookup)item.Fields["Project"]).LookupList;
SPList lookupList
= web.Lists[new Guid(lookupListId)];

// Get the corresponding item in the lookup list.
SPFieldLookupValue lookupValue
= new SPFieldLookupValue(item["Project"].ToString());
SPListItem correspondingItem
= lookupList.GetItemById(lookupValue.LookupId);
if (correspondingItem["Completion Date"] != null
&& (!string.IsNullOrEmpty(correspondingItem["Completion Date"].ToString())))

// Get the completion date.
DateTime? completionDate
= Convert.ToDateTime(correspondingItem["Completion Date"].ToString());

// Add 2 years to the completion date.
expirationDate = completionDate.Value.AddYears(2);

return expirationDate;

The scenario used in this Microsoft Office Visual How To involves calculating the expiration date of a record based on information that is contained in a secondary list. A custom expiration formula is implemented to retrieve this information and use it when calculating the expiration date.

This scenario is also similar to determining the expiration of content based on an event that occurs. The event in this example is the completion of a project. Records related to a specific project do not have an expiration date until the project completion date is set. As soon as this event occurs, all related records start their retention period and must have their expiration date calculated.

The expiration date is calculated automatically by the Expiration timer job and also by event handlers that are automatically registered by Office SharePoint Server on the library. Recalculation is done by the event handlers that are automatically registered and fired when changes occur to the record, such when the record metadata is modified.

This raises an interesting problem with the event-based expiration scenario. The first time the project completion date is defined, the Expiration timer job calculates the expiration date on all related records because they do not have an expiration date set. However, any later changes to the completion date do not initiate recalculation on the related records. This is because the project completion date exists in a secondary list, and modifications to this date do not cause the recalculation event handlers to occur on the record.

You have several options available to handle this; however, the exact implementation is highly dependent on the situation. Because the expiration formula could potentially be executed many times, you must ensure that the code is highly efficient and does not do any unnecessary processing, such as instantiate unnecessary SPWeb objects or process duplicate items.

One way that you can trigger a recalculation efficiently is to make a change and update the policy that is associated with the content type or library. All items that use the policy are then marked as “dirty” and are reprocessed, having their expiration date recalculated.

SPList list = web.Lists["Project Records"];
SPContentType contentType = list.ContentTypes[0];
Policy policy = Policy.GetPolicy(contentType);

In the example, there is only one content type in the document library, which is based on Document. To update the policy, you retrieve this content type from the list, obtain the policy for the content type, and finally, update the policy.