Category Archives: SharePoint

Javascript, List view modification, SharePoint

Compare DueDate with Today in list view

75 Comments

15.09.2009: Complete rewrite of the script to tidy up the code, improve functionality, and to add jQuery-logic to make it run smoother.

This script is used to Compare a “DueDate” in a list view with “Today” and generate “traffic lights” to flag status. It also adds a mouseover tooltip that shows the list element’s “day, hour and minute – status”.

The end result looks like this:
IMG

You can filter on the “Status-column”:
IMG

As always we start like this:
Create a document library to hold your scripts (or a folder on the root created in SharePoint Designer). In this example i have made a document library with a relative URL of “/test/English/Javascript” (a subsite named “test” with a subsite named “English” with a document library named “Javascript”):
IMG

Add a CEWP to your list view – below the list form, and add this code:

<script type="text/javascript" src="/test/English/Javascript/jquery-1.3.2.min.js"></script>
<script type="text/javascript" src="/test/English/Javascript/trafficLightsOnDueDate.js"></script>
<script type="text/javascript">
// Offset
yOffsetMinutes = 2880; // When to show the yellow "flag"
rOffsetMinutes = 1440; // when to show the red "flag"

// Order of apperance of the dateFields in your Formula (1 = first position, 2 = second position and 3 = third position)
OrderDay = 2;
OrderMonth = 1;
OrderYear = 3;

// Settings
defaultTimeIfDateOnly = '12:00'; // If used with "Date Only" - what time should be used as "DueTime" - Military Time
splitChar = '/'; // The separator between the day month year of your calculated dateField: 01[splitChar]01[splitChar]2009
separatorDateTime = ' '; // The separator in the calculated column between date and time: 01/01/2009[separatorDateTime]08:00
LeadInTextBeforeDate = 'Due:'; // The leadin text of your calculated dateField - used to locate the correct table cells

// Tooltip on "traffic lights" before days/hours/minutes
TooltipDueIn = 'Due in: '; 
TooltipOverdue = 'Overdue: ';

// Tooltip on "traffic lights" after days/hours/minutes
TooltipDays = ['day','s']; // Array - first value for "singular", second for "plural"
TooltipHours = ['hour','s']; // Array - first value for "singular", second for "plural"
TooltipMinutes = ['minute','s']; // Array - first value for "singular", second for "plural"

// Call script 
trafficLightsOnDueDate();
</script>

Like this:
IMG

The script “trafficLightsOnDueDate.js” has this soursecode:

/* Compare DueDate with Today in list view and generate "traffic lights" from DueDate
* ----------------------------------------------------
* Author: Alexander Bautz
* alexander.bautz@gmail.com
* Version: 2.0
* LastMod: 15.09.2009
* ----------------------------------------------------
* Must include reference to jQuery
* ----------------------------------------------------
*
* All variables are defined in a CEWP below the list view of your list. See separate codeblock for code.
*
* ----------------------------------------------------
*/

