Using calculated columns to write HTML

Posted on by

Update [6/8/2009]: A new version of the script is now available, more details here.

Updates
I am now expanding the scope of this method:
– list views (flat views and expanded grouping): this article
– list views (collapsed grouping): this article
– display forms (DispForm.aspx): published on 10/01/2008
– calendar views: published on 11/15/2008
– using calculated columns to write scripts: published on 2/26/2009
– preview panes: added on 7/9/2009
– filters: not published yet
Also note the troubleshooting section.

Update [09/10/2008]
I have added a few lines to the initial script to address the case of collapsed views. Also, see my note at the end of this post.
Your feedback is important to me. Big thanks to Fernando, Jeff and the others who reported the limitations of the initial version!

A technical note: considering that 1/ the script is generic and 2/ it may still evolve in the future, a good practice is to store it in a separate text file on your site. You can then link to it from any Content Editor Web Part by using the “Content link” box.

I have already introduced calculated columns in a previous post.

One of their limitations is that the output is just text. Sometimes your browser will be smart enough to interpret the text as a hyperlink – when you calculate an e-mail address or a URL for example. Nevertheless in this case the display is usually not user-friendly. This will never allow you to get what a “Hyperlink or Picture” column does, for example.

To extend the possibilities of calculated columns, my idea is to use them to write HTML instead of just text, thus allowing some additional formatting. In this post I am going to show how to achieve this result through a generic method, combining calculated columns and the Content Editor Web Part.

Let’s start with a simple example based on a contacts list (I’ll provide more advanced examples in my next posts). For each team member, I store the first name, last name and job title. On my site home page, I want to display the list of contacts as “First name Last name”, and display the managers’ name in bold. It would look like the first column below:

 

A calculated column will easily give me the display name:
=CONCATENATE([First Name],” “,[Last Name])

But I have no option in the SharePoint UI to highlight the managers’ name.

So let’s apply my idea, and use calculated columns to write HTML instead of simple text.
The initial formula becomes:
=CONCATENATE(“<DIV>”,[First Name],” “,[Last Name],”</DIV>”)

