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
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.
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.
Hannah's SharePoint 
Reblogged this on Dinesh Ram Kali..
Done! Still very cool that I found this on an old colleague’s blog. :) Nice site!
Great post!
I tried this on SP2010.. Is that the reason it doesn’t work?? I get a ‘syntaxerror or not supported error’.
If you’re having trouble with the calculated column, you might need to play around with it a bit. The regional settings mean that you may need to use slightly different notation.
You’re fast! I use NL-US regional settings so that might be the problem… Thanks very much :)
Phone apps are awesome. ;) Glad to hear it!
Use the text in the image below the post… or this:
=IF(ISBLANK([Site URL]),”URL Missing”,(““))
The URL link (or button) is rendered as expected in list view. However, when open the item in DispForm.aspx, the same column is not rendered as link. It is just a plain text
I am using Sharepoint 2013 on-premise. Did I miss anything? Thank you!
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.
Thanks!!!!
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.
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.
Awesome, glad to hear it!
You are awesome! Changing output format to a number, or rather not formatting as text, is a lifesaver! Thanks a million.
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.
Hi,
I did the above, but the image is generating a link that is basically appending the Website URL entered by the user to the URL of my list. For e.g. if the link entered by user is http://www.xyz.com, the image takes me to the link http://www.sharepoint365/site/subsite/list/www.xyz.com
Thanks in advance!
Not sure what’s happening here – it sounds like something is getting confused between relative and full links. Sorry I can’t help more in this case!
HELLO! doing the same thing but instead of a URL I’m using an email address. somehow it breaks the email address? when i look at the field that contains the email address it is displayed as “emailto:name@somewhere.com….the calculated column is not displaying the “emailto:” portion. any clue where I’m messing up?
Hello! I did this but with an emaill address and it’s not working as it should. I checked the Email column and email addresses are preceded by mailto: which on hover does not show on the created image. Is there a way to fix this?
Thank you so much for your help.
This is great; but I’m using SharePoint Online, and the image link isn’t working. I’ve tried using a direct URL; shifting the slash (directory symbol); and everything else I can think of, but continue to get the “broken image link” picture. Any ideas?
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])
Nice, glad to hear it!
Hello Hannah
This worked really well, thank you! My list uses a unique URL and the same image and you can click on the image…
However I need to be able to choose from 4 images (which I can determine from a column in the list called group) and I don’t know how to write it, I feel as though it should be something like (but with images)
IF([Priority]==”(1) High” , “fab” ,
IF([Priority]==”(2) Normal” , “fda” ,
IF([Priority]==”(3) Low” , “9fa” , “000” )))
which is on one of your sources, but I don’t really know what I am doing, so I was wondering if you could point me in the right direction?!
I did get it to work with a content query, not using the calculated column, but I then couldn’t collapse the groups…
They blocked it as of today?! What!
Hello,
I am having some issues with my calculated columns, 2 were displaying images and one was just highlighting different colors, they were working perfectly, didn’t change a thing and now instead of displaying the images it days the HTML even though the returned data type is number. Any thoughts? Help!
Sorry, typo, “it shows** HTML”
Wondering if anyone is able to still get this to work with the new modern experience? I’m all of a sudden seeing the code as text, where it was working perfectly fine in the past.
looks like one of the latest SharePoint updates knocks off HTML display capabilities. Now, after it worked for month only the HTML code is shown:-(
Great!!