31.07.2010 Updated the code for the file “FiveDateColumnStatus.js” and the CEWP code for ListView, NewForm, DispForm and EditForm. Added support for column name in the mouse over tooltip and the percentage is now “0 %” if none of the date fields are applicable. The CEWP code has changed as the “arrOfFields” has become an object (changed the parameter name to “arrOfFieldsObj”).

29.07.2010 Fixed bug when using using calculated columns and a date field is empty. Thanks to Charlie Epes for noticing the bug. The code for the file “FiveDateColumnStatus.js” is updated.

16.07.2010 Updated the file “FiveDateColumnStatus.js” to support calculated columns.

03.07.2010 Updated the code to add DispForm integration and made the number of “date fields” dynamic – you can add as many as you like to the array. The code in the file “FiveDateColumnStatus.js” is updated, and there has been added a new section for DispForm.

I got an e-mail from Charlie Epes about a ticket over at STP

Hi Alexander:
You are mentioned in the thick of a request started by me. If you want to chime in here, please do!

http://www.endusersharepoint.com/STP/viewtopic.php?f=10&t=1319

My challenge was:
Here’s an example of what I’m trying to do in my List.

Let’s say my dog’s veterinarian offers 5 shots for my dog. The vet’s record needs to show the expiration of each of the 5 shots. The vet likes to keep a “score” for my dog showing that Rover is 80% up to date on his shots.

Of course there are exceptions. My dog will never need 1 of the shots, so it’s “not applicable” and using it as one of the 5 to calculate my percentage throws off my score. I figure that entering a distant date of 12/31/8900 would indicate that the shot is not applicable.

In my case, I’m using a List to indicate the “Health” of a client’s files by giving each client an overall score based on how up to date “eligible” files are.

I thought of it and found that i could do this in another way than Charlie and Dessie had going in the discussion.

My approach reuses some scripts from this article Vertical Scrolling WebPart to get the items from a list view.

It then parses the dates and present the status by colored “boxes” and a “percent complete” value like this:

= a date to come
= a date that has passed
= a date that is applicable, but is empty
= a date that does not apply

It provides a mouse over with the “Overdue by X days” or “Due in X days”

This approach can be used with grouped views.

I have separated the main code in a file named “FiveDateColumnStatus.js”. This file should be saved locally and refered in the CEWP code. The code is found below.

To use this solution you have to:
Create some date columns, corresponding Yes/No type columns and one calculated column with the formula =”*Status*”

The date columns does not have to be in the list view, but the calculated column to hold the status and the “Title (linked to item with edit menu)” must be.

A modification from Charlie’s original idea is the presence of a checkbox for each date field to indicate whether it is applicable and should count in the total percentage – or not.

If the Checkbox is not checked – the corresponding date column is hidden.

This code goes in a CEWP below NewForm and/or EditForm and hides the date fields if the corresponding Yes/No column is not checked:

<script type="text/javascript" src="../../Javascript/jquery-1.4.2.min.js"></script>
<script type="text/javascript">
fields = init_fields_v2();
/*
 This code goes in a CEWP below NewForm and/or EditForm and hides the date fields if the corresponding Yes/No column is not checked.
 arrOfDateFieldsObj: 
  Array of objects containing FieldInternalName for the date column and FieldInternalName for the bool column to select whether the date column is applicable.
  Format is {dateFIN:'FieldInternalNameOfDateColumn',applicableFIN:'FieldInternalNameOfTriggerYesNoColumn'}.
*/

var arrOfDateFieldsObj = [{dateFIN:'DateField1',applicableFIN:'Date1Applicable'},
						{dateFIN:'DateField2',applicableFIN:'Date2Applicable'},
						{dateFIN:'DateField3',applicableFIN:'Date3Applicable'},
						{dateFIN:'DateField4',applicableFIN:'Date4Applicable'},
						{dateFIN:'DateField5',applicableFIN:'Date5Applicable'}]


hideDateFieldsNotApplicable(arrOfDateFieldsObj);

