I’ve been asked this question several times from SharePoint practitioners trying to translate their existing/new Information Architecture configuration into the world of retention.
Question: “Can you use a SharePoint calculated column as a condition to auto-apply a retention label?”
I didn’t know the answer for sure, so I tested it out!
Short answer… yes, but there’s some important nuances/limitations you should be aware of.
Background
A SharePoint calculated column can be created by selecting the Calculated (calculation based on other columns) data type option:
The calculated column you create is dynamically populated referencing metadata in the same item based on a formula you provide. (Formula reference: Examples of common formulas in lists)
The result of the formula can return 1 of 5 data types:
If you’re familiar with SharePoint Information Architecture, what we’ve covered so far is nothing new.
What you need to know
To auto-apply a retention label to an item in SharePoint based on a column’s value, the crawled property automatically generated for it in the search schema must first be mapped to a queryable managed property. This is done by using one of the pre-built Refinable managed properties that matches the data type of the column such as RefinableString##, RefinableDate##, or RefinableInt##.
But… a SharePoint calculated column is different. Whether you’ve chosen Single line of text, Number, Currency, Date and Time, or Yes/No as the data type of the calculated column, they are ALL stored as a string value in the search index as shown in the sample values below:
What this means is you must map the crawled property to a RefinableString managed property because all calculated columns are stored as text in the search index regardless of the data type chosen during column creation. This will affect the KQL query options available to you. The examples below assume you’ve mapped the crawled property to RefinableString00:
Note: in KQL, colon means ‘contains’, equal sign means ‘equals exactly’
Once the RefinableString## managed property is populated in the search index, you can reference it in a KQL query to auto-apply a retention label to SharePoint content matching the condition.
Practical use-cases? Are there any?
To be clear, SharePoint calculated columns have been around for a very long time. They serve a business purpose far beyond a retention need and will continue to be a tool in a SharePoint information architect’s toolbelt for years to come without using them for retention.
Are there cases where you may also want to use one in a retention label auto-apply condition to fulfill a retention requirement? The fact that I was asked by several customers if it was possible makes me think yes.
Although I haven’t used one yet, I suspect there may be places they could be used to help streamline the automatic application of retention labels or trigger a retention event. I’ll be watching for opportunities to do this in my customer’s retention schedules keeping the above limitations in mind.
If you have a use-case you’d like to share, let me know and I’ll add it to this post! I’ll do the same.
This post demonstrates why it’s necessary to have a deep understanding of SharePoint information architecture and the SharePoint search schema and index to build advanced KQL queries for some of your retention needs.
Thanks for reading.
-JCK
Hi Joanne. I’ve been following your blog for a while. We apply Retention Policies to the entire site and delete after x time based on file creation date. Is there a way for end users to see on each file when a file would be deleted based on the policy applied at the site level? Maybe with a calculated column? I’m not well versed in calculated columns.
Hi Doyle,
Great question! Mark Warnes has replied with the formula you could use to display a date based on a fixed amount of time past the created date. Thanks Mark!!
A word of caution on doing this is it will work fine if you only have 1 retention policy published to the site. If you have multiple retention policies published to the site and/or a retention label applied to an item with a longer retention period, then your calculated column will not reflect that.
I don’t know your setup so thought I’d mention that. If you only have 1 retention policy published to the site, displaying the calculated column to show users when the content will be deleted is a great idea. You can also create views based on “upcoming deletions” by filtering on the calculated column.
-Joanne
Assuming your “x time” is a fixed number of years/months/days for your site-wide retention policy, then you should be able to create a new column that takes the file creation date and simply adds x number of years/months/days.
For example, this syntax in the calculated column should take the file creation date (from column “Created”) and add 7 years:
=DATE(YEAR([Created])+7,MONTH([Created]),DAY([Created]))
Thanks for replying Mark!!
That would work but in the event they change their retention policy, they would need to manually change the calculated column. This is a good solution. I was just hoping for something dynamic. You’d think Microsoft would have metadata on files somewhere that could be queried directly for that kind of thing but we’ll try your formula. Thanks!
Hi Doyle, unfortunately it cannot be dynamic. Microsoft does not expose the “expired date” property to be able to use it in a calculated column.
-Joanne
Thanks for this post! Our use case is that we we’re using a retention policy to trigger a workflow that emails a user 30 days _before_ the date in a custom date column. The policy stage editor only allows you to specify a certain number of days after a date column, not before. (Negative numbers are not allowed). So we calculate thirty days before in a hidden column and then base the policy on that. This is a bit of a hack, but the other solution involving paused workflows that run forever was arguably worse.
Problem right now is, I can’t get my calculated field to show up in the list of crawled properties so that I can map it to a managed property…
Hi,
Loading...
By your description, it sounds like you’re using Information Management Policies and not Retention Label Policies in the Compliance Center.
As stated in my blog article, a calculated column of type ‘Date’ which is what I’ve assumed you’ve done will always show up as a string in the search index so you can’t have a comparison like CustomDateManagedProperty
Hi Joanne,
Is there a way I can display the retention expiration as column of the document based on the retention label assigned? Thank you
Hi Ruth, there is no way to show this date unfortunately. This may be something that Microsoft will add in the future though.