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.
If you do not fill in a description, the URL is displayed when you view the item.
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?
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.
*** Update 2017-07-21: This worked on SharePoint 2013 and SharePoint Online until sometime this spring. When using SharePoint Online, it now only works in classic mode – it has been disabled in the new mode.
To replicate, follow these steps. The column names are not important, they are just suggestions.
- Upload an image to SharePoint, i.e. in your site assets library
- Create a new (site) column of the type “single line of text”, and call it “Site URL“
- Create a new (site) column of the type “calculated column” and call it “Site Link” or similar
- Use the formula as stated below, adjusted for your site and column names –
- Change the output of the column from “single line of text” to “number”
- Save the column and enjoy
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.
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.