The purpose of this post is to help readers who are having trouble implementing my “HTML calculated column” method. Below are some of the most common issues.
SharePoint returns an error message when you create your calculated column
- Do not directly copy/paste the formulas from my blog. Use “copy to clipboard” when available, otherwise type it directly in the formula field of your calculated column.
- Start by implementing one of my examples as is. Choose for example the indicator, which works on a standard issues list.
- You can use the “Edit in Datasheet” option to edit your formulas. In datasheet mode, SharePoint provides some context help.
The formula works, but it is rendered as is, not as HTML
- Remember that the formula itself does nothing, you also need the script, provided through a CEWP placed in your page.
- Do not put the script in the text editor of the CEWP. You can either put it in the source editor, or (recommended) put it in a separate text file and link to it from the CEWP.
- The CEWP must be placed AFTER the list on the page.
- The CEWP is needed on every page where you need to render the HTML.
You don’t know the syntax for SharePoint formulas
You can find some useful references in this post.
The HTML is not rendered correctly
You may have a syntax error in your HTML, double check your opening and closing quotes, as well as semi-colons in styles.
For Firefox, do not forget the non-breaking space when needed (see my posts for examples).
Getting help
If you cannot get the method to work, you have two ways to ask for advice:
- post your question in the comments section. To post code, use the following syntax:
[sourcecode language='html']
Then write your code and close with [/sourcecode]
- or send me an e-mail at Christophe@PathToSharePoint.com
Hope this helps!
70 comments
Comments feed for this article
November 17, 2008 at 10:05 pm
Using calculated columns to write HTML « Path to SharePoint
[...] September 1, 2008 in Calculated Columns, Content Editor Web Part, SharePoint 2007, The HTML calculated column, Tips and tricks, html/scripts Update [Nov. 2008] 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 – preview panes: scheduled for December 2008 – filters: scheduled for December 2008 Also note the troubleshooting section. [...]
November 19, 2008 at 11:53 pm
SharePoint calendars: color coding, hover effects, etc. « Path to SharePoint
[...] find the following posts particularly useful: – some simple but useful examples to get started – a troubleshooting section – how to apply the method to the DispForm.aspx page (as this is the page your calendar items will [...]
December 1, 2008 at 10:55 pm
TFForums
Is there a length limit with this? the error message when fails doesn’t really tell much useful information – neither do the logs.
December 2, 2008 at 2:06 am
Christophe
Did you try the “Edit in datasheet” method, as mentioned in this post? It may help you if your formula is not correct.
I did a quick size test, and the maximum length I was allowed to enter in a formula was 854 characters…
February 19, 2009 at 7:21 pm
Michael
I’ve tried everything but always get a syntax error when I try to save the calculated column.
What am I missing? Does this require MOSS?
February 20, 2009 at 12:17 am
Christophe
This works for any SharePoint (wss 2, wss 3, MOSS).
Syntax error simply means that the formula in your calculated column is not correct. Check your quotes, parentheses, column names.
March 17, 2009 at 7:15 pm
Aaron
Is there a way to use HTML and CSS in the Datasheet view of a list as well. Your scripts for Calendar and normal Lists have worked great. Can’t thank you enough. I’m just trying to get the column contents to center correctly.
March 17, 2009 at 8:15 pm
larry
Aaron,
You can apply styles for your columns if you want to use the SP UI, else you can edit the datasheet view and make many more changes.
March 19, 2009 at 1:47 pm
Judith
Hi,
I posted under a Color Coding -More examples, not knowing the trick to post code. Please delete those postings(2)
I’m trying to use our images in the calculated field with an IF statement. It works great when using • not a problem but this formula just dispays as text. Can you help me out?
the column displays as text even though I have the CEWP with the text to html javascript loaded at the bottom of the list page.
Here is what is displayed:
The logic works but the images do not render.
I’ve tried different combinations and placement of quotes but can’t seem to hit on the correct syntax.
Can anyone help?
Thanks,
Judith
March 19, 2009 at 2:19 pm
Christophe
Judith: here are some possible reasons why it doesn’t work:
- you’re using capital letters in the script – DIV – but small letters in the formula – div
- a / is missing to close your img tag
Hope this helps.
March 19, 2009 at 6:38 pm
Judith
Thanks Christopher…it was the lower case vs upper case in the tags. When I changed the and in the calculated field to uppercase it worked.
The javascript in the CEWP was searching for the “” and could not find it because it was lower case.
Good catch. Its always better to have another pair of eyes!
Judith
(didn’t need to close the img tag)
March 20, 2009 at 10:34 am
Christophe
Great
. I would still close the IMG tag, the result may depend on the browser.
March 20, 2009 at 10:36 am
Christophe
@ Aaron: the datasheet view is a specific – IE only – control, and I don’t think you can change the rendering.
May 13, 2009 at 1:40 am
Poonam
I am trying to create a color coded list in Sharepoint for Issues, and risks tracking.
I want the value in the column ‘Traffic Light’ to be displayed as a bullet point ( red, green or gold depending upon the choice selected in the column ‘Priority”).
Following your example on the website I did the following steps:
1. Created a column named ‘Priority’ with choice option : 1.High 2. Medium 3) Low.
2. Created a calculated column named ‘Traffic Light’ and entered the formula =CHOOSE(RIGHT(LEFT(Priority,2),1),”red”,”gold”,”limegreen”)
3. Added the content editor web part
4. Copied the code( clipboard)
5. Entered the code in the source code
Can you please suggest what is it I am missing as it still does not display the colour coded bullets, but just the text ‘red’, ‘limegreen’ and ‘gold’
May 13, 2009 at 5:17 am
Anonymous
I am also getting this same result.
May 13, 2009 at 3:07 pm
Christophe
Poonam, getting the color name is only the first step, you then need to write the HTML (DIV tag). See my examples on the blog.
May 13, 2009 at 8:40 pm
Anonymous
Didn’t he say above that he did add the CEWP and added the source code?
June 1, 2009 at 1:51 pm
Dave
Ok still trying to get this towork:
1. Copied the code for calendars to a CEWP
2. Added two columns to my calendar
One for a type of event ( only three right now)
another calculated column with the input from the formula generator
3. added the CEWP to webpage.
No luck, the formula generator shows as text in my caluculated colomn. What am I doing wrong?
June 1, 2009 at 11:55 pm
Christophe
Dave: for calendars, I recently changed the code. In the formula generator you need to replace the two “DIV” with “SPAN”.
Sorry, I still need to find time to update my site…
June 2, 2009 at 3:36 pm
Dave
Ok let me make sure I got this right. I updated the new CEWP web part that has the SPAN tag in it. I then changed the formula from DIV to SPAN. Put both on the page with the calendar. Still not working.
Maybe I am doing the calendar wrong. I added two columns to the calendar. One was the Type of event as a choice colomn and there are three elements. I went to the Formula genterator and typed in the name of the column and the three choices and selected the color for each choice. is that right? then replaced the with and the closing tag as well
Sorry I know u must be busy!
June 4, 2009 at 3:37 am
Christophe
Dave, how about sending me a screenshot of the formula generator by e-mail? Christophe@PathToSharePoint.com
June 16, 2009 at 4:13 am
Color coding: more examples « Path to SharePoint
[...] the script, simply follow the instructions for list views or calendar views. There’s also a troubleshooting page to help you [...]
July 9, 2009 at 11:42 am
HTML Calculated Column – Updated script « Path to SharePoint
[...] If you haven’t heard about the HTML Calculated Column yet, check out these links: – the method, explained (includes version 1.0 of the script) – an example: apply color coding to lists – a formula generator for color coding – a troubleshooting page [...]
July 10, 2009 at 10:01 pm
HTML Calculated Column – Updated script v2.1 « Path to SharePoint
[...] If you haven’t heard about the HTML Calculated Column yet, check out these links: – the method, explained (includes version 1.0 of the script) – an example: apply color coding to lists – a formula generator for color coding – a troubleshooting page [...]
July 24, 2009 at 4:41 pm
Kathy
I am trying to setup the same type of Calendar as Dave. I have a column with the type of events called calendar and two types of events. i also have a calutater column.
I used the formula generator and here is the formula
=” “&[Subject]&”"
I have added the HTML Calculated column wep part to the page just under the calendar but still can’t get the color to render.
I guess I am missing something but I don’t know what.
July 30, 2009 at 5:01 pm
Derek
Having the same problem as Kathy.
July 30, 2009 at 7:49 pm
Dave
You have to change the view of the calendar. Under the Calendar Columns, select your column in the Month View Title
August 4, 2009 at 4:18 pm
Roy
Apologies if this is caused by something else Christophe but at my wits end with this bizzare problem. I have an issues list in which I have included a html calculated column which displays the Issue ID with a colour coded background according to Issue Status.
The code works fine until I edit an item in the list. When I return to the list from the edit screen the html still renders fine but does not show the ID after a refresh or even in a different browser window and/or different SP Page with the list on display. It has just gone for that entry alone, all unedited items display fine (which suggests its not the calculated column itself, but…)
The only way I can get it to appear again is if I go into settings and open the calculated field and (without change) click okay. Bizzare!
August 10, 2009 at 8:47 am
Roy
To demonstrate I have just updated ID 23. This is teh html generated by the calculated column for ID’s 22-24. For some reason the ID is not generated, yet is for all unedited entries despite no conditional logic within the formula.
It may be that this is not a problem with your script but a limitation with SharePoint formulas somehow that impacts on using your script. Of course it could be me being very stupid!
22
22
23
24
24
August 15, 2009 at 8:15 am
Christophe
Roy, this is a known SharePoint limitation, and it is mentioned in my post about the “HTML calculated column”. The ID field should not be used in formulas in calculated columns.
August 15, 2009 at 10:35 am
Roy
Thanks Christoph, and sorry for my oversight. I now use an alternative field that gets set to the ID when the entry is created and that works fine.
September 2, 2009 at 4:56 pm
Alison Ash
Hi Christophe,
I’m trying to display infopath xml files (in a forms library) in a Sharepoint calendar view. Only problem is that the link opens the properties and not the xml file.
I wanted to change the link to point directly to the xml file… I’ve tried using a calculated column to generate the link, but the text is not interpreted as HTML.
The same calculated column in a “normal” view and your code works just fine…
I would be very grateful if you had any ideas…
Thanks very much
October 11, 2009 at 12:44 pm
Christophe
Sorry for the laste reply Alison. It’s hard to tell what is going wrong, but here is a clue: lists items are cells (td tags), so you can use any html element inside (div, span, etc.). Calendar items are hyperlinks (a tags), so you should only use inline elements (no div for example).
For an example, see the difference between my post on lists (uses divs)and my post on calendars (uses spans).
September 11, 2009 at 3:39 pm
Bonnie
Christophe, I was so excited about applying this code to my content editor web part. We are operating in Sharepoint 2003, and although I have read other posts and they claim it works, I cannot the calendar to display. I’ve left all the defaults in the web part as is, but have had no success. I’ve used your abbreviated version for 2003:
/* Tiny Calendar – SharePoint 2003 */
/* Christophe@PathToSharePoint.com */
.ms-calMid {height:0px;}
.ms-CalSpacer {height:0px;}
.ms-calhead {padding:0px;spacing:0px;}
Do you have any other magic to work this brilliant idea of yours?
November 3, 2009 at 1:15 am
Jim M
I’ve been delightedly using the script to color lists for months. However, after a recent SP update by our administrators, the colors disappeared
I put the page in edit mode to see if the script got removed from the CEWP and the colors reappeared! When I exited edit mode, the colors remained — until I switch views, navigate away & come back, add/edit items, etc.
Any insight into what’s happening? Does going in and out of edit mode render the page differently somehow (cache)?
Thanks for any leads!
November 3, 2009 at 3:17 pm
Christophe
That’s weird… Are you switching from shared to personal views when you enter/exit edit mode?
November 5, 2009 at 12:28 am
Jim M
No, but I tried to after reading your question and I couldn’t discern any differences.
To add to the wierdness, Firefox and IE demonstrate the symptoms mentioned above, but Chrome works as expected!
November 5, 2009 at 9:26 pm
Simone
Hey Christopher… loved the whole thing
absolutelly brilliant! only small issue I am having is with recurrent events in the same month… it is creating duplicated DIV IDs… any idea on how I can make them unique??
Thinking of changing the javascript but would like to use something in the calculated formula!
Tried using the Start Time and ID fields and no luck… it captures the first instance!
Have a great day!
Simone
November 6, 2009 at 12:52 pm
Javier
Hi Christopher,
I’m trying to perform a simple test of the HTML calculated columns by creating a simple list with three columns: Title, Priority and Traffic light (calculated value) exactly the same as you explained in one of your exaples but i can only see the formula displayed as text . I’ve added the CEWP with the last published script but no luck. I’ve copy the text into an empty HTML file and it is working ok so the code generated by the calculated column is ok.
Could it be that there is some kind of restriction made by the SP administrators?
Thanks in advance for your help!
Kind regards,
November 6, 2009 at 2:11 pm
Javier
Already fixed it! I forgot to put the webpart below my list.
Thanks for your help!
November 19, 2009 at 6:41 am
Fayadh
Hi Christophe,
Can u please take a look at my IF funtion is not working.
=”IF(“&[Availability Status]&”=”&”Available”&”, “&”Yes”&”)”
This will return just string,
IF(Available=Available, Yes)
Do you think this problem occurs from my Sharepoint Services 3.0 itself?
I dont know why but it seems all the functions aren’t working.
Regards,
Fayadh
November 19, 2009 at 6:58 am
Christophe
Don’t put the IF function between quotes:
=IF(…
November 19, 2009 at 7:25 am
Fayadh
Fayadh
Thanks Christophe for the quick response,
I am afraid thats the main problem,
this Sharepoint site could not read functions.
if i put
=IF(“&[Availability Status]&”=”&”Available”&”, “&”Yes”&”)
it will return an error.
“The formula contains a syntax error or is not supported.”
do you have other solution? is it possible for me to use javascript in the listviewwebpart and grab the column [Availability Status] and use javascript on it?
November 24, 2009 at 6:13 am
Fayadh
After a long time finally I have found out the solution.
The problem is because my sharepoint site is not using default language pack.
Default language pack
=IF([Availability Status]=”Available”, “Yes”, “No”)
I replaced the ‘ , ‘ with ‘ ; ‘
=IF([Availability Status]=”Available”; “Yes”; “No”)
It works! Finally!
Thanks for your time Christophe
November 26, 2009 at 2:35 am
Christophe
Thanks for the update Fayadh. I should include regional settings in the checklist…
November 19, 2009 at 7:18 am
Fayadh
Thanks Christophe for the quick response,
I am afraid thats the main problem,
this Sharepoint site could read functions.
if i put
=IF(“&[Availability Status]&”=”&”Available”&”, “&”Yes”&”)
it will return an error.
“The formula contains a syntax error or is not supported.”
do you have other solution? is it possible for me to use javascript in the listviewwebpart and grab the column [Availability Status] and use javascript on it?
November 25, 2009 at 1:23 pm
Robert
I have followed the instruction to the letter and get no color in my calendar. I placed the webpart under the calendar and the DIV tag to SPAN and still no color is presented in the calendar. What have I missed.
November 26, 2009 at 2:43 am
Christophe
Robert, what do you see in your calendar? The raw HTML strings?
January 28, 2010 at 12:01 pm
Effie
Nice!
I’ve try this technique on a list that have a link type field, and I’ve put the image text on the “Description” field below it, assuming that the image will be clickable, but it not…
February 5, 2010 at 3:24 pm
Helen
Hi Christophe –
I have applied this and it works almost perfectly. However, there are 2 entries that show the HTML instead of the color coded item. I initially figured that they were corrupt, so recreated them, copying them exacly, and now the recreated ones appear on the calendar that way. I’m beside myself trying to figure out why just these 2 (now 3) entries will not work but everything else will. I tried changing the Title and the dates and any other fields that I thought might affect the outcome, but it still doesn’t work. Any ideas?
Thanks!
February 7, 2010 at 11:37 pm
Christophe
Helen, feel free to send me a screenshot and I’ll take a look.
February 10, 2010 at 6:10 am
Jeff Martin
Chris,
First off want to THANK YOU for your site, really good stuff. I have used your HTML Calculated Column Script without any problems on several other pages/sites I have been working on. However, (don’t you just love when that work comes up) I have been trying to get this to work on an Article Style Page Layout but having a couple issues. 1) I have put a CEWP on the Page from Designer and tried to insert your script but the CEWP does not allow me the option for RichText or SourceCode to put in the script on the CEWP from the Task Pane. So, I applied it to the CEWP from the Code View of Designer but it does not work. 2) I put the script in a CEWP from the browser and then exported the working CEWP to my desktop and then imported it to the Page Layout through Designer but it still does not work. 3) If I open the “Article Page” in the browser and place the CEWP on the page and put the script in the CEWP, Apply, and Publish the Page it works just fine. However, I can’t have users applying a CEWP and placing the script into every Article Page they create. Any suggestions or tips on how to get this to work in an Article Page Layout? Thanks in advance for any direction you may give.
February 16, 2010 at 7:57 pm
Teri Centner
I am having a problem similar to Helen’s, I think. (She posted 5 Feb.) I am using the SPAN code to put background colors on a calendar. (Three VIPs are represented in three different colors.) The script is in a hidden CEWP at the bottom of the page.
The only calendar items that render in color, however, are those that are “all day” events, or those that span mulitple days. The events that are constrained to a time show up with HTML code instead of a background color.
Any ideas how to fix this?
teri
February 17, 2010 at 11:16 pm
Teri Centner
I found somebody at work with calculated column experience, and he was able to solve my problem. It turns out I hadn’t tweaked my script properly. He made the right changes, so now all the color-coded calendar items are rendered *and* are clickable.
March 9, 2010 at 10:26 pm
Lizzie
=”"
I am using the above code to try to make the background color of the row based of the priority. It is just making the calculated column background color the right color but not the rest of the row! Please help!!!
March 9, 2010 at 10:27 pm
Lizzie
Sorry here is my code
April 12, 2010 at 4:51 pm
Oberhoser
I get an error when trying to create a calculated column refering to a workflow status column in my document library. Is there a way to include a workflow status column in a formula?
November 9, 2010 at 5:43 pm
Josh
I THINK I have done everything right, but when everything renders on the screen, I don’t see the HTML code, but I also don’t see my images.
When I look at the source behind the webpage, I notice that the HTML tags for my images looks like:
<DIV><img title='In Progress' border='0′ alt='Red' src='http://mysharepoint/sites/SPS_Smart_Dev/Status%20Images/_t/red_PNG.jpg ‘></DIV>
Any ideas?
November 9, 2010 at 5:44 pm
Josh
Hmm.. that looks good to me.
November 9, 2010 at 5:45 pm
Josh
Sorry.. the above is what I get in the source where everything else looks normal.
December 6, 2010 at 9:14 pm
Sam
Where I can get the script that goes in the CEWP? Thanks…
January 12, 2011 at 6:36 pm
Colin
Hi Christophe,
I have the calculated column (Colour) up and running and it generates the right colours, but by name only. When I add the following to the Content Editor
=”"&[Status ID]&”"
all I see in the Content Editor Window is this
=”
“&[Status ID]&”
”
and there is no colour change on Status ID. Any ideas?
Regards,
Colin
February 17, 2011 at 10:06 pm
stefanie
So I am attempting to use this web part to display a link in the description of a field… but it’s not working.
The view source looks like this:
March 28, 2011 at 4:57 am
Christophe
Stefanie, try this:
Also, I’d recommend that you read more recent posts for code updates.
June 7, 2011 at 3:31 pm
Jen
Christophe, you rock!!! Thank you so much for posting these awesome tricks!
I also got the CEWP script to work with a content type using an enhanced rich text column in a document library, which by default does not work. With your script placed after the doc library, the html code displayed like a charm!!!
Looks like your hard work has helped lots of people here. I myself have saved so much time learning tricks from brilliant and willing people like you, so thanks again!!!
Jen
June 16, 2011 at 12:33 pm
Christophe
Thanks for your comment Jen!
July 12, 2011 at 4:23 am
Anonymous
hi christophe
i am trying to put the CEWP below the list webpart and added the last published script inthe CEWP. but i am unable to get the color o nthe list ………..
July 12, 2011 at 4:24 am
monika
i am nort able to get color using the script last published
January 11, 2012 at 12:28 am
Anonymous
Hi Christophe;
Version 1.0 works in my SP 2003 env, but when I tried 2.1 I get the following returned in the CEWP when I am in Design mode on the page and the HTML is not rendered, it simply displays the HTML code as text.
- Default Use for formatted text, tables, and images. true Right 2 Normal true true true true true true false Modeless
Default
Cannot import this Web Part. Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c Microsoft.SharePoint.WebPartPages.ContentEditorWebPart – - /* Text to HTML Lite – version 2.1.1 Questions and comments:Christophe@PathToSharePoint.com */ function TextToHTML(NodeSet, HTMLregexp) { var CellContent =”"; var i=0; while (i< NodeSet.length){ try { CellContent = NodeSet[i].innerText || NodeSet[i].textContent; if (HTMLregexp.test(CellContent)) {NodeSet[i].innerHTML = CellContent;} } catch(err){} i=i+1; } } // Calendar views var regexpA = new RegExp("\\s*\\s*”); TextToHTML(document.getElementsByTagName(“a”),regexpA); // List views var regexpTD = new RegExp(“^\\s*\\s*$”); TextToHTML(document.getElementsByTagName(“TD”),regexpTD); ]]>
I copied the code from the download site and pasted it into the source editor. Any ideas? My calculated field HTML is below:
=CONCATENATE(“”,status,”")
January 11, 2012 at 10:04 pm
Christophe
Sorry, the code was released for SP 2007/2010, and I never tested it in SP 2003.
In the download site, make sure you download the txt file and paste the content in your CEWP. I wouldn’t expect the web part file to be compatible with earlier versions.