Tuesday 6 April 2010

Show traffic lights in SP List based on Checkbox(Yes\No) column

I had to create a SharePoint list with traffic lights as shown below.


The decision for color of traffic light is from a Checkbox field.

One calculated field for each traffic light has to be created with formula like
="< d i v >< i m g src="http://www.blogger.com/_layouts/images/KPIDefault-%22&IF([Gate%201]=TRUE,"0.GIF","2.GIF")&"" /> < / d i v >"

 

The idea is to use the existing SharePoint KPI images.

Since the formula is based on Checkbox, the following IF condition will work.
="< d i v >< i m g src="http://www.blogger.com/_layouts/images/KPIDefault-%22&IF([Gate%201]=TRUE,"0.GIF","2.GIF")&"" /> < / d i v >"
"


Did NOT work:
="< d i v >< i m g src="http://www.blogger.com/_layouts/images/KPIDefault-%22&IF([Gate%201]=Yes,"0.GIF","2.GIF")&"" /> < / d i v >"

="< d i v >< i m g src="http://www.blogger.com/_layouts/images/KPIDefault-%22&IF([Gate%201]=1,"0.GIF","2.GIF")&"" /> < / d i v >"


It will just set the HTML tags to the calculated field based on the checkbox value.
How to make the browser to interpret the HTML tags and display the image.

Here comes the handy script from http://blog.pathtosharepoint.com/2008/09/01/apply-color-coding-to-your-sharepoint-lists/

A CEWP has to be added at bottom of the page and include the following script in the CEWP, thats all.
It does magic for you...

< s c r i p t type="text/javascript">
//
// Text to HTML
// Feedback and questions: Christophe@PathToSharePoint.com
//
var theTDs = document.getElementsByTagName("TD");
var i=0;
var TDContent = " ";
while (i < theTDs.length) { try { TDContent = theTDs[i].innerText || theTDs[i].textContent; if ((TDContent.indexOf("") >= 0)) {
theTDs[i].innerHTML = TDContent;
}
}
catch(err){}
i=i+1;
}
//
// ExpGroupRenderData overwrites the default SharePoint function
// This part is needed for collapsed groupings
//
function ExpGroupRenderData(htmlToRender, groupName, isLoaded) {
var tbody=document.getElementById("tbod"+groupName+"_");
var wrapDiv=document.createElement("DIV");
wrapDiv.innerHTML="
<><><><>"+htmlToRender+"
";
var theTBODYTDs = wrapDiv.getElementsByTagName("TD"); var j=0; var TDContent = " ";
while (j < theTBODYTDs.length) { try { TDContent = theTBODYTDs[j].innerText || theTBODYTDs[j].textContent; if ((TDContent.indexOf("") >= 0)) {
theTBODYTDs[j].innerHTML = TDContent;
}
}
catch(err){}
j=j+1;
}
tbody.parentNode.replaceChild(wrapDiv.firstChild.firstChild,tbody);
}
< / s c r i p t >

5 comments:

Anonymous said...

thanks for your post - this is exactly what I was looking for.
However, when I try to add the calculated column SharePoint tells me that the syntax is incorrect - can you double check your code?
Thanks!

Muthu Kumaran said...

Hi,
Thanks for your comments.
Since i was facing difficuly adding html tags and scripts in this blogger site, i have added extra spaces in html tags. You copy the code to notepad or any editor and remove the extra spaces in html tags. Need to find out how to include actual scritps and html tags in this blogger site without any problem.

Anonymous said...

Thank you for the prompt response.

I had actually removed the spaces but am still getting an error - I will try playing around with it again.

btw. I found this article explaining how to add code to blogger:
http://www.blogpandit.com/2009/05/add-code-snippet-to-blogger-mystery.html

Muthu Kumaran said...

Hi,
Thanks fot the link.
I guess, the %22 and %201 (encoded html) in formula is causing problem.
BTW, wats ur email id? We work on same technology, so we can help each other. Send an email to muthugp@gmail.com

Dave said...

Use Syntax Highlighter plugin for blogger to preserve code.
You then use PRE tags around the code in your blog post. http://alexgorbatchev.com/SyntaxHighlighter/integration.html

Nice idea for the traffic lights. In the context of using SharePoint for project management, how would you go about using Dates (e.g. if(Today() >= DueDate, Red,Green) something like that.

Can you elaborate on this post a bit more? You use a checkbox but what sort of field are you using for the traffic light? The instructions are a bit brief.