(For an introduction to Retention Policies in SharePoint 2010, please see my earlier entry, "RM Basics: Retention")

Out of the box, SharePoint only provides retention based on a few options based on system metadata:

  • Create Date
  • Last Modified
  • Date Declared as Record

Unfortunately, most organizations with a retention policy and schedule of reasonable maturity will likely have other ways to calculate retention for their records, such as Fiscal Year (which may or may not be this year) or Contract Expiration Date, which will already require some level of customization.

The most notorious of all is event-based retention, which is the idea that some record retention periods will be calculated off some date in the future, yet to be determined. One example is an employee document, whose retention period might be "The termination date of the employee + 7 years." Unless you can see into the future (or perhaps know something this poor employee doesn’t) this can one of the trickiest components to design in a Records Management solution. This is a very common issue in records management, and one of the most complicated to define in an automated system.

As far as I know, there are only two primary ways to approach this (but if anyone out there has other ideas, please let me know!):

The Shortcut

By far the easiest way to support this is to have users hold off on declaring the record until the event actually occurs. Of course, right? If you can’t properly enforce a policy, just change the policy!

If you declare the record on the day the event occurs, then the event-based retention is the same as the create date retention. Easy as pie and cheap as hell to implement.

As another alternative, you can put off declaration until the event date is known, capture that date through a custom form or through metadata, and base the retention off that date. You’ll still have to create a custom retention policy in order to use a non-system metadata to calculate retention, but it’s still relatively easy to implement.

By the way, if any Microsoft employees are reading this site, in my opinion it would be an easy win, in the next version of SharePoint, to allow retention policies to be based on a custom column. This seems pretty easy to provide, and would open up a whole host of retention options, such as through workflow, without having to create a custom retention policy.

The Scenic Route

But of course that level of policy change just to accommodate the limitations of a technology platform is enough to make a records manager want to jump out of a window, and isn’t feasible for every organization.

It gets especially complicated if you have several documents tied to the same event. For instance, you may have hundreds of evidence documents all waiting on the same investigation to close before kicking off retention. No one wants to have to update each of those documents in order to properly enforce retention.

Instead, one alternative is to tie the document to a SharePoint list of "events" that each contain an "event date" that remains blank at first.

Let’s look at an example:









In this example, we have three events: the date the official policy document is superseded, the date Justin Ong leaves the company, and the date a SEC investigation closes. For the policy document, let’s say that we know that we are planning to release a newer version of the existing policy document on January 1st of next year. We can go ahead and input the event date as 1/1/2013, and that the retention for the current version of the document should begin on that day. For the SEC investigation, we know that the investigation just ended earlier in February, so we input the event date as 2/1/2012. All retentions based on that event will be calculated on that date. For the Justin Ong date, because we do not know when he will leave the company, we will leave the date blank for now. All retentions based on that event are still indefinite. Please note that the EventID field for this solution must be unique.

Now, in the actual document environment, all documents that require event-based retention now need to have two additional fields: EventID and ExpirationDate.













So in this document library, we have three document each tied to the EventIDs that were previously mentioned. When each document is declared as record, it must be associated with the EventID from the other list, which is why they need to be unique.

Open Issue: How do you associate the document with an EventID? It’s possible to just use a lookup column to tie to the other list, however you may have hundreds, if not thousands, of events at any time. Sorting through them all would be cumbersome. A custom form or filter may be required, depending on your needs.

After the documents are declared as records, their ExpirationDate field is blank. Some process, possibly a timer job, must go through and find all the documents with no expiration date, and match the EventID to the events list. If the matching event has a non-blank EventDate, the process or job can use that date to calculate the expiration date and write it to the ExpirationDate field.

The retention policy applied to each document simply needs to point to that ExpirationDate field and expire appropriately, or retain the document indefinitely if the ExpirationDate field is blank.

Again, this is just one concept on how to apply event-based retention. Ultimately, organizations who implement a solution like this do get to apply retention to their documents (on the plus side), but now have to have additional processes in place to manage the events list.