Blog : Using Today Calculation in SharePoint 2010
I came across a requirement where we need to show/update a SharePoint column with today’s calculation depending upon another date
e.g.
There three columns like below :
- Status (Red, Green, Yellow)
- Targeted Completion date
The status column need to update AUTOMATICALLY if
Green = Targeted Completion date >=7 days
Yellow = Targeted Completion date <=7 days
Red = Targeted Completion date <=0 days
As there is no EVENT to be fired, there is no way out to update through workflow or any other event. Calculated column (Today trick) is not working successfully. So, I found creating a custom timer job will be a real solution.
I think many of our friends may be facing this issue, when I googled it, I unfortunately didn’t find a perfect solution, so thought of sharing my efforts.
Here you go :
1. The source code will be like below
2. I am using VS 2010
3. Copy the source code below
3.1 Create a feature
3.2 Create a class file to include the logic
4. Change the server name and list name with your server address and list address
5. deploy it to the server
5.1 Go to Central Admin --> Click on Review Timer Job Definition à Search your timer job à click on it to view the detail
5.2 if you want to run the timer job NOW à Click on the “Run Now”
You should be good to go now.
Few Tricks :
Issue : First time when I deploy the timer jobs it is working fine, but when I updated the code and redeployed the change does not reflect. Due to high cache management system, in MSS 2010. Sometime it does not update the actual change you did. For that you need to do the below :
Go to the services (services.msc)
Restart the Service “SharePoint 2010 Timer”, you should be good now.
For any issue / clarification you may contact me :
Manas Pradhan
/* ************************************************************* */
/// Include this in a new Feature
// This timer job has been scheduled for 5 mins you may change later
using System;
using System.Runtime.InteropServices;
using System.Security.Permissions;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Security;
using Microsoft.SharePoint.Administration;
namespace Custom_TimerJob.Features.Feature1
{
/// <summary>
/// This class handles events raised during feature activation, deactivation, installation, uninstallation, and upgrade.
/// </summary>
/// <remarks>
/// The GUID attached to this class may be used during packaging and should not be modified.
/// </remarks>
///create a new GUID here
[Guid("61e31b39-77c6-49ac-bb34-f56745b23981")]
public class Feature1EventReceiver : SPFeatureReceiver
{
const string List_JOB_NAME = "ListLogger";
// Uncomment the method below to handle the event raised after a feature has been activated.
public override void FeatureActivated(SPFeatureReceiverProperties properties)
{
SPSite site = properties.Feature.Parent as SPSite;
// make sure the job isn't already registered
foreach (SPJobDefinition job in site.WebApplication.JobDefinitions)
{
if (job.Name == List_JOB_NAME)
job.Delete();
}
// install the job
ListTimerJob listLoggerJob = new ListTimerJob(List_JOB_NAME, site.WebApplication);
SPMinuteSchedule schedule = new SPMinuteSchedule();
schedule.BeginSecond = 0;
schedule.EndSecond = 59;
schedule.Interval = 5;
listLoggerJob.Schedule = schedule;
listLoggerJob.Update();
}
// Uncomment the method below to handle the event raised before a feature is deactivated.
public override void FeatureDeactivating(SPFeatureReceiverProperties properties)
{
SPSite site = properties.Feature.Parent as SPSite;
// delete the job
foreach (SPJobDefinition job in site.WebApplication.JobDefinitions)
{
if (job.Name == List_JOB_NAME)
job.Delete();
}
}
}
}
/* ********************************************************************* */
//Create Class file
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;
namespace Custom_TimerJob
{
class ListTimerJob :SPJobDefinition
{
public ListTimerJob() : base() { }
public ListTimerJob(string jobName, SPService service, SPServer server, SPJobLockType targetType)
: base(jobName, service, server, targetType)
{
}
public ListTimerJob(string jobName, SPWebApplication webApplication)
: base(jobName, webApplication, null, SPJobLockType.ContentDatabase)
{
/* Name of your timer job */
this.Title = "Date Proximity Timer Job";
}
public override void Execute(Guid contentDbId)
{
using (SPSite site = new SPSite("http://servername/sitename/"))
{
using (SPWeb web = site.OpenWeb())
{
web.AllowUnsafeUpdates = true;
SPListItem item = null;
SPList list = web.Lists["ListName"];
DateTime targetDate = DateTime.Today;
DateTime currentDate=DateTime.Today;
TimeSpan ts = targetDate -currentDate;
int isTargetDateBlank = 0;
int daysDiff = 0;
string status = null;
for (int i = list.ItemCount -1 ; i>= 0; i --)
{
item = list.Items[i];
if (item["Targeted Completion date"] == null)
{
isTargetDateBlank =1;
}
else isTargetDateBlank =0;
targetDate = Convert.ToDateTime(item["Targeted Completion date"]);
ts = targetDate - currentDate;
daysDiff = ts.Days;
if (( isTargetDateBlank == 1) || (item["Phase"].ToString()=="Complete") || (item["Phase"].ToString() == "Cancelled") || (daysDiff >= 7))
{
status = "(3) Green";
}
else if ((daysDiff <=7) && (daysDiff > 0))
{
status = "(2) Yellow";
}
else if (daysDiff <= 0)
{
status = "(1) Red";
}
if (status != item["Status"].ToString())
{
item["Status"] = status ;
item.Update();
}
isTargetDateBlank = 0;
status =null;
}
web.AllowUnsafeUpdates = false;
}
}
}
}
}