Sharepoint Information Management Policy – Retention 21

Mike BerrymanI have a client who stores various time-sensitive documents about their customers in a document library in sharepoint.  These documents are only valid for a year, at which point they have to be updated with new information.  The client keeps track of when a document is to become outdated with a field on the document library called “Effective Date”.

A little over a year ago, the client had set up an information management policy on the document library with a retention policy defined so they could keep track of any documents that were nearing expiration.  This retention policy consisted of multiple stages, with a different workflow being triggered at each stage that would construct an email with pertinent information regarding the status of the document and email it out to relevant recipients with instructions that action was needed.

As anyone who’s worked with retention in an information management policy in sharepoint knows, Microsoft’s intention for retention is to basically keep content fresh and updated.  The retention stages can perform various tasks on a document that meets that stage’s criteria, but most of those tasks involve deleting or moving the document to another location.  With a little research and fiddling around, it’s easy to see that Microsoft designed the retention portion of information management policies to only execute each stage once and only once for each document it runs against.

This caused trouble for my client.  They had designed their document library and retention policies with the intention that users would update their documents with fresh information and at the same time supply a new Effective Date.  They had thought (justifiably so) that since their retention stages were based off of the Effective Date field that updating the Effective Date would, in effect, recalculate where in the retention stages the document was.  So if they updated the document’s Effective Date such that the new Effective Date meant the first retention stage wasn’t applicable yet, they expected that as soon as the first stage’s criteria was met again that the first stage would trigger and the process to keep the document fresh would start anew.

As documents started requiring updates this year (a year after they initially set up the retention stages), they discovered that changing the Effective Date did not, in fact, “reset” the retention stages to whatever stage was now applicable.  They did not want to abandon their various workflows they had created for this retention so I was approached to find a way to get the retention working the way they had originally thought it worked.

What a challenge this turned out to be…

In my testing, I found there are a couple fields that are created on the document library once retention policies are defined.  These are as follows:

Field Internal Name Description
Original Expiration Date _dlc_ExpireDateSaved Normally not used.  Seems to keep track of whatever date was in the Expiration Date hidden field if the document is marked as Exempt from Policy (_dlc_Exempt gets set to true)
Exempt From Policy _dlc_Exempt A bit field.  Used as a flag to tell retention that this item is exempt from retention processing.
Expiration Date _dlc_ExpireDate The key field.  This is the calculated date of the next retention stage becoming applicable. If the current date is on or after this date the next time the Expiration Policy timer job runs, the next retention stage for the item is triggered.

The _dlc_ExpireDate field is the important field for this purpose.  The retention policy for an item gets checked whenever the Expiration Policy timer job is triggered (by default, once a week, but my client had bumped theirs up to run every day).  If the Expiration Date field for an item matches today or a date in the past, the Expiration Policy job knows to process the next retention stage for item and then calculate when the retention stage after the one that just got executed is scheduled to run.

So setting the _dlc_ExpireDate field is all that should need to be done, right?  Not quite… it turns out there are more pieces of data on each item.  Specifically, data to keep track of the retention stages.  This data is not stored as hidden fields on the item, but instead are stored as properties in the properties hashtable of the item.  They are as follows:

Property Internal Name Description
Item Stage Id _dlc_ItemStageId This property keeps track of the last stage that was applicable to the item.
Last Run _dlc_LastRun Keeps track of the last time the retention stage was executed.
Policy Id _dlc_PolicyId This seems to hold the unique ID of the retention policy defined for the item.
Item Retention Formula ItemRetentionFormula Holds a snippet of XML that defines the calculation used to determine when the next retention stage

is to be triggered.

It turns out the 2 important properties from these are the _dlc_ItemStageId and ItemRetentionForumla properties.

From my testing, I found that I could “reset” the entire retention policy for an item back to its first stage by merely removing the Item Stage Id and Item Retention Formula properties from the list item and then updating the list item.  This seemed to cause some processing for the item that would re-calculate the Expiration Date using the retention formula from the first stage and then once that date was hit, the first stage would be triggered again.

