I have a requirement that needs to highlight “Incomplete” row based on a certain task with a unique due date. It also highlights the row if the task is not yet completed and exceeds the due date.
For example:
Task A – Due date =10 days before = highlight Yellow
Task A – Due date =7 days before and After Due Date = highlight Red
Task B – Due date = 6 days before = highlight Yellow
Task B – Due date = 4 days before and After Due Date = highlight Red
Task C – Due date = 3 day before = highlight Yellow
Task C – Due date =1 days before and After Due Date = highlight Red
I found a lot of scripts in the net but the problem is, they need the columns like Due Date to be in the list view for the script to work. My requirement does not need the Due Date to be present in the list view. Also, I have 2 Due Dates, Warning (Yellow) and Critical (Red).
Okay let’s go now to the solution. First, I created two calculated columns. The two columns calculate the Start Date minus the days before. I also use “IF” in the formula because every task has a unique due date.
For 1st column, I named it Due Date (Yellow highlight) and for the 2nd column, I named it Red Date (Red Highlight).
=IF(Task=”Task A”, [Start Date] – 10, IF(Task=”Task B”, [Start Date] – 6, IF(Task=”Task C”, [Start Date] – 3, “None”)))
Formula for Red Date:
=IF(Task=”Task A”, [Start Date] – 7, IF(Task=”Task B”, [Start Date] – 4, IF(Task=”Task C”, [Start Date] – 1, “None”)))
After creating the two columns, I’m now ready working on the script. Upload the jquery files on your site and reference it on your script.
<script language="javascript" src="https://www.sample.com /sites/Mekalikot/JQuery%20library/jquery-1.6.4.min.js" type="text/javascript"></script>
<script language="javascript" src=" https://www.sample.com /sites/Mekalikot/JQuery%20library/jquery.SPServices-0.7.1a.min.js" type="text/javascript"></script>
$().SPServices({
operation: "GetListItems",
async: false,
listName: "Logistics Warning Copy",
CAMLViewFields: "<ViewFields Properties='True' />",
CAMLQuery: "<Query><Where><Eq><FieldRef Name='Status' /><Value Type='Text'>Incomplete</Value></Eq></Where></Query>",
CAMLQueryOptions: "<QueryOptions><ViewAttributes /></QueryOptions>",
completefunc: function (xData, Status) {
//alert(xData.responseText);
$(xData.responseXML).SPFilterNode("z:row").each(function() {
RedDate = $(this).attr("ows_RedDate");
//from SP: datetime;#2013-11-15 00:00:00
DueDate = $(this).attr("ows_DueDateCalculated");
//from SP: datetime;#2014-01-07 08:00:00
retvalID= $(this).attr("ows_ID");
Next on the script is the conversion of the Dates extracted.
I will use this converted dates in comparison with TODAY’s date format.
var SpRedDate = RedDate.substring(10,20); //get date only
var
ConvRedDate = SpRedDate.replace(/-/g,'/'); //replace dash with slash
var
d1 = new Date (ConvRedDate); //convert to date: Sat Nov 30 00:00:00 UTC+0800
2013
var
SpDueDate = DueDate.substring(10,20); //get date only
var
ConvDueDate = SpDueDate.replace(/-/g,'/'); //replace dash with slash
var
d2 = new Date (ConvDueDate); //convert to date: Sat Nov 30 00:00:00 UTC+0800
2013
The last part is the comparison of the Dates to TODAY’s date.
I will be using the “iid” to highlight the row based on the comparison results.
if (today >= d1){
$("tr[iid*='"+
string + "']").css("background-color", "#FCE4F2");
}
if
(today >= d2 && today< d1) {
$("tr[iid*='"+
string + "']").css("background-color",
"#FFF5C6");
}
if (isNaN(d1)){
$("tr[iid*='"+
string + "']").css("background-color",
"#FCE4F2");
}
});
This is the complete script:
<script language="javascript" src="https://www.sample.com /sites/Mekalikot/JQuery%20library/jquery-1.6.4.min.js" type="text/javascript"></script>
<script language="javascript" src=" https://www.sample.com /sites/Mekalikot/JQuery%20library/jquery.SPServices-0.7.1a.min.js" type="text/javascript"></script>
<script>
$(document).ready(function(){
var Task;
var RedDate;
var DueDate;
var today = new Date(); //Today's date Mon Dec 2 16:20:24 UTC+0800 2013
$().SPServices({
operation: "GetListItems",
async: false,
listName: "Logistics Warning Copy",
CAMLViewFields: "<ViewFields Properties='True' />",
CAMLQuery: "<Query><Where><Eq><FieldRef Name='Status' /><Value Type='Text'>Incomplete</Value></Eq></Where></Query>",
CAMLQueryOptions: "<QueryOptions><ViewAttributes /></QueryOptions>",
completefunc: function (xData, Status) {
//alert(xData.responseText);
$(xData.responseXML).SPFilterNode("z:row").each(function() {
RedDate = $(this).attr("ows_RedDate"); // from SP: datetime;#2013-11-15 00:00:00
DueDate = $(this).attr("ows_DueDateCalculated"); //from SP: datetime;#2014-01-07 08:00:00
retvalID= $(this).attr("ows_ID");
var SpRedDate = RedDate.substring(10,20); //get date only
var ConvRedDate = SpRedDate.replace(/-/g,'/'); //replace dash with slash
var d1 = new Date (ConvRedDate); //convert to date: Sat Nov 30 00:00:00 UTC+0800 2013
var SpDueDate = DueDate.substring(10,20); //get date only
var ConvDueDate = SpDueDate.replace(/-/g,'/'); //replace dash with slash
var d2 = new Date (ConvDueDate); //convert to date: Sat Nov 30 00:00:00 UTC+0800 2013
var string = retvalID.toString();
if (today >= d1){
$("tr[iid*='"+ string + "']").css("background-color", "#FCE4F2");
}
if (today >= d2 && today< d1) {
$("tr[iid*='"+ string + "']").css("background-color", "#FFF5C6");
}
if (isNaN(d1)){
$("tr[iid*='"+ string + "']").css("background-color", "#FCE4F2");
}
});
}
});
});
</script>
For simple row highlights based on status values you can check my other post: Highlight Row, Change Font Color and Highlight One Cell Based on Status or Column Value using JQuery in SharePoint
For simple row highlights based on status values you can check my other post: Highlight Row, Change Font Color and Highlight One Cell Based on Status or Column Value using JQuery in SharePoint
