Adding an image + link via calculated column on a list with HTML (Directory List Solution)

I was working on a document management system, where each client had its own site collection and projects were subsites under that site collection. I needed a way to show a list of available site collections from a main landing page.  This needed to show all site collections, no security stripping and search rollup was not an option at this time. The easiest way then is to create a list that acts as a directory – just add the sites to the list manually.

The problem was the link to the sites. How best to do this?

Option 1: Hyperlink site column

Adding a hyperlink site column is easy. You get a clickable result in your field display.

The tricky part is dealing with the fact that a hyperlink column consists of two fields: the web address and then the description.

clientsite1

If you do not fill in a description, the URL is displayed when you view the item.

clientsite2

This approach has a few issues:

  • Showing a text like “Link” is not very nice looking.
  • When an end-user fills in the item, they may not be consistent with the result. You could add a description to the field, but this is still not foolproof.
  • Even though the link is blue, not everyone will realize that you need to click the text to open the site – many people will still try to click the title and then just open the item.  This leads to frustration.

Option 2: Creative use of calculated column

You can use a calculated column to display the link as an image, i.e. an arrow or something that is very obviously a link – how about something like this?

clientsite6

By choosing to use a calculated column, we have ensured that anywhere the column is used, the arrow will be displayed. This means that it will work in any view, any webpart, etc.

clientsite4

To replicate, follow these steps. The column names are not important, they are just suggestions.

  1. Upload an image to SharePoint, i.e. in your site assets library
  2. Create a new (site) column of the type “single line of text”, and call it “Site URL
  3. Create a new (site) column of the type “calculated column” and call it “Site Link” or similar
  4. Use the formula as stated below, adjusted for your site and column names –
  5. Change the output of the column from “single line of text” to “number”
  6. Save the column and enjoy

clientsite5

Formula

The formula I used is as follows:

=IF(ISBLANK([Site URL]), "URL Missing", ("<a href="&[Site URL]&" target='_blank' alt='Open site in new window'><img src='/SiteAssets/link.png' style='height:20px; width:20px;' /></a>"))

Broken down, it has a few interesting elements:

  • IF(ISBLANK([column name]), “URL Missing” – if the column with the URL is empty, it will show this text. If you leave off this structure, then the calculated column will simply be empty. You can also change the “URL Missing” text to whatever you would like, i.e. “Please fill in a URL”.
  • A hyperlink is created based on the value of the Site URL column
  • The link opens in a new window (this is specific to my example, as I expect a new window to be more useful for these users)
  • Link to the image
  • Set the image height – I found 15-20 pixels fit nicely into the column. You could also include a class from your CSS.

Tip when editing formulae in calculated columns: use Notepad++, as it will help you see when brackets are closed.  Just make your changes in Notepad++, then copy them into the edit column window each time – this will make it far easier to work with.

clientsite3

Display as HTML

One of the coolest things about this solution is that generally, calculated columns cannot be rendered as HTML. Technically speaking, the contents of the column should just be displayed as plain text. Yet, here we are, with an nice arrow image being displayed and the link working beautifully. The trick is changing the returned datatype from single line of text to number. Once you do that, then the contents are rendered as HTML.

Credit for this solution goes to Danny Engelman at ICC – HTML in a Calculated Column / Field of a SharePoint View.

Site columns and content type hubs

In the real world solution, both the Site URL and Site Link columns were site columns, attached to a content type called Client Directory. The columns were also reused for a similar construction in a Project Directory custom list, which showed the subsites under each client site collection. Using a content type hub, these columns were replicated to each site collection and we were able to build a similar Project Directory in exactly the same way: simply fill in the URL, display Site Link column. This made the solution incredibly flexible and reusable.

Sources

HTML in a Calculated Column / Field of a SharePoint View

23 responses

  1. I tried this on SP2010.. Is that the reason it doesn’t work?? I get a ‘syntaxerror or not supported error’.

    • I don’t think this same trick works on the display form , only on the list view. However, the link itself should be clickable on the display for, making the button less necessary. I believe there are JavaScript solutions which could render a link as a button.

  2. The formula within this tutorial is working and displaying the image in column (site link) but when i am using the site link column in my calendar to display the image it is displaying the link of the image rather than the image itself. Help Needed

    • Hi there!

      Do you mean on on an agenda/list display, or on a calendar display with a block for each date? I wouldn’t expect this to work on the calendar display.

  3. Cool ! It worked perfectly on Sharepoint Online. didn’t realize that calculated column will also hold images. I used the same logic to display dynamic images/icons off a list column based on condition.

  4. You are awesome! Changing output format to a number, or rather not formatting as text, is a lifesaver! Thanks a million.

  5. Im referencing a field that contains a space in it and the image/link is cutting the url off at the space. any ideas? i.e. Referenced Column is “/HumanResources/New%20Employee%20Documents/Joe Smith”, Calculated column shows “/HumanResources/New%20Employee%20Documents/Joe”. Where “Joe Smith” is a field that I cannot insert the %20 into.

    • If I remember correctly, calculated columns don’t work well with spaces.

      My best tip is to create that column without a space in the name, then rename it with a space. The actual name will remain the same but the display name will update.

      • I ended up creating an additional calculated field to replace the space with %20. I used =IF(ISNUMBER(FIND(” “,[Ref])),REPLACE([Ref],FIND(” “,[Ref]),1,”%20”),[Ref]) where REF was the original Name field which couldn’t be changed and is also the items Name/Title field.

  6. Disregard; I went into SharePoint Designer and pulled the full address, and it’s working perfectly. (i.e., //sitename.sharepoint.com/sites/[library]/SiteAssets/[filename])

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s