function hideDateFieldsNotApplicable(arrOfFieldsObj){
	$.each(arrOfFieldsObj,function(i,obj){
		var thisChck = $(fields[obj.applicableFIN]).find(':checkbox');
		var checked = thisChck.attr('checked');
		// Onload
			if(!checked){
				$(fields[obj.dateFIN]).hide();
			}
		// Onclick
		thisChck.click(function(){
			var checked = $(this).attr('checked');
			if(checked){
				$(fields[obj.dateFIN]).show();
			}else if(!checked){
				$(fields[obj.dateFIN]).hide();
			}
		});	
	});
}

function init_fields_v2(){
	var res = {};
	$("td.ms-formbody").each(function(){
	var myMatch = $(this).html().match(/FieldName="(.+)"s+FieldInternalName="(.+)"s+FieldType="(.+)"s+/);	
		if(myMatch!=null){
			// Display name
			var disp = myMatch[1];
			// FieldInternalName
			var fin = myMatch[2];
			// FieldType
			var type = myMatch[3];
			if(type=='SPFieldNote'){
				if($(this).find('script').length>0){
					type=type+"_HTML";
				}
			}
			if(type=='SPFieldLookup'){
				if($(this).find('input').length>0){
					type=type+"_Input";
				}
			}
			// Build object
			res[fin] = this.parentNode;
			res[fin].FieldDispName = disp;
			res[fin].FieldType = type;
		}		
	});
	return res;
}
</script>

This code goes in a CEWP below the DispForm:

<script type="text/javascript" src="../../Javascript/jquery-1.4.2.min.js"></script>
<script type="text/javascript" src="../../Javascript/FiveDateColumnStatus.js"></script>
<script type="text/javascript">
fields = init_fields_v2();
/* 
This code goes in a CEWP below DispForm. It displays the status field in the same way as in the list view. 
It also hides the date fields if the corresponding Yes/No column is not checked.

Parameters:
 listGuid: The GUID of this list
 arrOfDateFieldsObj: Array of objects containing FieldInternalName for the date column, DisplayName for the date column and FieldInternalName for the bool column to select whether the date column is applicable.
 Format is {dateFIN:'FieldInternalNameOfDateColumn',dateDispName:'DisplayNameOfDateColumn',applicableFIN:'FieldInternalNameOfTriggerYesNoColumn'}.
*/
var listGuid = "31FFC357-74DA-4830-8288-D8214781556D";
var arrOfDateFieldsObj = [{dateFIN:'_Date1',dateDispName:'Datefield number 1',applicableFIN:'Date1Applicable'},
						{dateFIN:'DateField2',dateDispName:'Datefield number 2',applicableFIN:'Date2Applicable'},
						{dateFIN:'DateField3',dateDispName:'Datefield number 3',applicableFIN:'Date3Applicable'},
						{dateFIN:'DateField4',dateDispName:'Datefield number 4',applicableFIN:'Date4Applicable'},
						{dateFIN:'DateField5',dateDispName:'Datefield number 5',applicableFIN:'Date5Applicable'}]


// Get ID from querystring
var thisItemID = getQueryParameters().ID;
// Call function to generate "Status field contents"
var res = getDateFieldStatus(arrOfDateFieldsObj,{'listName':listGuid,'ID':thisItemID});
// Write back the result to the calculated field containig the value "*Status*"
$.each(fields,function(id,obj){
	if($(obj).find('.ms-formbody').text().match(/*Status*/)){
		$(obj).find('.ms-formbody').html(res[thisItemID].html)
	}	
});

// Hide fields that does not apply
$.each(arrOfDateFieldsObj,function(i,obj){
	var applicable = res[thisItemID][obj.applicableFIN];
	if(!applicable){
		$(fields[obj.dateFIN]).hide();
		$(fields[obj.applicableFIN]).hide();
	}
});
</script>

This code is used in the list view, and must be inserted in a CEWP below the list view webpart:

<script type="text/javascript" src="../../Javascript/jquery-1.4.2.min.js"></script>
<script type="text/javascript" src="../../Javascript/FiveDateColumnStatus.js"></script>
<script type="text/javascript">
/* 
 arrOfDateFieldsObj:
 Array of objects containing FieldInternalName for the date column, DisplayName for the date column and FieldInternalName for the bool column to select whether the date column is applicable
 Format is {dateFIN:'FieldInternalNameOfDateColumn',dateDispName:'DisplayNameOfDateColumn',applicableFIN:'FieldInternalNameOfTriggerYesNoColumn'}
*/

