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!
48 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?