Using calculated columns to write HTML
Update [6/8/2009]: A new version of the script is now available, more details here.
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.
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:
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!
- 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