How to update calculated column on a daily basis without admin rights?

Question

I am running SharePoint 2013 and have a column that is supposed to determine whether or not each phase of a project is late. I am using the NOW() fucntion, which, of course, does not update dynamically - only when the item is edited.

=IF(INT(NOW())-INT([Define/Concept End Date])>0,"Late","On Time")

I have seen two solutions: powershell and custom SPD workflow. I do not have access to SPD. As I understand it, I would need admin rights to run the powershell script, which I don't have either.

Is there a way to force all items to update every day using, say, javascript? I know I can embed javascript in the page. I am open to any other solutions that might be out there as well.

Thanks

Answer 1

If you only use this for display purposes, why not use a different approach which does not require you to do updates on the list item at all....

1. Create the site column in a sandboxed solution.
2. Your column stores the Due date.

3. Create a javascript file to deploy somewhere to your site, e.g. /Documents/duedate.js

4. Configure the JSLink property in the site column. Point it to the deployed javascript file, e.g. /Documents/duedate.js

The magic happens in the duedate.js file:

siteCtx.Templates.Fields = {
// MyCustomField is the Name of our field
   'MyCustomField': {
   'View': customView
  }
};
// Register the template to render custom field
SPClientTemplates.TemplateManager.RegisterTemplateOverrides(siteCtx);
// View Page custom rendering
function customView(ctx) {
  if (ctx != null && ctx.CurrentItem != null) {
    var endDate= parseInt(ctx.CurrentItem["Concept End Date"]);
    if (endDate < new Date()) {
         // this code is unverified.
         return "On time";
    } else {
        return "Late";
    }
  }
}

Answer 2

Caluclated Columns will be calculated each time they are displayed. You don't have to update items. :-)

The problem is your formula. Why do you try to convert it to INT?

SharePoint has very nice Calculated Field Formulas, see http://msdn.microsoft.com/en-us/library/office/bb862071%28v=office.14%29.aspx

A working example would be:

=IF(NOW()>[Define/Concept End Date],"Late","On Time")

Answer 3

I don't have enough Rep to comment on the other answer but Calculated fields are updated at SAVE time.

Your Javascript idea could work if users viewing the list have permissions to update the items, otherwise it might update, or might not update.

Since you don't have SPD you don't have many other options but you can try designing a reusable workflow in another environment where you CAN use SPD. On the Workflow, call a save once a day after 12am, export it as a sandbox solution and deploy it at the other environment. You will need Site Collection Administrator permissions, but that might be easier for you to procure.

Answer 4

This sounds more like a timer job should be made - that way the job can be managed via Central Admin. A workflow might work, but a timer job is more likely the correct way to update a field on a schedule.