I've been tasked with getting a SharePoint site up and running as part of my job, it requires a semi-automated way of marking files for an archival process where they are taken off of the SharePoint and moved to a shared drive to conserve space. This is also part of a process to move many files on to the SharePoint system which may override important information stored on the files such as the date it was last modified, for this reason I would be utilizing a column which is effectively the last modified date of the file before its transferral to SharePoint.
Right now my brain is kinda working on this problem in pseudo-code of sorts, I have some decent experience with PHP and Java and mostly understand how the logic behind this should work, I'm just having trouble bringing it to life.
What I have so far is a collection of columns for files, the main ones relevant to my problem here are a File Activity metadata which should be a calculated column that can determine the contents of the field depending on other factors/columns.
Another column is for a Retention Period End Date, of which after this date is reached the file should be pushed to review by a user and then its fate should be decided (postpone archival/archive it/delete it, this is handled in another metadata column that is manually selected called Review Status).
Finally, the last relevant column to this idea is a [Real Document Date] which is the date the file was last modified before transferring it to the SharePoint system, since [Last Modified] would be overwritten as soon as it was added, as I've touched on above.
Anyway, the File Activity metadata should contain three possible outcomes: Active, Dormant or Needs Review.
My pseudocode for this sort of process is as follows:
IF [Last Modified] < 6 months
= "Active"
IF [Last Modified] > 6 months
= "Dormant"
IF [Last Modified] OR [Real Document Date] > [Retention Period End Date]
= "Needs Review"
I have tried a few different ways I could best understand how to implement this idea as a calculated column based on other people's questions and answers given on here and modifying their code for my own use, but I've failed each time. I was wondering if anyone more experienced with calculated columns in SharePoint could effectively spoonfeed me the solution to my conundrum?
Thank you for your time.
