USING A SHAREPOINT CALCULATED COLUMN WITH RETENTION

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.

This blog is part of SharePoint Week. Find more here.

About the Author:

Joanne Klein is the owner of NexNovus, a SharePoint and Office 365 consulting company. She is an Advanced Compliance specialist within the Microsoft 365 cloud, has over 10 years of experience in SharePoint and is a Microsoft MVP in Office Apps & Services.

Reference:

Klein, J. (2022). USING A SHAREPOINT CALCULATED COLUMN WITH RETENTION. Available at: https://joannecklein.com/2022/02/16/using-a-sharepoint-calculated-column-with-retention/ [Accessed: 28th April 2022]. 

Share this on...

Rate this Post:

Share:

Topics:

SharePoint

Tags: