Dynamic Hyperlinks in Calculated Columns

I recently was faced with an interesting problem. I needed to create a list column that referenced a field in the list and then dynamically constructed a URL to a filtered view of a document library. Specifically, the users needed to be able to enter the title of a company in a list item, and then have clickable links created that would take them to a separate document library, and only show them documents that belonged to that company.

The solution I implemented was developed by Christophe at Path To SharePoint. His solution was a lifesaver. I give full credit to him. I did find that I had trouble following some of his instructions, so I'm going to post a simplified version of how I implemented it here, along with a few pointers that I uncovered along the way.

The solution is two fold:

1. Create the list that will hold the name of the company and the hyperlink field
2. Embed a Content Editor web part in the page that contains a JavaScript that will render the hyperlinks in a friendly manner.

Creating the list:

For illustration purposes, we'll have a list that holds the titles of websites and builds links to their websites dynamically. 

Create a list with a field called "Title". This will hold the name of the website. Next create a field called "Hyperlink". This field will be a calculated field that will dynamically build the hyperlink to the website of that name. 

Enter the following formula in the definition of the Hyperlink field: 

This will  dynamically build a hyperlink of http://www.[Title].com. It will also open the link in a new window. However there's a problem. I created a list item and entered "ESPN" as the website title. Here's what it looks like:

As you can see, the hyperlink isn't exactly user friendly, and it also isn't a clickable link. There is no way I know of to make a dynamically built hyperlink in a calculated field that is user friendly and clickable. Christophe came to the rescue by pointing out that you could embed a content editor web part at the bottom of a page that holds a script that will render the hyperlink correctly.

So, create a web part page, place a list view at the top of the page and place a CEWP at the bottom of the page. You can grab his script at using calculated columns to write html. Make sure that the CEWP is at the bottom of the page. Also, make sure you set the Chrome to 'None' so that the CEWP is completely invisible to the users.

Now when you view your page, it will look like this:

You can see the link now renders correctly, and it is also clickable. Since the hyperlink is a calculated field, you can edit the list item and change the title from ESPN to MSDN. The hyperlink will automatically update.

This is a powerful tool. I was able to use it to link to a filtered view of other document libraries so that only documents belonging to the item in question were displayed.

Thanks to Christophe for the great work.