Automatically update a list column based on specific criteria (change the item Status if the Item Review Date is met)

Question

I am working on an issue tracking list inside SharePoint server 2013. And I have two columns as part of the list:-

• Review Status. with these choices; New, in-progress, Need Review.
• Review Date, of type Date only.

Now when an item is first created it will have its "Review Status" = "New", and its "Review Date" must be greater than current day.

now I am trying to implement this feature:- If "Review Date" is reached (“Review Date” = today), to automatically change the "Review Status" from “New” to “Need Review”?

Can anyone advice on the available approaches to achieve this ? is defining a workflow an approach to follow ?

Answer 1

If coding is option, Then you can create a timer job or console application and then schedule it to run daily. It will check whether today is review date and update the status accordingly.

The code will be something like below

using (SPSite site = new SPSite("Your site URL"))
{
    using (SPWeb myweb = site.OpenWeb())
    {
        SPList olist = myweb.Lists["Your list Name"];
        SPQuery query = new SPQuery();
        query.Query = "<Where><Eq><FieldRef Name='ReviewDate' /><Value Type='DateTime'><Today /></Value></Eq></Where>";
        SPListItemCollection olistitems = olist.GetItems(query);
        foreach (SPListItem item in olistitems)
        {
                //Update the item
                //Send the mail
        }
    }
}

Answer 2

Using a workflow, you can set an action to Pause Until Date. In the date field, use your Review Date or a variable that is set to Review Date.

Basic workflow logic:

    Pause Until Review Date
    If Review Status = New
      Set Review Status = Review Needed

Little more info on the topic: "Pause until Date" workflows unable to handle changing dates?