6

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?

6

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)

  • Thank you for that, makes sense. Any idea/advice on how to colour code SOME field in a document library based on Approval Status? For example, in my JSON column formatting code I have something like: "operator": "toString()", "operands": [ "@currentField" ] }, "Approved" if instead of @currentField I could somehow do @[Approval Status] Do you think that would be possible? – Sylvie Jul 16 '18 at 15:32
  • @Sylvie I don't know, I haven't ever done this with JSON - only with JS. You might find some help by checking the site's other questions with e.g. this search: sharepoint.stackexchange.com/search?q=json+calculated. – moe Jul 16 '18 at 19:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.