function trafficLightsOnDueDate() {
// Find today's date
var now = new Date();
$("td.ms-vb2:contains('" + LeadInTextBeforeDate + "')").each(function(){
	var sepDate = '';
	var sepHourMinutes = '';
	if($(this).text().substring(LeadInTextBeforeDate.length + 1).length>10){
		sepDate = $(this).text().substring(LeadInTextBeforeDate.length + 1,$(this).text().lastIndexOf(separatorDateTime)).split(splitChar);
		sepHourMinutes = $(this).text().substring($(this).text().lastIndexOf(separatorDateTime)+separatorDateTime.length).split(':');
		IncludeHoursAndMinutes = true;
	}else{
		sepDate = $(this).text().substring(LeadInTextBeforeDate.length + 1).split(splitChar);
		IncludeHoursAndMinutes = false;
	}		

	var m = sepDate[OrderMonth - 1];
	var d = sepDate[OrderDay - 1];
	var y = sepDate[OrderYear - 1];
	
	if(IncludeHoursAndMinutes){
		h = sepHourMinutes[0];
		min = sepHourMinutes[1];
	}else{	
		h = defaultTimeIfDateOnly.split(':')[0]
		min = defaultTimeIfDateOnly.split(':')[1]
	}
	// Build the datestring from fieldvalue
	var fieldDate = new Date(y,m-1,d,h,min,00);

	// Build friendly string for due/overdue mouseover tooltip
		var DueByDays = msToDHM(Math.round((fieldDate.getTime() - now.getTime())));
		var overDueByDays = msToDHM(Math.round((now.getTime() - fieldDate.getTime())));
	
	// OffsetDate - Create the traffic lights
	var yellowOffsetDate = now.getTime() + (yOffsetMinutes * 60000);
	var redOffsetDate = now.getTime() + (rOffsetMinutes * 60000);
	
		if(yellowOffsetDate < fieldDate.getTime() && redOffsetDate < fieldDate.getTime()){
		// green
			$(this).html("<div style='font-weight:bold; font-size:16px; color:green'>•</div>");
			$(this).parent().attr('title',TooltipDueIn + DueByDays);
		}
		else if(yellowOffsetDate >= fieldDate.getTime() && redOffsetDate < fieldDate.getTime()){
		// yellow
			$(this).html("<div style='font-weight:bold; font-size:16px; color:yellow'>•</div>");
			$(this).parent().attr('title',TooltipDueIn + DueByDays);
		}		
		else if(redOffsetDate > fieldDate.getTime() && now.getTime() < fieldDate.getTime()){
		// red due in...
			$(this).html("<div style='font-weight:bold; font-size:16px; color:red'>•</div>");
			$(this).parent().attr('title',TooltipDueIn + DueByDays);
		}
		else if(redOffsetDate > fieldDate.getTime() && now.getTime() > fieldDate.getTime()){
			// red overdue by...
			$(this).html("<div style='font-weight:bold; font-size:16px; color:red'>•</div>");
			// Red text in all row (but the title with link)
			$(this).parent().attr('title',TooltipOverdue + overDueByDays).find('td').addClass('ms-errorcolor ms-bold');
		}

	});
}

function msToDHM(ms){
	var countDays = Math.floor(ms / 86400000); // 86400000 in one day
	var countHours = Math.floor((ms % 86400000) / 3600000); // Find "leftovers" after all the full days have been taken away / 3600000 ms in one hour
	var countMinutes = Math.floor(((ms % 86400000) % 3600000) / 60000); // Find "leftovers" after all hours have been taken away / 60000 ms in one minute
	var str = '';
	if(countDays!=0){
		if(countDays>1){
			str +=  countDays + " " + TooltipDays[0] + TooltipDays[1] + " ";
		}else{
			str +=  countDays + " " + TooltipDays[0] + " ";
		}	
	}
	if(countHours!=0){
		if(countHours>1){
			str += countHours + " " + TooltipHours[0] + TooltipHours[1] + " ";
		}else{
			str += countHours + " " + TooltipHours[0] + " ";
		}
	}
	if(countMinutes!=0){
		if(countMinutes>1){
			str +=  countMinutes + " " + TooltipMinutes[0] + TooltipMinutes[1];
		}else{
			str +=  countMinutes + " " + TooltipMinutes[0];
		}
	}
	if(countDays + countHours + countMinutes == 0)str += " 0 " + TooltipMinutes[0] + TooltipMinutes[1]
	return str;
}

// Attaches a call to the function "trafficLightsOnDueDate" to the "expand grouped elements function" for it to function in grouped listview's
function ExpGroupRenderData(htmlToRender, groupName, isLoaded){
	var tbody=document.getElementById("tbod"+groupName+"_");
	var wrapDiv=document.createElement("DIV");
	wrapDiv.innerHTML="<TABLE><TBODY id="tbod"+groupName+"_" isLoaded=""+isLoaded+"">"+htmlToRender+"</TBODY></TABLE>";
	tbody.parentNode.replaceChild(wrapDiv.firstChild.firstChild,tbody);
trafficLightsOnDueDate();
}

Add these columns to your list:

  • a column of type “Date and Time”, format “Date Only” or “Date & Time” named DueDate
  • a column of type “Yes/No (check box)” named Completed
  • a column of type “Calculated (calculation based on other columns)” named Status

The DueDate-column does not have to be in the list view, but the calculated column must be.

The column named Status has this formula for “DateOnly”:

=IF(DueDate="","N/A",IF(Completed<>TRUE,"Due: "&TEXT(DueDate,"mm/dd/yyyy"),"Completed"))

And this formula for “Date & Time”:

=IF(DueDate="","N/A",IF(Completed<>TRUE,"Due: "&TEXT(DueDate,"mm/dd/yyyy HH:mm"),"Completed"))

This has formula for “Date & Time”:
IMG

Thats it!

Alexander