Calculated Column Based on Version Not Updating

Question

I'm using a calculated column [DocumentStatus] and versioning to try and do some colour-formatting based on Approval Status. I have Major & Minor versioning turned on.

Since I cannot use Approval Status in a calculated column (and I'd really prefer not to rely on workflows), I have created this formula:

=IF(ISERROR(FIND(".0",Version)),"Draft","Approved")

This is supposed to figure out if the document is a major(approved) or minor(not approved). Then I have added some column formatting to change the colour if the text = Approved or Draft.

This works about half of the time. The other half, it just doesn't update. When I approve a document, the version changes to a major version e.g. from 3.4 to 4.0. But the [DocumentStatus] field remains as Draft.

If I go into the library settings, open the calculated column and save it (without changing anything) it seems to jumpstart the list again, and the field changes to the appropriate value. But I'd have to do that for every change. Any ideas?? Maybe there's a better formula I can use?

Answer 1

There are two issues.

1.

Calculated columns are not synchronized with other columns' data, so when the version-column's value changes, the calculated field doesn't understand to re-calculate its status until there's a save-action either for the calculated field or for the document itself. With lists the same could be "handled" by opening the list item into edit and saving it without changes (which corresponds to your workaround you found). This matter has been discussed over the years and summarized as a some sort of a poor functionality by the users, but seemingly hasn't received the treatment it maybe would deserve.

The workarounds I remember coming across would be to

• "update" your document library's content with a PowerShell script e.g. daily so that the calculated column values refresh
• create an event receiver which again "refreshes" the document in the library and which would fire after a specific field is updated,
• create a workflow with an above described action.

There are also other workarounds which probably could be translated to your scenario in e.g. here.

2.

Even if the issue 1 wouldn't exist, the calculated field would still be calculated before the version number would be saved as the document's property. So you would be trying to reference something which isn't yet saved or even exists. (A related question)

Answer 2

You can change the way the calculated column is rendered through CSR (js link) like so

var documentStatusTemplate = {
Templates: {
  Fields: {
    'DocumentStatus': {
        View: function(){ 
            var version = ctx.CurrentItem._UIVersionString
            return version.indexOf(".0")>=0 ? "Approved" : "Draft"
        }
    }
  }
}

};

SP.SOD.executeFunc('clienttemplates.js', 'SPClientTemplates', function () {
SPClientTemplates.TemplateManager.RegisterTemplateOverrides(documentStatusTemplate)

});