Wednesday, October 5, 2011

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.


7 comments:

  1. I'm trying to replicate the text in this image http://1.bp.blogspot.com/-mkESDelPHO4/TozUmuSQTpI/AAAAAAAADi4/N8WtTtQ3Ga0/s1600/3.PNG

    This is what I am tying and keeps coming back with syntax errors.

    http://goo.gl/sa87k (Blogger wouldn't let me post the code here.

    NB. Working on SP 2007 - it automatically puts the brackets in the field names.

    Any assistance would be greatly appreciated.

    ReplyDelete
  2. Solved my own problem - egs of syntax written here http://blog.pathtosharepoint.com/2008/09/01/how-to-open-hyperlinks-in-a-new-window-or-not/

    ReplyDelete
  3. Actually it is possible to create automatic Dynamic links (although it is a bit of a kludge work around).

    Simply set the data type of the Calcultaed Column from Text to Number or Date/time and it will display the link as a hyperlink.

    Works for me in SP2010.

    However the link is always right aligned (because it is a number) and will not display in a CQWP. Because of this I always create the text in the hyperlink as something generic like "View details" so that it is the same on every line.

    Hope this helps someone.

    Simon.

    ReplyDelete
    Replies
    1. Hi Simon, this really worked for me. But how I don't understand that.

      Delete
  4. I tried this approach with a javascript popup function to ensure it pops up in a new window and found that if someone filters any column manually on the list the hyperlink goes back to the unformatted HTML. Any ideas?

    ReplyDelete
  5. The images in the body of this page are not displaying for me to see the examples you provided.

    Could you please provide a text version of your script?

    Thank You

    ReplyDelete
  6. Hi, the images are not been displayed, can you please submit them again, thanks!

    ReplyDelete