For most cases, this would satisfy the requirements.  In my case, however, the client needed this reset process to be able to handle a new Effective Date that would place the item anywhere within the retention policy’s stages.  So, for instance, if a new Effective Date was supplied that would put the item between stage 2 and 3 in the retention stages then this solution had to set up the item such that the next stage to run would be the 3rd stage as soon as the Expiration Date was hit.

This is not as simple as just removing the 2 properties above and letting Sharepoint handle the rest.

What I opted to do was create an event receiver on the document library that would trigger whenever an item was updated.  If the Effective Date has been changed, then the event receiver would re-calculate all the retention information.  Below is the event receiver’s code.

private const string _fieldName = "Effective_x0020_Date";
public override void ItemUpdated(SPItemEventProperties properties)
{
    bool eventFiringEnabledOldValue = base.EventFiringEnabled;
    base.EventFiringEnabled = false;

    try
    {
        string beforeEffectiveDate = properties.BeforeProperties[_fieldName] == null ?
            string.Empty : properties.BeforeProperties[_fieldName].ToString();
        string afterEffectiveDate = properties.AfterProperties[_fieldName] == null ?
            string.Empty : properties.AfterProperties[_fieldName].ToString();
        if (!string.IsNullOrEmpty(afterEffectiveDate))
        {
            DateTime dtBefore = DateTime.MinValue;
            if (!string.IsNullOrEmpty(beforeEffectiveDate))
                dtBefore = DateTime.ParseExact(beforeEffectiveDate, "yyyy-MM-dd\\THH:mm:ss\\Z",
                                               CultureInfo.InvariantCulture);
            DateTime dtAfter = DateTime.ParseExact(afterEffectiveDate, "yyyy-MM-dd\\THH:mm:ss\\Z",
                                                   CultureInfo.InvariantCulture);
            if (string.IsNullOrEmpty(beforeEffectiveDate) || dtAfter.Date != dtBefore.Date)
            {
                PolicyItem pi = null;
                foreach (SPContentType contentType in properties.List.ContentTypes)
                {
                    Policy policy = Policy.GetPolicy(contentType);
                    if (policy != null && policy.Items.Count > 0)
                        pi = policy.Items[0];
                }
                int stageToSet = 0;
                string itemRetentionFormula = string.Empty;
                bool doResetWorkflowProperties = false;
                DateTime dtExpirationDateToSet = DateTime.MaxValue;
                if (pi != null)
                {
                    try
                    {
                        XDocument xDoc = XDocument.Parse(pi.CustomData);
                        var data = xDoc.Descendants("data").Select(n => n);
                        foreach (var datum in data)
                        {
                            int stage = (int)datum.Attribute("stageId");
                            var formula = datum.Element("formula");
                            string property = (string)formula.Element("property");
                            string period = (string)formula.Element("period");
                            int number = (int)formula.Element("number");

                            //check if this stage is valid for this item
                            string propertyVal = properties.AfterProperties[property] == null ?
                                string.Empty : properties.AfterProperties[property].ToString();
                            if (!string.IsNullOrEmpty(propertyVal))
                            {
                                DateTime dtProperty;
                                if (DateTime.TryParseExact(propertyVal, "yyyy-MM-dd\\THH:mm:ss\\Z",
                                                           CultureInfo.InvariantCulture, DateTimeStyles.None,
                                                           out dtProperty))
                                {
                                    doResetWorkflowProperties = true;
                                    DateTime dtStageExpiration = DateTime.MaxValue;
                                    switch (period.ToLower())
                                    {
                                        case "years":
                                            dtStageExpiration = dtProperty.AddYears(number);
                                            break;
                                        case "months":
                                            dtStageExpiration = dtProperty.AddMonths(number);
                                            break;
                                        case "days":
                                            dtStageExpiration = dtProperty.AddDays(number);
                                            break;
                                        default:
                                            break;
                                    }
                                    if (stage == 1)
                                    {
                                        dtExpirationDateToSet = dtStageExpiration;
                                        itemRetentionFormula = formula.ToString();
                                    }
                                    if (dtStageExpiration.Date <= DateTime.Now.Date)
                                    {
                                        stageToSet = stage;
                                        dtExpirationDateToSet = dtStageExpiration;
                                        itemRetentionFormula = formula.ToString();
                                    }
                                }
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                    }
                }
                if (doResetWorkflowProperties)
                {
                    //we were able to successfully navigate the stages and 
                    //determine dates of expiration
                    stageToSet = stageToSet - 1;//when processing occurs, the system actually advances
                                                //the stage to the desired stage, so we need to back up one stage
                    int itemId = properties.ListItem.ID;
                    SPListItem item = properties.List.GetItemById(itemId);

                    if (stageToSet < 1)
                        item.Properties.Remove("_dlc_ItemStageId");
                    else
                        item.Properties["_dlc_ItemStageId"] = stageToSet;
                    itemRetentionFormula = itemRetentionFormula.Replace("\r\n", "").Replace(" ", "");
                    item.Properties["ItemRetentionFormula"] = itemRetentionFormula;
                    item["_dlc_ExpireDate"] = dtExpirationDateToSet;
                    item.Update();
                }
            }
        }
    }
    catch (Exception ex)
    {
    }

    base.EventFiringEnabled = eventFiringEnabledOldValue;
    base.ItemUpdated(properties);
}

The very first thing it does it check if there even is an Effective Date anymore after the item has been updated.  If there isn’t, there obviously isn’t anything to do with the item and we’re done.  If there is an effective date and it’s been changed, however, then the fun begins.  A side note, you’ll notice that in translating the dates into a DateTime object, DateTime.ParseExact() is being used with a custom format.  Dates from the BeforeProperties or AfterProperties in an event receiver are formatted differently from normal, so translating them into a DateTime object requires a custom format string.

As soon as it is determined that this item’s retention needs to be examined we need to actually get the retention and it’s stages.  Luckily my client had only one information management policy defined for this document library, so I just needed to find the only policy defined across all content types for this document library.  All the information about the retention stages is stored in the CustomData property of the PolicyItem object.  It’s stored as XML, so the event receiver is using Linq to XML in order to parse out the relevant information.  The XML will look something like this:

<Schedules nextStageId="5">
    <Schedule type="Default">
        <stages>
            <data stageId="1">
                <formula id="Microsoft.Office.RecordsManagement.PolicyFeatures.Expiration.Formula.BuiltIn">
                    <number>335</number>
                    <property>Effective_x0020_Date</property>
                    <propertyId>6c655a0b-a1eb-4ad7-b3a5-b4fe9ad8266d</propertyId>
                    <period>days</period>
                </formula>
                <action type="workflow" id="538dc011-d7d9-4c18-8c03-7987a5eb2025" />
            </data>
            <data stageId="2">
                <formula id="Microsoft.Office.RecordsManagement.PolicyFeatures.Expiration.Formula.BuiltIn">
                    <number>360</number>
                    <property>Effective_x0020_Date</property>
                    <propertyId>6c655a0b-a1eb-4ad7-b3a5-b4fe9ad8266d</propertyId>
                    <period>days</period>
                </formula>
                <action type="workflow" id="453995a8-aab2-413c-bdf0-5e01d2325290" />
            </data>
            <data stageId="3">
                <formula id="Microsoft.Office.RecordsManagement.PolicyFeatures.Expiration.Formula.BuiltIn">
                    <number>1</number>
                    <property>Effective_x0020_Date</property>
                    <propertyId>6c655a0b-a1eb-4ad7-b3a5-b4fe9ad8266d</propertyId>
                    <period>years</period>
                </formula>
                <action type="workflow" id="6f6a9c98-4c95-4437-9857-d8b3d225b93f" />
            </data>
            <data stageId="4">
                <formula id="Microsoft.Office.RecordsManagement.PolicyFeatures.Expiration.Formula.BuiltIn">
                    <number>367</number>
                    <property>Effective_x0020_Date</property>
                    <propertyId>6c655a0b-a1eb-4ad7-b3a5-b4fe9ad8266d</propertyId>
                    <period>days</period>
                </formula>
                <action type="workflow" id="df2ae27c-8e00-422b-9c43-59975d50a81a" />
            </data>
        </stages>
    </Schedule>
</Schedules>

All the information necessary for each stage to calculate if that stage is applicable yet or not is contained within this XML, so with it the event receiver can calculate which stage the item is currently on.  It uses the property’s name from the retention stage, but you could just as easily use the property’s Id to get the data and do your calculations.  You’ll notice that as the event receiver is looping through each retention stage, if the retention stage is the first stage then we want to, by default, save off the calculated expiration date for the first stage to be applied to the hidden _dlc_ExpireDate field later unless another stage is proven to be ready to be triggered.  For sake of completion, it also saves off the XML snippet that is used to calculate the _dlc_ExpireDate field.  I found in my testing that I could just set the ItemRetentionFormula property and the _dlc_ExpireDate would get calculated automatically.  However, once my test code (which was a console app just to work as a proof-of-concept) was moved into the event receiver, setting the ItemRetentionFormula property didn’t automatically update the _dlc_ExpireDate field anymore.  My best reasoning for this is that the processing that updated the _dlc_ExpireDate using the ItemRetentionFormula doesn’t trigger after the item has already been updated (you’ll notice the event receiver is on the ItemUpdated event).

The rest of the code should be fairly self-explanatory.  The retention stage doesn’t get reset unless the event receiver was able to successfully parse the retention XML.  The _dlc_ItemStageId gets set to one stage prior to the stage we want to run next time the Expiration Date is reached since it seems Sharepoint uses that property to determine what was the last stage that was run, and will run the next stage.  If we’re resetting back to the first stage, the property can just be removed which will have the effect of setting it to NULL and Sharepoint will know to trigger the first stage the next time the Expiration Date is met.

Using this, you can “reset” the retention policy of any item back to any stage that is applicable with the updated field, even if the stage has already run.  Neat!

21 comments

  1. Thank you for posting…I’ve been battling retention policies for a while and this post certainly clears up some of the mysteries that happen behind the scenes!

    Like

  2. Thank you, this helped me to realize that just modifying a retention policy would not cause it to run again. I had to remove the retention policy and add a new one.

    Like

  3. Hi, thanks for the code, but i get all this errors,

    The name ‘CultureInfo’ does not exist in the current context.
    The type or namesapce name ‘PolicyItem’ could not be found.
    The type or namesapce name ‘Policy’ could not be found.
    The name ‘Policy’ does not exist in the current context.
    The type or namesapce name ‘Xdocument’ could not be found.
    The name ‘Xdocument’ does not exist in the current context.

    Like

  4. You need to include the proper namespaces. PolicyItem and Policy are in the Microsoft.Office.RecordsManagment.InformationPolicy namespace; CultureInfo is in the System.Globalization namespace; XDocument is in the System.Xml.Linq namespace.

    Like

  5. Thanks Mike, the only error i have now is:

    Error 1 ‘System.Collections.Generic.IEnumerable’ does not contain a definition for ‘Select’ and no extension method ‘Select’ accepting a first argument of type ‘System.Collections.Generic.IEnumerable’ could be found (are you missing a using directive or an assembly reference?) C:\testprojekter\EventReceiverProject1\EventReceiver1\EventReceiver1.cs 58 69 EventReceiverProject1

    Br
    Pettsen

    Like

  6. Got it, had to add the code line (using System.Linq;) back at the end of the references ..

    using System.Xml.Linq;
    using System.Xml.XPath;
    using System.Linq;

    Now i just have to test if it’s working.

    Thanks SO MUCH in advance.

    Like

  7. Hi,
    I have a document with this retention stages: next revision + 0 days, recurrence every 1 days.
    When i run it manually, it runs fine. (today it is 31/5 2013 )
    I then change the next revision date to 4/6, but in the compliance details, the Scheduled occurence date says 1/6 2013

    Help

    Like

  8. If you’re using a field called something other than “Effective Date”, you’ll need to change the const _fieldName variable at the top of the code snippet. The code that actually “resets” the retention stages doesn’t run unless the event receiver notices that the field in _fieldName has changed – only then will it examine the retention stage and do any processing necessary.

    Like

  9. Hi Mike,
    Excuse my stupidity, i’m not not that great programmer, but we are not using any fields that we have created on our own. The only field that is changed is “Sheduled occurence date”.
    So i don’t know what to write in the _fieldName where you are writing “Effective_x0020_Date” .
    Whatever i write, i is not working.

    Sorry for keep asking, but this is VERY important for us.

    Br
    Pettsen, ans in advance, THANKS

    Like

  10. Hi Mike,
    Excuse my stupidity, i’m not not that great programmer, but we are not using any fields that we have created on our own. The only field that is changed is “Sheduled occurence date”.
    So i don’t know what to write in the _fieldName where you are writing “Effective_x0020_Date” .
    Whatever i write, i is not working.

    Sorry for keep asking, but this is VERY important for us.

    Br
    Pettsen, thanks in advance

    Like

  11. Ups, sorry for writing the same 3 times.
    A little correction to what i wrote before.
    We are using a field, is a custom site column defined on top site collection level and utilised on a subsite list. The columns fieldname is ‘Nextrevision’ (no spaces).
    If we replace your “Effective_x0020_Date” with ‘Nextrevision’ nothing happens.

    Is the site column source to be equal with the site that hosts the list, or is it ok to define the site column on top level (site collection level)

    Like

  12. If the field exists on the list itself, you can use it in the _fieldName variable. You have to make sure to use the internal name of the field (when editing a list, you can click on the field and the internal field name will be shown in a query parameter in the url).

    Beyond that, the other common problem I can think of is that you’re not actually wiring up this code to trigger on the ItemUpdated event for your list. I didn’t include instructions in this post on how to do that, but it’s a quite common procedure and some quick googling will show you how to do that.

    Like

  13. Sorry for all my questions, this will be the last time i will ask you:

    No matter what combintion i use in theese 3 questions when i create the solution, it don’t get triggered

    1. What type of event receiver do you want
    ( here i choose List item events )

    2. What item should be event source
    ( here i choose Document Library )

    3. Handle the following events
    ( here i choose An item was updated )

    Any suggestion ?
    Thanks in advance

    Like

  14. Sorry, for some reason I didn’t get notified regarding your first post.

    It sounds like you were following the proper steps for setting up the event receiver using the visual studio wizard. I’m glad to hear you got it working.

    The document type shouldn’t matter at all as far as the Information Management Policies. It’s possible you can set up a policy that only targets certain document types, but I doubt you did that.

    At this point, I would just put a breakpoint in your code and see that the event receiver is triggering for all document types as a first step. If it’s not you just need to figure out why it’s ignoring certain document types, and if it is trigger just step through your code and see what’s going on to differentiate one document type from another.

    Like

  15. If we add multiple custom retention policy and then re deploy the custom policy then at that time for previous stage of list item the formula tag will be removed in data tag. So i think it would be good to check formula is not null

    Like

  16. Hi mike – a wonderful post with very undocumented information surfaced, thanks! I’m trying to update the _dlc_ExpireDate field using powershell but am unable – i can pull info from it and i can update the value but when i update the list it reverts to the old value – do you have any hints as to what might be going on?
    Thanks
    Sandra

    Like

  17. Great Post. I can affirm that this same issue is carried over to SharePoint 2013. I have a client that just forwarded me your blog in identifying a very similar issue where we were trying to progress documents from one library to another through a series of 4 libraries until they finally were gone from SharePoint 2013. We found that even moving the documents between libraries, carries over the flagging from the previous retention policy. For example, if we use retention policy to move docs from Lib 1 to Lib 2, then kick off a retention workflow to move from lib 2 to lib 3, nothing happens. No moves. This is because, as you describe so well in this post, that SharePoint has flagged the documents as having been processed by retention so they are not effected by the workflow in downstream libraries.

    This is just crazy. But thanks for writing this up – it’s an oldy, but still very much in effect today in SharePoint 2013. I’ll have to test out SharePoint 2016 to see if this is still an issue going forward.

    Has anyone tested this out in O365 / SharePoint online?

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s