I can now add my condition: if the job title contains the word “manager”, add bold style. In SharePoint, this translates as:
IF(ISERROR(SEARCH(“manager”,[Job Title],1)),”style=’font-weight:bold;'”,” “)

So here is my complete formula:
=CONCATENATE(“<DIV”,IF(ISERROR(SEARCH(“manager”,[Job Title],1)),” “,” style=’font-weight:bold;'”),”>”,[First Name],” “,[Last Name],”</DIV>”)

Let’s take a look at our list:

 

We now have the correct HTML…except that SharePoint is displaying it as simple text. We need to add a short script to change it into HTML.

So let’s add a CEWP to the bottom of our Web Page (how?), and paste the following script in the source editor: 

<script 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&#91;i&#93;.innerText || theTDs&#91;i&#93;.textContent;
if ((TDContent.indexOf("<DIV") == 0) && (TDContent.indexOf("</DIV>") >= 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="<TABLE><TBODY id=\"tbod"+ groupName+"_\" isLoaded=\""+isLoaded+ "\">"+htmlToRender+"</TBODY></TABLE>";
var theTBODYTDs = wrapDiv.getElementsByTagName("TD"); var j=0; var TDContent = " ";
while (j < theTBODYTDs.length) {
try {
TDContent = theTBODYTDs&#91;j&#93;.innerText || theTBODYTDs&#91;j&#93;.textContent;
if ((TDContent.indexOf("<DIV") == 0) && (TDContent.indexOf("</DIV>") >= 0)) {
theTBODYTDs[j].innerHTML = TDContent;
}
}
catch(err){}
j=j+1;
}
tbody.parentNode.replaceChild(wrapDiv.firstChild.firstChild,tbody);
}
</script>

Now the browser displays the strings as HTML, and my list looks like the initial screenshot at the top of the page!

A couple notes:
– The script identifies the to-be HTML by the opening tag <DIV and the closing tag </DIV>.
– I have tested the script in Internet Explorer and Firefox.
– Remember to drop the CEWP after all the views you want to change. You can for example put it at the bottom of the page.

In my next posts, I’ll show various examples involving styles, pictures and links. If you have a specific issue, feel free to submit it!

How about the Data View Web Part?

Of course such customizations could also be done through the DVWP. I am not going to detail this here, feel free to contact me for specific questions.
As I already mentioned, if I can reach the same result with either the CEWP or the DVWP, I’ll favor the CEWP as 1/it is a safer approach 2/the changes can easierly be undone and 3/it doesn’t require SharePoint Designer.

A note for the SharePoint experts

On SharePoint pages, scripts can be called by a function named “_spBodyOnLoadFunctionNames”. This allows for a timely execution of the script, on page load.
In this case, I am not using it, as we want the script to run before the page can be viewed by the users. Are there any side effects of not using _spBodyOnLoadFunctionNames? Advice on this is welcome!

 

 

 

For more examples using this method, see the “Calculated Columns” category.
I would be especially interested in your feedback if you have tested the script in one of the following situations:
– browser other than IE or Firefox on Windows
– non-Windows environment
– scalability (long lists, use across multiple lists)
– specific use cases, with formulas you have written yourself
– coexistence with other code or customizations (DVWP, etc.)
Feel free to leave a comment or contact me: Christophe@PathToSharePoint.com

304 thoughts on “Using calculated columns to write HTML”

  1. Pingback: EndUserSharePoint.com: How to create links, labels and coloring in a SharePoint list : End User SharePoint

  2. Chris,

    Script works great, I just have one question.
    When I make the list look ok all the font formating I do with HTML, but if I make a grouping withing that list I loose the HTML formating and it dispays simple text again (like not having the scrip)
    Do you know why? Can I solve this?

    Thanks

  3. Good point Fernando. The current script only runs on the items present when the page is initially loaded (whether they are visible or not).

    In SharePoint 2003, all items are pulled when the page loads, so the script will work for both collapsed and expanded groupings.
    In SharePoint 2007, the script only works for expanded groupings. For collapsed views, the items are only pulled later, on request (when the user expands a group), and they won’t be converted into HTML.

    Let’s see if we can improve this… suggestions are welcome!

  5. [Update] Several readers have reported the limitation with collapsed views in SharePoint 2007, and I have sent them a solution for this specific case. I’ll wait for their feedback before I publish the updated script.

  7. Hey Christophe,

    Thanks for posting this gem! I tweaked it a bit in order to make it work on a calendar view too. I had problems getting it to work across views (day, week, month) properly until I had the idea to change the theTDs array from loading with TD tag data to loading A tag data. This preserves the link and allows the native CSS to work … mostly.

    You can see my writeup on it here: http://moblog-banditben.blogspot.com/2008/09/sharepoint-calender-colorification.html

  8. Hi Christophe,
    I did a try on the above. I create a task library with the columns setup as above. Then I added in a Content Editor Web Part with the script above inside. The CEWP was placed just right below the task table but hidden.
    However, nothing happen. The browser didn’t displays the strings as HTML and I still see … from the column.

    Could I know how to make the formular in Calculate Column as HTML?
    Thank you.

  11. Hi Christophe,
    Thanks it works finally. Would like to find out the script above is it just applicable to the above function or it works for any html code written in Calculate Column? As I’m trying out the feature on the color coding (background color). Anything that I need to amend from the script in order to make it works?
    Thanks for your advise again.

  12. DL, if you read other posts on this blog you’ll find other examples, and I plan to publish a few more.

    How far can we go? I can’t tell. I hope other people will do like Ben and share their findings. On my side, I have for example tried to include simple events in my formulas (onmouseover, onmouseout) and it worked fine.

    This method answers many common requests, and I am a proud dad. But let’s not forget it’s just a trick!

  13. Christophe-It works. I just change the choose() to simple if() statement and it works fine now. Thank you for the codes.

    I have 2 more questions.
    1) can the calculate column refer to itself? Actually would like to disable the input if column is filled.
    2) can we compare same column figures but from 2 different records. Means in the List library, I have 2 records which contain a set of figures (Plan & Actual) and would like to compare the figures.

    Thank you.

  15. I have changed the formatting of the post to make the script easier to grab (click on “copy to clipboard”).

  17. Pingback: Jazz Up Your SharePoint Dashboards With Graphic Indicators « The WorkerThread Blog

  18. Pingback: Gantt view: a first test « Path to SharePoint

  19. Pingback: MindBusiness Blog » Formatierte Felder und Ampelfunktionen in SharePoint mit JavaScript

  20. Pingback: Troubleshooting your “HTML calculated column” « Path to SharePoint

  23. Pingback: SharePoint calendars: color coding, hover effects, etc. « Path to SharePoint

  24. Great find! I, however, can not get the java code to work properly. I have added it as a Content Editor Web Part and copied your code exactly. I have a column of Priority and the recomended code for the calculated column. The logic is working but the HTMl code is not being read in my view. Can you please give any suggestions? I am running SharePoint Services 3.0. Thank you!

  28. Hi Christopher,
    Would like to find out, can we make use of calculate column, to auto assign the permission?

    Where I have a Task Library and every task there is a Task Lead, which I’m making use of the field “Assigned To” that auto created by the library. Permission control is applied to the Task Lead. They only have the Edit (Contribute) rights for those task records which are assigned to them.

    My question is, is there a way to automate the permission? Means once I add the name of the Task Lead in the field “Assigned To”, sharepoint will automatically give them the Contribute rights for that record. Rather then we have to go into individual records to assign the rights.

    Any advise?
    Many Thanks
    Demi

  30. I’m trying to highlight a date red if its older than 30 days. I have two calculated columns, thus:

    =IF(DATEDIF(Date,Today,”D”)>30,TRUE,FALSE)

    =IF(DateHighlight,””&Date&””,Date)

    The First on is Called DateHighlight and just calculates if the date should be higlighted – the 2nd does the highlighting based on the first.

    I can get the thing to highlight. However, if this columns type is set to string… all the dates come out with values like 27,203 etc… if i set the type to a date – the non highlighted ones come out as formatted dates – the highlighted ones but the date still comes out as a string.

    Is there a way to get the string representation of the formatted date?

  31. sorry – some cut and paste went a bit wrong in my post… basically my problem is the highlighted red dates come out as numbers (37432) with no commas even if the type of the column is set to date. If the type of the column is text then all the dates come out as numbers. Please help.

  33. Pingback: Compare DueDate with Today in list view « SharePoint JavaScript’s

  34. Pingback: One way to present media with SharePoint - From SharePoint with love

  35. Christophe,

    Great serie of posts. really, really useful. I’ve created a contacts list with a calculated field for picture. If I view the list with the “normal” table style, the HTML trick works perfectly. If I change the style of the view to boxed (with labels) the HTML web part seems to change the formatting of all of my other fields so they all run into each other and so are much less useful. Is there anything I can do to change the code to help with this?

    Thanks,
    Adam

  38. Robin: I’ll certainly publish a post about formatting columns in a couple months, but I have nothing ready yet.

    What I am going to publish soon is a post about formatting rows (color, hide/display).

  40. Christophe, Thanks for the response. My formula is (I think!) pretty simple:

    =CONCATENATE(“”)

    When I look at the result of the calculated field (without the necessary HTML webpart on the page), it seems to generate the right code. When I add the webpart to any view other than one that is a boxed style view, it works perfectly and renders the picture without impacting on anything else.

    When I add it to a boxed view, the picture renders fine, but all of the labels and text in the other fields getconverted to a different font and all run together. I can’t quite se what I’ve done wrong, particularly as you get it to work on your world clocks demo.

    I’ve played about with the formula, but just can’t get anything to work. Any suggestions?

    Great series, by the way. Giving me loads of inspiriation to try many things!

    Thanks,
    Adam

  41. Sorry, forgot to put my code in the right format. My formula is:

    =CONCATENATE("<DIV><IMG src='http://server/sites/CSGC/Pictures/",&#91;Firstname&#93;," ",&#91;Lastname&#93;,".jpg' /></DIV>")

    Thanks,
    Adam

  42. Adam: I would first try to replace the blank in the URL with its escaped value “%20”. Also, check if any of the names includes a special character (apostrophe or space for example).

    I hope that you’ll share some of your ideas with us!

  43. Christope: I’ve worked out why I’m getting the odd behaviour, but not how to fix it!

    In my boxed style view, I was displaying my calculated column as column 1, so it appeared at the top of each entry and Sharepoint was therefore trying to make it link through to dispform.aspx for the individual item (as this is the standard Sharepoint behaviour for the first coumn in a boxed style view). If I move the column down so it appears as column 2 or a later column in my boxed style view, everything works fine! So it must be something to do with placing that calculated column as the first column in the boxed style view. The column works fine when it placed as the first column in any other style of view.

    I’m not a coder by any stretch of the imagination 😉 so if you can come up with a fix, that would be great. In the meantime, my work-around is to not make this column 1 (which isn’t too much of a problem)

    Cheers,
    Adam

  45. Pingback: JQuery for Everyone: HTML Calculated Column | End User SharePoint

  46. Hi Christophe,

    I’ve used your scripts from the HTML Calculated Column article to create a column showing a due date indicator icon.
    It works fine in the site where I created the column manually on a site column which was then added to a content type. The content type then added to the list on the site.

    When I then save that site as a template (.stp) and try to create a new site from the template I get the error:

    The formula refers to a column that does not exist. Check the formula for spelling mistakes or change the non-existing column to an existing column.

    I’ve tried everything including renaming all my columns in the Content Types and Site Columns but to no avail.

    We are going live with a project next week using these indicators and an event handler that generates a site from the .stp template but we have just tested the template today manually and it fails because of the HTML in the columns. I am in desperate need of your help at the moment.

    Is there a workaround for this issue?

    Thanks for any help.

    • Joe,
      Did you ever get the error “The formula refers to a column that does not exist. Check the formula for spelling mistakes or change the non-existing column to an existing column.” solved.

      I have the same problem. My calulated columns work just fine. When I create a site based on that site template, I get the same error mentioned above. However, when I check my newly created site, the columns work fine there as well (at least I think so).

      Any guidance would be appreciated. Thanks!

    • Hi Joe,

      I have exactly the same problem. Did you get a response or a solution? Is there a workaround?

      Thanks for your help.

    • Sorry, I missed this thread 😦

      Does the site column belong to the site you are saving as template, or to the top level site (site collection)? In the latter case, I wouldn’t expect the site column be saved in the .stp template.

      • Hi Christophe,

        Thanks for your response. Indeed, the site column belongs to the top level site. But what is strange is that it has worked for a certain time but for a few days it does not work anymore.

        I found a workaround by creating the calculated column in the list instead of in the top level site.

  47. Pingback: Christmas indicators « Path to SharePoint

  49. Adam: this is a difficult question.

    Let’s take two extremes:
    – if you include a simple onclick=’alert(“hello!”)’ it works, no problem
    – if you include the “script” tag and lines of code, the script will be included in the page but not executed.

    There are lots of variations around this, and I’ll publish more examples next year. For more information on this topic, search the Web for “script” and “innerHTML”. Note that the behavior also depends on the browser you use.

  50. Hi Christophe! I am trying to use your wonderful guidance, but after I add the script to the CEWP, I get a pop up that says “Cannot retrieve properties at this time”. The title bar of that pop up shows Microsoft Internet Explorer. Then it won’t let me save the changes made to the CEWP. Any ideas? I’ve tried it several times. Thank you.

  52. Pingback: World Clock « Path to SharePoint

  53. Pingback: SharePoint OOTB Trend Reporting | End User SharePoint

  54. Hi Christophe,

    Thanks for all of your wonderful examples so far! I’m wondering if you could help out with an adaptation. I’ve created a column type of Choice with multiple selections. Each selection would be a term such as “JQuery” or “CEWP” or “View”. I would like to portray these column values with the same text but in a format that would be accessible as a URL. So, if I clicked on the “CEWP” value in the list view web part, it would access the URL that I set in the calculated column. Does that make sense? 🙂

  56. Thanks, that answers some questions. One final question I have would be around formatting. In your example, you are working with discrete values. How would you go about applying this with several possible values that would originate from a choice column type? Could you provide the formatting of that conditional column? It would be something like this:

    If ColumnTitle=X then

    CONCATENATE(““,Title,”“)

    else if ColumnTitle=Y

    CONCATENATE(““,Title,”“)

    etc etc

  57. Hi Christophe and all,

    After working with some of the cacluated column examples you have shown in various posts, I was wondering if it is possible to include a “multiple line text” column in a calculated column. I have tried the default “desrciption”column , as well as one i have created neither will allow me to enter it in to a caclulated column. i was just wondering if it was possible or not. thanks again

  60. Hello Christophe,

    Great work on this concept. I have one twist for you though. Using the method you described above I was able to create a calculated column to formulate the “Full Name” of a contact in a SharePoint list. This works great until I get to the point where I wanted to reference this column via a lookup field in a seprate list. In the area where you select the full name to populate the contact lookup column in my 2nd list, the HTML is rendered as plain text. I was wondering if you had any throughts on how to capture those select objects in javascript and formulate them so that they render the HTML properly. Any suggestions on this matter are greatly appreciated.

    Thank You,
    Ryan

  61. Ryan: you could apply a similar method. In the edit form, search for the DIV tags and convert them to HTML. However this is very intricate, I think this is pushing the method too far…

  62. Hey there, thank you for the reply. I have actually come up with a solution to my original problem. Rather than trying to render the HTML elements within the column; I simply grab all of the “select” objects on the page, loop through their corresponding “option” objects, check the text property of the option object for the DIV tags, and if the tags are found then I run a regular expression against the text to remove all HTML tags from the text. This allows for the proper display in the select list, and leaves the value property of the option object with the DIV tags so the HTML is carried through to the new list.

    Something interesting that I noticed while creating this script was that it appears that javascript is run after the page is loaded to actually populate the select object as the options are not visible in the source of the page. Rather, the values appear in hidden input objects that I can only assume are used to populate the select object with javascript. With that being the case, my code had to be wrapped into a function that I pushed into the body OnLoad event. Something that I still need to address is that when an item is removed from the list the text in the pick list is set back to the text with html tags. I plan on having that issue fixed later today. I will let you know what I find out.

    My code if anyone is interested:

    //This script is for re-rendering select options.
    function ReRenderSelectObjects()
    {
    var oSelect = document.getElementsByTagName(‘select’);
    var x = 0;
    var y = 0;
    while(x < oSelect.length)
    {
    while(y < oSelect[x].options.length)
    {
    if ((oSelect[x].options[y].text.indexOf("<DIV") == 0) && (oSelect[x].options[y].text.indexOf("”) >= 0))
    {
    oSelect[x].options[y].text = oSelect[x].options[y].text.replace(new RegExp(/(]+)>)/ig),””); ;
    }
    y=y+1;
    }
    y=0;
    x=x+1;
    }
    }
    _spBodyOnLoadFunctionNames.push(‘ReRenderSelectObjects’);

  63. Hello everyone, I am finally finished with the code to correct the scenario that I stated above. I figured that since I asked a question, I should at least come back here and post my solution to the problem. My code is:

    <script 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&#91;i&#93;.innerText || theTDs&#91;i&#93;.textContent;
if ((TDContent.indexOf("<DIV") == 0) && (TDContent.indexOf("</DIV>") >= 0))
{
if(TDContent.match('>; <')){theTDs&#91;i&#93;.innerHTML = TDContent.replace(new RegExp(/>; </g),'><');}
else if(TDContent.match('>;<')){theTDs&#91;i&#93;.innerHTML = TDContent.replace(new RegExp(/>;</g),'><');}
else{theTDs&#91;i&#93;.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 theTBODYTDs = wrapDiv.getElementsByTagName("TD"); var j=0; var TDContent = " ";
while (j < theTBODYTDs.length) 
{
try
{
TDContent = theTBODYTDs&#91;j&#93;.innerText || theTBODYTDs&#91;j&#93;.textContent;
if ((TDContent.indexOf("<DIV") == 0) && (TDContent.indexOf("</DIV>") >= 0))
 {
if(TDContent.match('>; <')){theTBODYTDs&#91;j&#93;.innerHTML = TDContent.replace(new RegExp(/>; </g),'><');}
else if(TDContent.match('>;<')){theTBODYTDs&#91;j&#93;.innerHTML = TDContent.replace(new RegExp(/>;</g),'><');}
else{theTBODYTDs&#91;j&#93;.innerHTML = TDContent;}
}
}
catch(err){}
j=j+1;
}
tbody.parentNode.replaceChild(wrapDiv.firstChild.firstChild,tbody);
}
//This script is for re-rendering select options.
function ReRenderSelectObjects()
{
                var oSelect = document.getElementsByTagName('select');
                var replaceExp = new RegExp(/SelectCandidate/);
                for(var x=0; x<oSelect.length; x++)
                {
                                CleanOptionsText(oSelect&#91;x&#93;);                  
                                if(oSelect&#91;x&#93;.id.indexOf('SelectCandidate') > 0)
                                {
                                                var selectId = oSelect[x].id;
                                                var btnId = selectId.replace(replaceExp,'RemoveButton');
                                                var spMasterName = selectId.replace(replaceExp,'MultiLookupPicker_m');
                                                var eventHandler = function(){CustomRemoveItemEventHandler(this);};
                                                var objRemoveBtn = document.getElementById(btnId);
                                                if(objRemoveBtn){objRemoveBtn.onclick = eventHandler}
                                                var objSelected = document.getElementById(selectId.replace(replaceExp,'SelectResult'));
                                                if(objSelected){objSelected.ondblclick = eventHandler}
                                                eventHandler = null;
                                }
                }
}
function CleanOptionsText(selectObj)
{
                var removeExp = new RegExp(/(<(&#91;^>]+)>)/ig);
                for(var y=0; y<selectObj.options.length; y++)
                {                              
                                if ((selectObj.options&#91;y&#93;.text.indexOf("<DIV") == 0) && (selectObj.options&#91;y&#93;.text.indexOf("</DIV>") >= 0))
                                {
                                                selectObj.options[y].text = selectObj.options[y].text.replace(removeExp,"");
                                }
                }
}
function CustomRemoveItemEventHandler(callingObj)
{
                if(callingObj.tagName == 'BUTTON')
                {
                                var replaceExp = new RegExp(/RemoveButton/);
                                var spMasterObj = window[callingObj.id.replace(replaceExp,'MultiLookupPicker_m')];
                                GipRemoveSelectedItems(spMasterObj);
                                var oSelect = document.getElementById(callingObj.id.replace(replaceExp,'SelectCandidate'));
                                CleanOptionsText(oSelect);
                }
                else if(callingObj.tagName == 'SELECT')
                {
                                var replaceExp = new RegExp(/SelectResult/);
                                var spMasterObj = window[callingObj.id.replace(replaceExp,'MultiLookupPicker_m')];
                                GipRemoveSelectedItems(spMasterObj);
                                var oSelect = document.getElementById(callingObj.id.replace(replaceExp,'SelectCandidate'));
                                CleanOptionsText(oSelect);
                }              
}
_spBodyOnLoadFunctionNames.push('ReRenderSelectObjects');
</script>

    P.S.: Something that you may consider is placing this code, as well as the code provided by Christophe, at the bottom of the master page used by your SharePoint sites. The code is very lightweight so running on every page is not a big concern of mine, and having the code on the master page meens that you do not have to place this javascript on every page you want to render the html; thus encapsulating all of the logic for future updates. Just a suggestion.

    Thank You,
    Ryan

  64. Hi Christophe,
    It worked!!! What I had completely ignored was your instruction about placing the CEWP AFTER(below )the list . I changed that, and everything works great. Thanks so much. You’re such a genius!! and this blog is extremely helpful and very clear.

  65. The JavaScript is working wonders for me on my Contacts list. My goal was to combine the First Name and Last Name fields, plus make them link to the display page for that contact (by adding the ID number to the end). It works well, however, I can’t get it to work on the home page of the site (Default.aspx), where I have added the contact list as a web part.

    I have definitely added a CEWP to the home page and inserted the JavaScript code, yet the Full Name column just displays the raw HTML code.

    Have I done something wrong? Or isn’t the JavaScript written to work anywhere besides the contacts list? I unfortunately don’t know JavaScript anywhere near well enough to modify the scripts. Any advice?

    Cheers,
    Dave

  67. Ah, thank you very much. I had placed the CEWP before the contact list. It’s all good now. Thanks for the quick reply too.

    Cheers,
    Dave

  69. Chris,

    First, thank you for the script. It has solved a major problem I was having, and is a great solution, and works great.

    But, and isn’t there always a but…

    I have found that when one adds the script to a page, different browsers behave differently. More precisely, different browsers are displaying the page at different speeds.

    I ran some tests, and timed how long it takes when you click the link to the “coded” page, to when it’s fully loaded. The results are:

    No Code, Internet Explorer 13 seconds, Firefox 5 sec, Chrome 3 sec
    Your Code, Internet Explorer 57 seconds, Firefox 8 sec, Chrome 4 sec
    Ryan’s Code*, Internet Explorer 63 seconds, Firefox 7 sec, Chrome 5 sec

    Why is IE taking so long to load the page?

    Normally I wouldn’t care, but the organisation I work for has IE as their standard browser, and for a page (on the intranet) to take around 1 minute to load would be seen as unacceptable.

    Any ideas?

    *note: Ryan’s code was the variation published to this thread on February 3, 2009 at 4:03 pm.

  70. Thanks for the feedback James.

    First, it’s good to know that the code works in Chrome, I haven’t tried it yet.

    I have also noticed that Firefox was faster, but I don’t see such a big difference…

    My code could be optimized. The sample script I have published scans through the whole page, but we could restrict it to the Web Parts, or to one specific Web Part. How many Web Parts do you have on your test page? Feel free to send me more details by e-mail:
    Christophe@PathToSharePoint.com

    Also, Paul Grenier has proposed an adaptation of the script using jQuery, it would be interesting to see how it behaves in your environment:
    http://www.endusersharepoint.com/?p=995

    I’d love to hear feedback from other readers on performance tests!

  71. Hey there,

    Unfortunately the performance of a javascript is very hard to predict and/or debug due to the fact that the code is executed on the clients machine.

    In my implementation I observed no noticible delay in performance. If I had to guess, the most likely culprit would be the speed your client browser is able to retrieve the html objects from the page.

    Sorry I couldn’t offer a better answer.

  72. Hi

    This works great for the “default view” in a List, but I can not get the code to work on any personal views created based on the default view. How do I add the code to the maser so it automatically applies to all screens?

    Thanks!

  73. OK, I have something that I would like to do with a calculated column I use on a calendar view. What I’ve done is built a custom list that provides my department the ability to track infrastracture and technical change requests for dev, QA, and prod environments. We’ve implemented our own scheduling and tracking as the change request system has no real way to manage those change requests effectively, and most importantly , no way to see any meaningful schedule.

    So, on the calendar view, I want to be able to have an additional link that takes the user straight into the change request system to view the change request, in addition to the standard one that displays the form view in SharePoint.

    Any way to do this?

    The formula for my calculated column is:

    =””&[ECM No]&” – “&IF([SCT Cross Impacted]=”Yes”,”(Non-SCT) “,””)&[ECM Brief Title]&IF([Major Tech Event]=”Yes”,” (Major Tech Event)”,””)&IF(Approved=”Yes”,” [Approved]”,” [Not Approved]”)&” Open ECM”

    And it creates values for the column like this:

    625538 – The Title of the Chage Request [Approved] Open ECM

    When displaying the calendar view, it seems the IE picks up the URL and displays it on the status bar, but clicking the link always goes to the SharePoint form view. I suppose it is essentially an tag inside an tag, which I do not believe works. How to I get my own tag to be after the one SharePoint creates for the calendar item?

    Thanks!

    Tony

  74. Tony: I assume that you’re using my script for calendars, not the script from this post.
    That’s correct, SharePoint links to the form view. Your custom links are buried under the SharePoint one and don’t work (btw this is mentioned in the post).
    To achieve your result, you’ll need to modify the script so that it delete the default link before inserting your custom link.

  75. Pingback: Using calculated columns to write scripts « Path to SharePoint

  76. Pingback: Highlight rows in SharePoint lists « Path to SharePoint

  77. Hi Christophe. Thanks, I was pretty sure I’d have to modify some Javascript. I was not using your script, but one I wrote, which I did to give me colors based on status. I’ll switch to your script, get it working, then try to modify it so I can rework the “a” tag for the calendar items, and produce two “a” tags.

  78. OK, I’ve been able to modify the HTML for the two “A” tags, getting my calculated one in serial with the parent one SharePoint creates, not as a child node. But then I saw in the HTML that SharePoint generates that there is an OnClick attribute for the “TD” that is the parent node of both of the “A” tags. Would that override both child “A” tags hrefs?

    Here’s the code I have thus far, admittedly not too clean, but I am trying to stay out of coding and development (and into PM, org building, and methodology/process stuff).

    var theTDs = document.getElementsByTagName(“TD”);
    var j = 0;
    var TDContent = “”;

    for (j = 0;j < theTDs.length; j++)
    {
    if (theTDs[j].className==”ms-cal-monthitem” || theTDs[j].className==”ms-cal-defaultbgcolor”)
    {

    theTDs[j].innerHTML= theTDs[j].innerHTML.replace(/</g,”);

    //theTDs[j].OnClick = “void();” — I’ve tried removing the attribute, setting it to an empty string, etc. value never changes.
    TDContent = theTDs[j].innerHTML;

    if ((TDContent.indexOf(“=0 && (TDContent.indexOf(““) >= 0))
    {

    var A1PosSt = 0;
    var A2PosSt = 0;
    var A2PosEnd = 0;

    A1PosSt = TDContent.indexOf(“<A “);
    A2PosSt = TDContent.indexOf(“<A “,A1PosSt+1);
    A2PosEnd = TDContent.indexOf(““,A2PosSt+1);
    theTDs[j].innerHTML = TDContent.substring(A1PosSt,A2PosSt) + ““;
    theTDs[j].innerHTML = theTDs[j].innerHTML + TDContent.substring(A2PosSt,A2PosEnd+4);
    }
    }
    }

  79. Pingback: Live demo: jQuery sparklines « Path to SharePoint

  80. Pingback: JQuery for Everyone: AOP in Action - loadTip Gone Wild | End User SharePoint

  81. Pingback: JQuery for Everyone: $.grep and calcHTML Revisited | End User SharePoint

  82. One little question, if I do it the way described above, all my special characters are replaced by the html code:

    a href=” –> a href='
    '><img

    It works in the browser and is shown correctly, but the source code of the page looks really not fine. How can I change this?

  83. Selinee:

    Are you looking at the source via View > Source? If so, it won’t look correct – it is the original source for the page. Use the IE Developer Toolbar. You may have to install it. This will show you the final HTML on the page.

  84. If you add the supplied script to the bottom of your master page or, better still, as an include with a function call to it at page bottom, might it then become available to any and all web parts on a given page?

    Or is it reliant on the original list view containing the calculated column??

  85. Has anyone considered adding a custom class to the DIV tag in order to avoid any possible interference with page layouts when using web part pages? Is this a factor?

    e.g. if ((TDContent.indexOf(“<DIV class=”2HTML”) == 0)

  86. Paul: I haven’t seen any case of interference so far. Keep in mind that the script is only looking at text fields and will ignore everything that is already html.
    Your solution should work however – just make sure it doesn’t interfere with an existing “2HTML” class 😉 . The idea is the same, find a way to identify the text that needs to be converted.

  91. Pingback: Beyond Training’s Blog

  92. Hi Christophe,
    Would like to find out.
    Can we make use of the Calculate Field to select the workflow we want? Example, I have workflow A, B, C and approvers have been preset in the workflows. When user upload document, they will base on the need of the document and trigger the workflow. However, I do not want them to amend the approvers name in the workflow. I know if workflow is triggered manually, user is allowed to edit the information inside, which I do not wish to.

    Hence, could we make use of the Calculate Field to carry out a check and trigger the correct workflow?

    Any advise?
    Thanks in advance.

  93. Pingback: SharePoint Color Coded Calendar - The PayRoll Schedule | End User SharePoint

  94. Christophe, is there any reason why this shouldn’t work with data rendered by a Business Data List View? It results in TDs on the page which contain DIVs with a link in them, but it does not convert to html on the page. Your CEWP is at the bottom of the last webpart zone on the page as recommended. I’m puzzled.

  95. JoyceMR: my sample code assumes that DIVs are in upper case and in the beginning of the string. Is it the case for you? If not, you may need to teak the code (lines 15 and 40).

  96. Yes, my DIVs are uppercase and are visible as text in the BDC List View web part fields on the rendered web page, but after further research I find that that they are not visible when I do right-click View Source on the page. SharePoint must be doing something complex with the BDC controls that doesn’t let us get to the source to change the display. Rats!

  97. Thanks a lot Christophe: that’s really a key achievement to enhance end-user ability to quickly customize their pages.

  98. I cannot use the content editor for this, so I was wondering if it is possible to incorporate it in the default.aspx of the site so that when I display the list on the main page, it will show the calculated field.

    So far, I added the code above to the default.aspx, and the code shows up in the web page, but does not convert the DIVs

  99. You can add the code to default.aspx or to the master page, just make sure that you place it at the bottom of the page so that it runs AFTER your list is loaded.

  100. I did that and can get the code to show up in when i view source, but it does not change anything. After that, I found another web part called “Form web part” and put it in there. I again got the code to show up, but it is not translating the DIV.

    I noticed that the entire content for the list item shows up on one line. Don’t know if that matters. The return for the field….

    Wait….

    Just got it. I moved my web part to the last in the list, and it worked. Boom Baby!!!

  101. hi Christophe,
    I am very thankfull to ur work.
    i am having one task like this.
    I am having list columns with tuype choice by values red,green and yellow.
    when the user one click on new button in list and he will enter his data and choice one color from choice column and click ok. now when we see in list choice column if he select Green background color of column must be Green.
    Can u help me in this way.

  102. Pingback: Ezra’s Software Development Notepad » Reporting on Sharepoint data using the Sharepoint content database

  104. Hi Christophe. Thanks for this, it ‘almost’ provides the perfect solution for me!
    I am using it on a Calendar view, and it works perfectly *unless* the item only spans one day, i.e. The End date for the item is the day after the Begin date (the item would be contained in just one cell of the calendar) – in this case, the html just appears as plain text. The rest of my calendar items, which span more than one day, appear in lovely multicolor!.
    Any ideas ??

  105. Hi again. One little extra point – with the color coding in place, the calendar item text is no longer a hyperlink to the calendar item. Is it possible to retain the link behaviour ?

  106. Nigel: which script are you using?
    As explained in the beginning of the post, this script is for lists, there’s another version for calendars.

  107. I seem to be having problems with this in Firefox/Chrome. I can see the script working, but it does not render the bar on the page, it just shows up as blank space.

    I’m using the list script on a web part with the javascript embedded on the very bottom of my masterpage.

      • Sorry, I didn’t expalint it well and or commented on a wrong post. It’s just a html calculated value that displays a full percentage.

        =” ”

        Using the above script you provided, this is rendered as a progress bar and works fine in IE. In Firefox and Chrome, the text is removed from the view, but is not replaced with the progress bar.

        Other posts indicated that it did work in other browsers.

  108. Removed some of the code so it would post.

    DIV style=’background-color:LimeGreen;’>DIV style=’background-color:red; width:”&([Filled Seats]/[Total Seats]*100)&”%;’> </DIV

    • Disregard my question, i forgot to add   between the divs for it to render appropriately in Firefox and Chrome.

  109. This is awesome since we don’t have access to our server or central admin. We are using the traffic light (large bullet) option. Question boss has is: Is there a way to show something else in the filter dropdown in the column heading. Now it shows html code. is there a way to show even words like Red, Yellow, Green so we can filter to see only the projects that are red?
    thanks for all your hard work

  110. Also, would be cool if when user moused over the traffic light it actually showed the calculated percentage. Is that possible?

  111. Scott: I am aware of this filter issue, unfortunately I don’t have a clean solution to share yet.

    You can add the mouseover behavior by changing the formula: simply add a title attribute to the DIV tag.

    • I’ve made this script work in a DVWP and in there you can us the “regular” column as the header and substitute the “formatted” column in the table cells – that way the data filters properly and displays propertly. I’ve used the cacluated fields on other custom views and forms – works great! Much more scalable than lots of conditional formatting. Thanks for the post!

  112. Note: the web content part must be on the bottom of the page for some reason.. probably to execute after the rest of the page is rendered. If its at the top you will just see the div code..

  113. Pingback: HTML Calculated Column – Updated script « Path to SharePoint

  114. Hi, I used the above script on some document libraries and it worked great! I am having issues getting it to work with lists though. I see that the code is indeed added to the page through the CWP but the HTML is still showing. Is it possible there is an issue with the script? Have you updated it recently?

    Thanks great post!

  115. I seemed to have narrowed the issue down to grouping. When the list/document lib. is grouped by a value and not expanded the code works. When not grouped it fails. Looking at the code to see if I can figure out the issue.

  117. Todd: I regularly update the “HTML calculated column” method, and you can read a summary at the top of the post. The script is widely used, in particular by my own customers.
    Yesterday I just published v 2, so you may want to try it out (no documentation yet):
    https://pathtosharepoint.wordpress.com/2009/06/04/html-calculated-column-updated-script/

    Also remember to check out the troubleshooting section (see link in the post).

    If it still doesn’t work, feel free to contact me with more details (list type, view style, etc.) at Christophe@PathToSharePoint.com

  118. I believe my issue was that I had the CEWP WP at the top of the page. Thanks for your quick responce and all of your helpfull effort!

  119. Pingback: Color coding: more examples « Path to SharePoint

  120. Pingback: Add Status Style Icons to SharePoint List « Rock Webcast

  121. Pingback: Turbo charge your sharepoint lists by using calculated columns to write HTML - PeteStilgoe.com

  123. Pingback: Case study: KPI roll-up in MOSS (Part I) « Path to SharePoint

  124. Pingback: HTML Calculated Column – Updated script « Path to SharePoint

  125. Thank you so much for sharing this, Christophe! I had already constructed the HTML I wanted in my calculated column, but was at a loss when it came to getting SP to display it properly. Your updated code flawlessly tackled the problem, however, and it’s now my list is doing exactly what I wanted. Fabulous!
    Thanks again!

  126. Pingback: HTML calculated column and Data View Web Part « Path to SharePoint

  128. Christopher,

    What can I do if I do not have access to CEWP???

    Any other way to make it behave as real HTML???

  129. Hi,
    I must be doing somethign wrong but not sure what.

    What I want to do is this.
    I have a calendar list, and I’ve added column named ‘Number Type’ when the user makes an entry, they must select from 3 choices.

    So what I want to happen is on the page I’ve added the calendar view, depending on what choice is select, the title will be a different color.

    I’ve added the following calculated column to my calendar list.
    =” “&Title&””

    I’ve then also added the DWP after my calendar webpart on my page.
    But the ‘Title is not changing colors at all.

    I’ve went back to the calendar list, and when I view it in list view. The ‘display’ field, lists out the HTML and for each entry it seems to be listing the correct color.

    So it seems the DWP is not working, or I’m doing something wrong.

    Any ideas?

  130. hmm, looks like my code didn’t all appear, but I used the generator to get my code, and like I said the HTML code looks to be selecting the right color. It’s just not doing anything on my page.

  132. Pingback: SharePoint Steve » Running SharePoint Code with Elevated Privileges – A Real Example

  133. Pingback: The HTML Calculated Column, one year later « Path to SharePoint

  134. Chris,

    Thank you for this.

    I am on a hosted 2007 WSS environment and am wondering if you know of any server settings that would prevent your solution from working. I have implemented other text/javascript CEWP without trouble but not on the Calc Column. I have aslo experienced difficulty implementing Lytebox (in case it helps to understand teh issue).

    As far as I know, I have followed your instructions (Exact replication of your example above with no code changes and have added the CEWP under the target WP with your ecat script).

    It does not display a read-out of the Calc Column formula but simply the concatenated first and last name with no bold formatting.

    I appreciate any advice you can give. Thank you.

    -JL

    • Jeremy, if you cannot see the div tags on your page it means that the solution is working. There may be a typo in your formula that prevents the styles from applying.

  135. Hi, thanks for this, it’s working great, but I run into 1 issue when adding or modifying a item. I have a calculated column with a link to DispForm.aspx to display 1 item, so the calculation is based on the ID (ID=”,ID,”). Works like describe. But now when I add a new item or edit a exsisting one, the ID is not in the url. Only when I go to List settings, open the calculated column and save it (without making changes) the ID’s are added to the links. How can I et this to work that the ID is displayed without doing this kind of action? Your help is much appreciated.

    Best regards,
    Michael

  136. Thank you, what a hack! I can’t blame ya after SharePoint being such a leaky abstraction…

  137. Pingback: Live demo: animated countdowns with Flash and jQuery « Path to SharePoint

  138. Has anyone else run into issues with this script in SharePoint discussion lists?

    I added the script to my template and it worked like a charm allowing me to add nice icons to usually boring lists. However, when I tried to reply to a discussion thread the browser hung completed (IE8 – Firefox pops up a message saying a script is having trouble and allows you to stop the script).

    I removed the script from my master and the reply works fine.

    Any thoughts?

  139. Hi
    I tried to use this script. When I used this script for test – just for two entries ..it is working fine.

    However, Now i have 2 categories and almost 600 events-Then I am getting message as “Please wait while scripts are loaded”..

    Any thoughts?
    Thanks
    Krishna

  140. Thanks for all the great info and ideas. They’re working great for me. I’m just having problems making changes to calculated fields on a list that has populated information. Is there anything I should be doing in the calendar settings? Even though I’ve tested the formulas, and they are correct, I can’t make a change without getting an error. Please let me know if you have any ideas.

    Thank you.

  141. Christophe – using the above script, it is blanking all my text. My condition: I have used ithe calculated column in a seperate list and then am using a Look up List on which I use the CEWP to place your script. Any clue why it is blanking the text completely? Ramesh

      • Well, I am using the Calulcated colum and calling for comparision of data that is in a seperate filed which is actually a Look Up filed. The script you have given works. But, it is blanking all data dn nothing is displayed. What is the Tag “TD” in the list. Do I have to change anhthing to make it respond or I can use the script as it is?

  142. Pingback: ÁghyBlog » Listatrükkök

  143. Hi guys,

    Marvellous Job. But just a request.. Rather then interlinking the content it’s better to ellaborate the steps and mark them in Bold (Actions). That will help begineers to understand easily.

    Keep it up. Cheers!!

  144. I wonder if anyone is still folllowing this discussion….

    I have followed this procedure and my calendar entry isn’t colored but my “calculated field” shows the following HTML

    Live Free!

    I added the script to my CEWP at the bottom of my calendar page. Does this help troubleshoot? I’d like to make this work.

  145. Hi,
    I have i custom list. It has status (choice field). For this column i want to show background color as per the choice selected. So i created 1 calculated column with HTML calculator . I also added content webpart with the above script below that list. But this is not working. The formula itself is getting displayed against each row. Please help.

  146. Pingback: SharePoint Computed List Field showing Links « Code Sparks

  147. I see that if the user has only read permission to the site, he see the HTML code only and not the rendered view. the script is not working in this case.

    please help

  148. In sharepoint, i have a column named [Suspense] that uses dates (date & time format) we input and compares today’s date to change the color font in the [Suspense] column. I am looking to see if there is someway to change the font of another column named [Subject] based on the suspense date and today’s date. I would like to have the subject column fonts to be red if we are within 24 hours, yellow if between 24 and 48 hours, and green if it is greater than 48 hours. Any help is greatly appreciated.

    Kalani

  149. Sadly you cannot use the [Today] function in calculated columns. One way around this is to use a workflow which triggers after midnight to set your “Suspense” flag. This can then be used to colour code the way you want it to using TextToHTML.

  150. I have been using this for a long time and recently added a CQWP that does a list rollup from multiple lists in subsite from the parent. The subsite lists include this indicator and I would like to have it included in the CQWP rollup (ie dashboard style). When I try to do this the output looks like

    string;#•

    the actual code that is rendered in the html is
    string;#<DIV style=’font-weight:bold; font-size:32px; color:silver;’>•</DIV>

    not sure what the deal is here and I didn’t see anything similar in the posts. Any ideas would be helpful. Thanks

    • Scott, here is the good news: the method works even better with the CQWP than with regular lists. Search my blog for CQWP or KPI, and you’ll get your answer. Also note that an alternate option is to use a DVWP (again, search for KPI on the blog, and expect a couple more articles on the topic in the weeks to come).

    • I have tried this method in SharePoint 2010 and it does not seem to work. Is there anything different that needs to be done for SP 2010?

  152. This is awesome. Just used it to format a link to a subsite that is created on item creation. It saved me from needing to populate a hyperlink column in code. Thanks for the great post!

  154. prob imp exp color
    1 1 0.2 •
    1 5 1.0 •

    it is getting mplementted this way…..i am not able to understand what is wrong..please give geedback on the same

  155. Hi Christophe,

    Thanks for a great tip. Is there a way to verticaly align the bullet point? It seems to be a touch low and not quite in line with the text of the task list item I have.

    Many thanks,
    Matt

    • Matt, you’re correct, as we use a bullet it is aligned with the bottom of the row.
      To fix this, you can:
      – use a different symbol (search my blog for “unicode”). I would recommend this approach.
      – modify the formula to center the bullet in the cell.

  160. Great post but i’m having issues with my column.

    I am trying to search for a word “internal” in the single line of text column [Abstract], then if it contains that word i would like my new column to have all the info from [Abstract] and make it bold and colour formated:

    =” “&Abstract&””

  161. Hello,

    This script works perfectly when I use lists that aren’t grouped. When I use a grouped list however, I just see the code. If I remove grouping, it processes the HTML correctly again.

    I am using SP2010, is there anything else I need to do or should be aware of to get this to work?

    Thanks.

    • The code was written for SP 2007, and breaks in SP 2010 for several features (grouped views, calendars, view refresh). I don’t have a perfect answer, see my 2010 posts for suggestions (category: HTML Calculated Column).

  162. Hi

    I did read the troubleshoot section and still my script does not get executed in the CEWP section and all i see is the in the status field.

    What needs to be changed?”

    I tried to create 2 columns as statuslevel as Choice field and status as calculated column

    Values for statuslevel are
    (1)Low
    (2)Medium
    (3)High

    formula for status is

    =”•”

    Sourcecode

    //
    // 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("<DIV") == 0) && (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("<DIV") == 0) && (TDContent.indexOf("”) >= 0)) {
    theTBODYTDs[j].innerHTML = TDContent;
    }
    }
    catch(err){}
    j=j+1;
    }
    tbody.parentNode.replaceChild(wrapDiv.firstChild.firstChild,tbody);
    }

    • Your formula didn’t come through…
      I’d recommend that you read my other posts about color coding, and start with a simple copy/paste of my examples. Also, pick the latest version of the script (2.1.1).

  163. Hi Christophe, I’m new to SharePoint and development in general so I’m a bit lost.
    I want to use indicators (diagrams) here but I don’t know where exactly to place the formula within the code. In your formula with indicators you use a CHOOSE statement, I’ll be using an IF statement.
    I want traffic lights to display if values are under 50, 51-70 and over 70; the IF statement is easy and I can do that and I’ve successfully displayed a single traffic light in the column (all rows) without any calculations. I’m not sure where to place place the IF statement and the syntax.

    • Ade, I’d suggest that you search my blog, there are lots of examples that use this method. In your case, search for “gradient” or “progress bar” and you should find some useful examples.

  164. Great article. I have a problem I can’t seem to solve.

    I want to concatinate 3 columns. I want the title, topic and url. I want the title on the first line, topic on the 2nd and url on the third. I only want the Title to be bold. Here is the code minus the bold.

    =CONCATENATE(Title,CHAR(10),Topic,CHAR(10),URL)

    I tried various <DIV styles, etc. and , etc to bold title but it does not work. Any idea how to make title bold

  167. do i need to change anything the java script?

    i have added CEWP with script but html is displayed as plain text.

    • I’d recommend that you visit the troubleshooting page (see link in the post). There is nothing to change in the JavaScript, but here again follow the links to get the most recent version.

  168. I saw your post and found it very interesting. Thank you. I am trying to get more information on using the DVWP with HTML. I am trying to create a dashboard of sorts for management. They want a stoplight effect based on the status of an item. If item is past due date then red image, if completed before due date then green image…etc. To do this I was thinking of using HTML w/ DVWP since views are essentially static and they want real time stats. Currently the html displays as text in the DVWP and does not render properly. I am not using images just html that looks like an image (•) Any assistance would be greatly appreciated!

  169. Pingback: Openen van URL’s in een nieuwe browser in SharePoint « Joost van Lier's Blog

  171. Pingback: Essential Links for the SharePoint Client Side Developer | John @ Enable

  172. Thanks…this was a life saver. Used it to create a navigation menu of links (left side nav content) that had to go to a URL listed in another column. Worked like a charm!

  173. I have a Gantt chart I needed conditional formatting on. Wanted to do this from designer by converting the list to a XSLT Data View in SPD, but found out quickly that a Gantt is not able to be converted.

    I have implemented your Text to HTML Web Part. It works fairly well. There is an issue with the amount of time it takes the page to load. Do you know of any solution to have the HTML render faster?

    I saw on your blog concerning this the note to SharePoint experts. It mentions the “_spBodyOnLoadFunctionNames” function. Would this be something that I could implement to speed things along?

    • Anthony, have you tried the latest version of the Text to HTML script (v 2.1.1)?
      I hope to publish more performant versions this year. Also, note that the speed depends on your browser, IE 6 and IE 7 will be much slower than “modern” browsers.

  174. Folks, I am a newbie and have a script that is not showing the image in my sharepoint list but is instead showing the evaluated javascript section. I am sure the answer to this is very simple but I can’t see what I am doing wrong.

    I have a sharepoint list and have two fields
    Architecture Comfort – this is a choise field with Red, Amber and Green as the choices
    Architecture.Status – this is a calculated value based on the above field

    So if the Architecture Comfort choice selected is RED the following is displayed instead of the image

    Red

    I have entered the following in the formula section:

    =IF([Architecture Comfort]=”Green”,” Green”,IF([Architecture Comfort]=”Amber”,” Amber”,IF([Architecture Comfort]=”Red”,” Red”,”N/A”)))

    I would appreciate any feedback

  176. Have you been able to get this working for SP 2010? I’m testing some views where I have used this script and it works perfectly in SP 2007. When I try the same thing in SP 2010, it renders the HTML correctly in a “flat” view, but not with a grouping view.

    Thanks again for this helpful script, Christophe!

  177. this is awesome! Thanks for the info. I do have one question – and I apologize if I missed this in the forum – is there a way to change the filter results so that I see the result from the html vs. the code?

    Thank you!

  179. Pingback: Add image to custom list with calculated / conditional column. « Pratik's SharePoint Blog

  181. Hi Christophe,

    I tried the traffice light code, it works fine on one sharepoint site but not on other….. What could be the reason?
    Anyway thanks for sharing your knowledge

  182. Hi,

    To elaborate the problem, on one site i get the colored bullets but on other it gives me html code, I checked the code agian and agian.
    Pl.let me know what could be the reason?

  183. Hello
    Thank You for the Useful Article
    i tested it on my shp2010 list and it worked but the problem is , when i add the CEWP my list dashboard Disappears 😦
    may i ask you to write an article like this just for the shp2010 ?
    Regards

  184. Pingback: A Few JavaScript/jQuery Best Practices « Cory Peters

  185. Is there a way this script can be modified so that I could drop the CEWP in on a New or Edit form and i could ahve text that is in the description of column appear as HTML? (Of course it would not be rendering a caluclated column at that point, but the overall description of a column in the form instead)

  186. Hi Christophe,
    I really like your solution… clean and simple… However, I have been trying your step by step example many times in different servers, but still no luck… basically script in CEWP bring no effect on rendering the HTML tag… it does not error or anything…

    My environment is SP 2010, I wonder if there is settings on SP I should be changed to allow the script to work?

    Many thanks!
    KC

  188. Hi!

    Very good the post. I have a question i did it and it is works fine, but… when i put a group by in the list it is not working? Do you know the solution?

    Thanks

  190. Hello Christophe,

    How do i modify this formula to add one more choice (4) Not Applicable

    in this formula at add one more light.

    =””

    I have a choice column with (1) Completed,(2) In Progress and (3) Not Started.

    Your help will be greatly appreciated.

  192. Christophe,
    Thanks, this color coded R/Y/G works great in SP2007, but we just changed over to SP2010 and the “flat and expanded” view doesn’t seem to work when originally set to flat. If set to expanded, it loads properly. But, if set as collapsed, when expanded it only shows the text. Just wanted to let you know since the original code works very well. Thanks.

    • Christophe, this is good to know that Sean is having the exact same problem I have. Since I don’t use this code, but other code for showing stoplights with a calculated field, the problem has to be with SP2010. Other than keeping the set expanded, I have not found a solution.

  193. Hi i am using WSS3.0 i created a calculated field i passed the formula like u it is showing the then i added u r script with CEWP under the List is is not working can u explai y?

  194. Christpher, I apologize if you covered this elsewhere, within the script, what pattern are you looking for with RegExp(“\\s*\\s*”)… near as I can tell you are looking for the pattern “__” where the underscores represent whitespace and “letter” means a-z,A-Z… the ? is the “(.|\\s)*”, which I still don’t get… you are looking for a specific HTML tag?

  195. Works great aside from collapsed grouping on SP 2010.
    Have seen posts about setting a time out and re-triggering the routine but have not got that to work either.

  196. Hi Christophe –

    I can’t seem to get this to work. When I view the source of my page, I *think* the reason is that SP is automatically converting all ‘special’ characters in the calculated column. For example, my <DIV appears as <DIV in the source…so when the script runs, I think that it is never seeing <DIV –

    Am I a missing a step so that SP won't convert this upfront from the calculated column?

    Thanks much,
    Darren

      • Darren, you’re correct, SharePoint “escapes” the code. This is why you need the script that unescapes it. Follow my more recent tutorials – for example the color coded calendar – for more step by step explanations.

  197. Hi Cristophe,

    Any idea how to solve the group by issue in SP 2010? I have read the other posts but can’t seem to get it work, I am using the update code as well but still nothing. How can I get around this?

    Thanks for all the helpful posts!
    Tudor

    • There is no easy way, and I am reluctant to publish the more complicated ones that fix grouping and filtering because I don’t want to push this “hack” too far. Let me see if I can include this in the new techniques I am about to publish.

      • I’m not sure if this helps but we were having the same problem. I’ve discovered that if I change the calculated column to be defined as a Number instead of Text, the HTML is displayed without the use of the texttohtml script on a grouped list. Still, texttohtml is needed in the forms (display, edit and new).

  198. I have a lot of lists that need to use this javascript code, so i was wondering, is it possible to put this javascript code somwhere other than in a new web par… for example on masterpage or some page layout, so that it is not needed to manualy add new web part (with java script code) next to the every existing list?

    Any hints would be priceless.

    Thank You & Best Regards!
    Mario

    • I’m not an expert but this is what I am doing…I created a library for JS files and just crated a .txt file there with this code. When you add a Content Editor to a page instead of copying the code into the web part you could just add the link (Edit Web Part -> Enter link to JS file in Content Link field). That works for me.

      Another way you could do this is to copy the code into one Content Editor and then export that web part, then you could import that exported web part wherever you need it. Note: I have not used this technique for this but have used it when I modified a web part and wanted to keep using it. Please correct me if I am wrong 🙂

      Hope one of those options work for you, there are probably others!
      Tudor

      • Thanks for jumping in Tudor! Having the code in a central location is definitely a good practice. Saving it within the Web Part is not so good, because if you need to update it you’ll have to open all the instances.
        The code could also be placed in a Master page, in this case it’ll run on all pages, whether they need it or not.

        One final note: before you use the code on a large scale, I recommend that you read my recent article about security concern. I’ll soon publish other techniques to achieve a similar result.

  200. Pingback: Security concerns with the HTML Calculated Column « Path to SharePoint

  201. HEy there

    Ich bin zurzeit dabei, ein Ampelsystem in unsere SHP Site einzubauen. Momentan teste ich noch, es aber will einfach nicht klappen und das ärgert mich, da ich den Fehler nicht erkennen kann.

    Zum Problem.

    In einem einfachen Wertefeld (1000 bis 2000) möchte ich eine Ampelfunktion einbauen: grün, falls <1300, gelb, falls =1300 und rot else (also grösser 1300).

    Das kalkulierte Feld hat folgende Formel:

    ="Indikator#"&IF(Preis<1300,"ampelgreen",IF(Preis=1300,"ampelyellow","ampelred"))

    so weit so gut.

    Nun füge ich ein Java Script in einen Content Web Part ein , wähle hierfür "edit HTML Source" und gib den folgenden Code ein:

    var i=0;

    var allCells = document.getElementsByTagName(“td”);

    for(i=0;i<allCells.length; i++)

    {

    if(allCells[i].innerText.indexOf(“Indikator#”) == 0)

    {

    id = allCells[i].innerText.split(“#”)[1];

    if (id == “ampelyellow”)

    {

    allCells[i].innerHTML = “”;

    }

    if (id == “ampelgreen”)

    {

    allCells[i].innerHTML = “”;

    }

    if (id == “ampelred”)

    {

    allCells[i].innerHTML = “”;

    }

    }

    }

    so weit so gut.

    wenn ich nun aber auf den OK Knopf drücke, wird einfach nichts übernommen, im Gegenteil, ich erhalte noch die folgende Meldung:

    “Warning: the HTML Source you entered might have been modified”

    Danke sehr für eure rasche Antwort

  202. Pingback: Formatierte Berechungen - Take a photo

  204. Looks Grat, but with a Presentation Web Part it appears to surpress the Icon and Words to allow you to “copy Slides to a Presentation”, although it shows the other default selection items for this list type (“New”, “Upload”, “Actions”).. any ideas ?

  205. Woah! I’m really loving the template/theme of this site. It’s simple, yet effective. A lot of times it’s difficult to get that “perfect balance” between superb usability and appearance. I must say you have done a superb job with this. In addition, the blog loads super fast for me on Safari. Excellent Blog!

  206. I was wondering if you ever thought of changing the layout of your site?
    Its very well written; I love what youve got to
    say. But maybe you could a little more in the way of content so people could connect with it better.

    Youve got an awful lot of text for only having one or
    2 pictures. Maybe you could space it out better?

  207. Excellent beat ! I wish to apprentice whilst you amend your web site,
    how can i subscribe for a weblog site? The account helped me a acceptable deal.

    I were a little bit familiar of this your broadcast offered
    vibrant transparent concept

  208. I have this works well in both collapsed group and uncollapsed group in SharePoint 2007 and once it is migrated to 2010 it stopped working in collapsed group for SP 2010. Does anybody have same issue please reply me soon.

    thanks
    Tesfaye

  212. Pingback: Calculated Columns (Functions and Use of HTML) | SharePoint of no Return

  213. This was a lifesaver, thank you for taking the time to publish.

    Question though… has anyone tried to get this to work with javascript code in the list items, not just HTML?

    I’m trying to get it to not only change the color of the font in the list item, but also to change the color of some text in another location on the page. I have the script that works, but it doesn’t work from within the list item. Hoping someone else has a trick that would work.

    I converted your script to search for and , instead of “” and “/DIV>”

    Then I have this in my list item:

    var x = document.getElementsByTagName("SPAN") // find all of the TDs (or spans in this case)
    var i=0;
    for (i=0;i<x.length;i++)
    {

    if (x[i].className=="menu-item-text") //find the TDs styled for lists
    {

    if (x[i].innerHTML=="Text I'm Highlighting HERE") //find the data to use to determine the color
    {
    x[i].parentNode.style.backgroundColor='RED'; // set the color
    }

    }

    }

    • Patrick, you’ll find tons of articles about this technique on my blog, some involving scripts. Just using a script tag instead of div or span won’t work, but there are other ways. Be warned that there are security risks when you go that route, I have already mentioned that in a recent article and I’ll write another one very soon.

  214. I’ve been browsing online more than 4 hours today, yet I never found any interesting article like yours. It’s pretty worth enough for me.
    In my view, if all website owners and bloggers made good content as you did,
    the web will be much more useful than ever before.

  215. I have been exploring for a bit for any high quality articles or blog posts on this
    sort of space . Exploring in Yahoo I at last stumbled upon this
    website. Studying this information So i’m glad to express that I have a very excellent uncanny feeling I discovered exactly what I needed. I most indisputably will make sure to do not fail to remember this site and provides it a glance on a relentless basis.

  216. After I apply the code to my page, the button from top bar – Page, Item, List etc. are removed and i’m left with Browse only. Have you come across this issue anytime?

    • Which code are you using? This post is pretty old, and there are new versions available – especially if you are working with SharePoint 2010 or 2013.

      • I’ve seen this issue. While the List and Item Ribbons appear to disappear, they’re still there. Just select an item within the list and you should see the Ribbons come back (at least in 2013). Christophe–you mention that there are new versions available, is there code to handle the collapsed grouping issue in 2010/2013?

  217. I am a fumbling SP user with an idea. I would like to present users with a single list of all the documents contained in multiple document libraries within a single site. This list to contain (amongst other things) a hyperlink, showing the document title, linked back to the actual document. Your calculated HMTL method seems to be an ideal candidate for achieving this. However, while I can create a calulated field, when I paste your sample code (=CONCATENATE(“”,Title,””)) into it, it returns the error “The formula contains a syntax error or is not supported” I’m using SP Foundation 2010.

    I have also noted that every calculation which includes “CONCATENATE” fails in the same way. Is this one of those things that worked in SP2003/7 but not in SP2010?

  218. Does your Text to HTML code work in the CEWP in SP2010 as well? We had this working great in our 2007 environment but I can’t get it to work in SP2010. Any suggestions?

  220. Pingback: HTML Calculated Column + Client-Side Rendering | Path to SharePoint

  222. I will immediately snatch your rss feed as I can’t
    in finding your email subscription hyperlink or newsletter
    service. Do you have any? Please let me understand so that I could subscribe.

    Thanks.

  223. Hi,

    Thanks for you post it is really helpful. However I have come across and issue. I can’t have the link open in a new browser window. I believe this is because the CONCATENATE function uses quotes and so does target=”_blank”.

    Do you know of a workaround for this? I may just be being stupid and missing something!

    Thanks

    Dom

    • Hi Dom,

      you have two ways to address this: either use simple quotes around _blank (this is accepted in html) or use double double quotes: “”_blank””

      • Hi Christophe,

        I have tried both, double quotes brings back an error from SharePoint, with single quotes you get no error but the browser doesn’t open in a new tab.

        I have however now found out why. I wasn’t adding spaces between the link and the Target.

        Thanks for you help anyway.

        Dom

  224. Hi Christophe,

    Thanks for the Post. In the Preview Pane I could see my HTML codes are rendered correctly but while clicking on the Title of an list item, the HTML scripts are not rendered.

    The Calculated column is displayed as “In Progress”

    Kindly Help!!

    –RB

  225. Hi,

    Any chance anyone is still answering questions for this awesome sharepoint idea? I’m pretty much a noob at coding and I followed the steps (changed for my SP), but it doesn’t show the html part. It just shows text. I put the CEWP at the bottom and added this to the source editor:

    <script 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("<DIV") == 0) && (TDContent.indexOf("</DIV>") >= 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="<TABLE><TBODY id=\"tbod"+ groupName+"_\" isLoaded=\""+isLoaded+ "\">"+htmlToRender+"</TBODY></TABLE>";
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("<DIV") == 0) && (TDContent.indexOf("</DIV>") >= 0)) {
theTBODYTDs[j].innerHTML = TDContent;
}
}
catch(err){}
j=j+1;
}
tbody.parentNode.replaceChild(wrapDiv.firstChild.firstChild,tbody);
}
</script>

    This is my formula in the calculated field: =CONCATENATE(“”,[PHY/HOS],,””)

    Note: “Email” and “PHY/HOS” are column names.
    It just shows as text. For example my first line shows this:
    Physician
    I checked the troubleshooting page too and couldn’t figure it out. Any thoughts?

    Thanks!

  226. First of all. Great!
    Second, and not sure if it was mentioned already, but it took me a while to figure out that needs to be all caps. didn’t work for me when i left it lower case.

  227. Hi,
    In the Script below if percentage is selected as 100, instead of the first IF condition (Perc>=75), the second IF condition is getting executed i.e the background color for the condition =”75%”,””&Percentage&””,IF(Percentage<="25%","”&Percentage&””,IF(AND(Percentage>”25%”,Percentage<="74%"),"”&Percentage&””)))

    –Script End

    Kindly help!!

  228. Pingback: Formatierte Berechungen | Bernhard Tinner

  229. Hi guys
    Does anyone know if this colour coding works in SharePoint 2013 or SharePoint Online?. I can create the calculated columns and I see my colour coding applied as text in a COLOUR field but it wont apply the colour to text in a column called STATUS. Like the script isn’t firing!

    In 2013, do we use a “script editor” web part below the custom list to render the colours?. The script just doesn’t seem to work for me.

    Any help greatly appreciated.
    Matt

    • Matt, the issue is that the script is actually firing too early, as in SharePoint 2013 the content is rendered asynchronously. You’ll need to look into other options, see for example posts I published in 2010 and 2013.

  230. please help. it is working fine with the list. but when I make a grouping in the columns it is not working. i am working at SharePoint 2010. do you have a solution for this problem? Thank you.

  231. Pingback: Apply color coding (KPI Status Indicators) to your SharePoint lists | SharePointKeith

  232. SharePoint 2010 calculated column and hyperlink (no workflows or scripts needed)

    http://devdotnotes.wordpress.com/2012/01/29/sharepoint-caculated-column-and-hyperlink-no-workflow-or-script-needed/

    The point of the article referenced above is if you change the caluclated field type to anything but “single string of text” it renders the html correctly “if you’ve formatted it correctly” which may be a bit challenging on by itself but give it a try, it worked great for me.

    BTW thanks for the interesting articles..

Comments are closed.