var arrOfDateFieldsObj = [{dateFIN:'_Date1',dateDispName:'Datefield number 1',applicableFIN:'Date1Applicable'},
						{dateFIN:'DateField2',dateDispName:'Datefield number 2',applicableFIN:'Date2Applicable'},
						{dateFIN:'DateField3',dateDispName:'Datefield number 3',applicableFIN:'Date3Applicable'},
						{dateFIN:'DateField4',dateDispName:'Datefield number 4',applicableFIN:'Date4Applicable'},
						{dateFIN:'DateField5',dateDispName:'Datefield number 5',applicableFIN:'Date5Applicable'}]

getDateFieldStatus(arrOfDateFieldsObj);
</script>

This is the code for the file “FiveDateColumnStatus.js”:

/* ---------------------------------------------
 * Created by Alexander Bautz
 * alexander.bautz@gmail.com
 * https://spjsblog.com
 * Copyright (c) 2010 Alexander Bautz (Licensed under the MIT X11 License)
 * v1.1
 * LastMod: 31.07.2010
 * LastChange:
 	- Variable number of date columns
 	- DispForm integration
 	- Support for calculated columns
 	- 29.07.2010: Fixed bug when using using calculated columns and a date field is empty.
 	- 31.07.2010: Added the column name in the mouseover tooltip. If none of the date fields are applicable, the percentage reads "0 %".

 * See this blog post for instructions:
	
Visual status indicator based on 5 date columns in a list view


 * ---------------------------------------------
*/

