Blog

I'm looking for:

Sharepoint Information Management Policy – Retention

I 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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
<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!


Looking for a new job? We work with some of the biggest names in tech, and we’re hiring! Check out our open jobs and make your next career move with Planet.