Add color coding to your SharePoint lists Using calculated columns

Introduction:-

This article describes; that how to apply color code to sharepoint lists or sharepoint documnet library.

This article will use below terms-

– OOB -> Out of the box.
– CQWP -> Content Editor webpart
– SharePoint calculated columns

Issue details:

Some time in our list or document library; customer want to see result by just looking at a glance. Hence they wish to see some color on records to identify up or down.
For example-
You have list where your team get some request forms and then they update forms and finally they submit to government.
Manager of department want to see that who submit within 2 days and who delayed than 2 days.

Solution Idea:

For any such need; you have to create calculated column to find result.
Then another column (which i will describe in this article) to show color coding.
Note- In this solution we dont need to develop any c# custom code and dont need of any deployment.

Solution Details:

1. For any such need; you have to create calculated column to find expected result.
2. Then another column to present expected result into desired color coding.

Both above columns; i will describe below in detail.

Lets take below example where you have receipt date and submitted date. Manger want to see green color if document submitted within 2 days or else red color.
below are the steps to implement

Step 1:

Create calculated column called “SubmissionDateDifference” and add below formula
=[Submission Date]-[Date of receipt]
As you see in formula and image; It will show the difference between 2 dates in number format.
Note; Don’t show this formula in your list view.

Calculated Coumn

Step 2:

Create another calculated column called “Is Submission in 2 days” and add below formula

1
="<DIV style='font-weight:bold; font-size:24px; color:"&CHOOSE((IF(SubmissionDateDifference<=2,2,1)),"red","green")&";'>•</DIV>"

Note: here the real logic is “CHOOSE((IF(SubmissionDateDifference<=2,2,1)),"red","green","orange")" which says its green if its <=2 red if its >2

Rest html is to present result in color.

Step 3:

Create a webpart page where you add this about list as list view webpart. The display would like like below where you red or green inside div.

list view

Step 4:

Below listview webpart; add a content editor webpart where you write below javascript

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
<script type="text/javascript">
 
//
// Text to HTML
// Demo: sharepoint.infoyen.com by Avinash Kumar Dad
//
var cellTDs = document.getElementsByTagName("TD");
var i=0;
var TDContent = " ";
while (i < cellTDs.length) {
try {
TDContent = cellTDs[i].innerText || cellTDs[i].textContent;
if ((TDContent.indexOf("<DIV") == 0) && (TDContent.indexOf("</DIV>") >= 0)) {
cellTDs[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="<TABLE><TBODY id=\"tbod"+ groupName+"_\" isLoaded=\""+isLoaded+ "\">"+htmlToRender+"</TBODY></TABLE>";
var tBODYTDs = wrapDiv.getElementsByTagName("TD"); var j=0; var cellTDContent = " ";
while (j < tBODYTDs.length) {
try {
cellTDContent = tBODYTDs[j].innerText || tBODYTDs[j].textContent;
if ((cellTDContent.indexOf("<DIV") == 0) && (cellTDContent.indexOf("</DIV>") >= 0)) {
tBODYTDs[j].innerHTML = cellTDContent;
}
}
catch(err){}
j=j+1;
}
tbody.parentNode.replaceChild(wrapDiv.firstChild.firstChild,tbody);
}</script>

After adding this CQWP; the result will look like below image

list color coding

Hope this helps.
Thanks!!
Infoyen

calendarJuly 1, 2014 · cardInfoyen · comments3 Comments
tagPosted in: List, MOSS, SharePoint, SharePoint 2010

3 Responses

  1. Danny Engelman - October 11, 2014

    If you set the datatype of your “HTML Calculated Column” in step 2 to datatype = Number, the content is NOT converted to Text displaying in the list (step 3) but actually interpreted as HTML.
    That means you do not need step 4

    Alas this only applies to Views, on Edit/New/Display Forms you would still see the HTML (and need step 4)

  2. Amby - December 17, 2014

    I am following the above steps, but still unable to see the html render. In the preview pane – view, used in the webpart, I still see the html code.
    Any ideas?

  3. Infoyen - December 25, 2014

    not sure.. Need to look into your code.
    Logically its problem in script which is not converting html into required color.
    Please debug script.

Leave a Reply

Spam Protection: , required

myworldmaps infoyen