function getDateFieldStatus(arrOfFieldsObj,qParam){
	if(typeof(arrOfFieldsObj)!='undefined'){
		viewFieldsArr = [];
		dateFieldsArr = [];
		dateFieldsDispNameArr = [];
		dateApplicableArr = [];
		$.each(arrOfFieldsObj,function(i,obj){
			dateFieldsArr.push(obj.dateFIN);
			dateFieldsDispNameArr.push(obj.dateDispName);
			dateApplicableArr.push(obj.applicableFIN);
		});
		viewFields = viewFieldsArr.concat(['ID'],dateFieldsArr,dateApplicableArr);
	}
	if(typeof(myCustomQueryRes)=='undefined'){
		wsBaseUrl = L_Menu_BaseUrl + '/_vti_bin/';
		if(qParam==undefined){
			myCustomQueryRes = queryItemsByViewName(ctx.listName,ctx.view,viewFields);
		}else{
			var query = '<Where><Eq><FieldRef Name="ID" /><Value Type="Text">' + qParam.ID + '</Value></Eq></Where>';
			myCustomQueryRes = queryItemsByCAML(qParam.listName,query,viewFields);
		}
		if(myCustomQueryRes.count==-1){
			alert("An error occured in the query.");
		}else{
			resObj = {};
			var now = new Date();
			$.each(myCustomQueryRes.items,function(i,item){
				var thisID = item.ID;
				resObj[thisID] = {};
				var dArr = [];
				$.each(dateFieldsArr,function(j,fin){
					if(item[dateApplicableArr[j]]==1){
						dArr.push((item[fin]==null)?'':item[fin].replace(/w+;#/,'').split('-'));						
						// Add to the object: applicable
						resObj[thisID][dateApplicableArr[j]]=true;
					}else{
						dArr.push('NotApplicable');
						// Add to the object: not applicable
						resObj[thisID][dateApplicableArr[j]]=false
					}
				});

				var percent = 0;
				var increment = ((100/dArr.length)*100)/100;
				var htmlBuffer = [];
				var notApplicableCount = 0;
				$.each(dArr,function(idx,dateArr){
					if(typeof(dateArr)=='object'){
						if(dateArr.length==1){
							date = '';
						}else{
							var y = dateArr[0];
							var m = (dateArr[1]-1);
							var d = dateArr[2].substring(0,dateArr[2].indexOf(' '));
							var date = new Date(y,m,d);
						}
					}else if(dateArr=='NotApplicable'){
						notApplicableCount += 1;
						date = 'NotApplicable';
					}else if(dateArr==undefined){
						date = '';
					}
					var dummyIMG = "<img src='"+L_Menu_BaseUrl+"/_layouts/images/blank.gif' style='height:7px;width:7px;border:1px silver solid'>";
					if(date==''){
						htmlBuffer.push("<span title='"+dateFieldsDispNameArr[idx]+" is empty' style='background-color:yellow'>"+dummyIMG+"</span>");
					}else if(date=='NotApplicable'){
						percent+=increment;
						htmlBuffer.push("<span title='"+dateFieldsDispNameArr[idx]+" is not applicable' style='background-color:white'>"+dummyIMG+"</span>");
					}else if(date>now){
						percent+=increment;
						var countDays = Math.floor(((date-now)/86400000)*10)/10; // 86400000 in one day
						var postfix = (countDays>1)?"s":'';
						htmlBuffer.push("<span title='"+dateFieldsDispNameArr[idx]+" is due in "+countDays+" day"+postfix+"' style='background-color:green'>"+dummyIMG+"</span>");
					}else{
						var countDays = Math.floor(((now-date)/86400000)*10)/10; // 86400000 in one day
						var postfix = (countDays>1)?"s":'';
						htmlBuffer.push("<span title='"+dateFieldsDispNameArr[idx]+" is overdue by "+countDays+" day"+postfix+"' style='background-color:red'>"+dummyIMG+"</span>");
					}
				});
				if(notApplicableCount==dArr.length){
					var percentagePostfix = "0 %";
				}else{
					var percentagePostfix = Math.floor(percent)+" %";
				}
				resObj[thisID].html=htmlBuffer.join(' ')+" "+percentagePostfix;
			});
		}
	}
	if(qParam==undefined){
		updateView(resObj);
	}else{
		return resObj;
	}
}

function updateView(obj){
	$("table.ms-listviewtable td:contains('*Status*')").each(function(){
		var lineID = $(this).parent().find('table.ms-unselectedtitle').attr('id');
		$(this).html(obj[lineID].html);
	});
}

// Attaches a call to the function 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);
getDateFieldStatus();
}

/*****************************************************
			Web service calls
*****************************************************/
function queryItemsByCAML(listName, query, viewFields, rowLimit, pagingInfo){
	var content = wrapQueryContent({'listName':listName,'query':query,'rowLimit':rowLimit,'viewFields':viewFields,'pagingInfo':pagingInfo});
	var result = {count:-1, nextPagingInfo:'', items:[]};
	wrapSoapRequest(wsBaseUrl + 'lists.asmx', 'http://schemas.microsoft.com/sharepoint/soap/GetListItems', content, function(data){
		result.count = $(data).find("[nodeName='rs:data']").attr('ItemCount');
		result.nextPagingInfo = $(data).find("[nodeName='rs:data']").attr('ListItemCollectionPositionNext');
		$(data).find("[nodeName='z:row']").each(function(idx, itemData){
			var fieldValObj = {}
			$.each(viewFields,function(i,field){
				var value = $(itemData).attr('ows_' + field);
				if(value == undefined) value = null;
				fieldValObj[field]=value;
			});
			result.items.push(fieldValObj);
		});
	});
	return result;
}

// Function to pull items from view
function queryItemsByViewName(listName, viewName, viewFields, pagingInfo){
	var content = wrapQueryContent({'listName':listName,'viewName':viewName,'viewFields':viewFields,'pagingInfo':pagingInfo});
	var result = {count:-1, nextPagingInfo:'', items:[]};
	wrapSoapRequest(wsBaseUrl + 'lists.asmx', 'http://schemas.microsoft.com/sharepoint/soap/GetListItems', content, function(data){
		result.count = $(data).find("[nodeName='rs:data']").attr('ItemCount');
		result.nextPagingInfo = $(data).find("[nodeName='rs:data']").attr('ListItemCollectionPositionNext');
		$(data).find("[nodeName='z:row']").each(function(idx, itemData){
			var fieldValObj = {}
			$.each(viewFields,function(i,field){
				var value = $(itemData).attr('ows_' + field);
				if(value == undefined) value = null;
				fieldValObj[field]=value;
			});
			result.items.push(fieldValObj);
		});
	});
	return result;
}

function wrapQueryContent(paramObj){
	var result = [];
	result.push('<GetListItems xmlns="http://schemas.microsoft.com/sharepoint/soap/">');
	result.push('<listName>' + paramObj.listName + '</listName>');
	if(paramObj.viewName!=undefined){
		result.push('<viewName>' + paramObj.viewName + '</viewName>');
	}
	if(paramObj.query != null && paramObj.query != ''){
		result.push('<query><Query xmlns="">');
		result.push(paramObj.query);
		result.push('</Query></query>');
	}
	if(paramObj.viewFields != null && paramObj.viewFields.length > 0){
		result.push('<viewFields><ViewFields xmlns="">');
		$.each(paramObj.viewFields, function(idx, field){
			result.push('<FieldRef Name="' + field + '"/>');
		});
		result.push('</ViewFields></viewFields>');
	}
	if(paramObj.rowLimit != undefined && paramObj.rowLimit != null && paramObj.rowLimit > 0){
		result.push('<rowLimit>' + paramObj.rowLimit + '</rowLimit>');
	}else{
	    result.push('<rowLimit>100000</rowLimit>');
	}
	result.push('<queryOptions><QueryOptions xmlns=""><IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns>');
	if(paramObj.pagingInfo != undefined && paramObj.pagingInfo != null && paramObj.pagingInfo != '')
		result.push('<Paging ListItemCollectionPositionNext="' + paramObj.pagingInfo.replace(/&/g, '&') + '" />');
	result.push('</QueryOptions></queryOptions>');
	result.push('</GetListItems>');
	return result.join('');
}

function wrapSoapRequest(webserviceUrl,requestHeader,soapBody,successFunc){
	var xmlWrap = [];
		xmlWrap.push("<?xml version='1.0' encoding='utf-8'?>");
		xmlWrap.push("<soap:Envelope xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'>");
		xmlWrap.push("<soap:Body>");
		xmlWrap.push(soapBody);
		xmlWrap.push("</soap:Body>");
		xmlWrap.push("</soap:Envelope>");
		xmlWrap = xmlWrap.join('');
	$.ajax({
		async:false,
		type:"POST",
		url:webserviceUrl,
		contentType:"text/xml; charset=utf-8",
		processData:false,
		data:xmlWrap,
		dataType:"xml",
		beforeSend:function(xhr){
			xhr.setRequestHeader('SOAPAction',requestHeader);
		},
		success:successFunc
	});
}

/*********************************
		Helper functions
*********************************/
// Function to separate each url search string parameters
function getQueryParameters(){
qObj = {};
var urlSearch = window.location.search;
	if(urlSearch.length>0){
		var qpart = urlSearch.substring(1).split('&');
		$.each(qpart,function(i,item){
			var splitAgain = item.split('=');
			qObj[splitAgain[0]] = splitAgain[1];
		});
	}
return qObj;
}

function init_fields_v2(){
	var res = {};
	$("td.ms-formbody").each(function(){
	var myMatch = $(this).html().match(/FieldName="(.+)"s+FieldInternalName="(.+)"s+FieldType="(.+)"s+/);
		if(myMatch!=null){
			// Display name
			var disp = myMatch[1];
			// FieldInternalName
			var fin = myMatch[2];
			// FieldType
			var type = myMatch[3];
			if(type=='SPFieldNote'){
				if($(this).find('script').length>0){
					type=type+"_HTML";
				}
			}
			if(type=='SPFieldLookup'){
				if($(this).find('input').length>0){
					type=type+"_Input";
				}
			}
			// Build object
			res[fin] = this.parentNode;
			res[fin].FieldDispName = disp;
			res[fin].FieldType = type;
		}
	});
	return res;
}

Save as “FiveDateColumnStatus.js” – mind the file extension and update the script “src” in the CEWP code to point to where you save this script.

Ask if anything is unclear!
Alexander