Show status image in SharePoint column

Recently, I was discussing a clever trick with a colleague, for showing HTML in a SharePoint column.  

For SP2003 and SP2007, you needed to include some additional JavaScript (TEXT2HTML / examples) – but with SharePoint 2010 + 2013, you can do it using a calculated column.

Source column : Priority

Firstly, we need to add a column that we’ll use at the ‘checker’ column – this might be something like a STATUS column, or % COMPLETE, or PRIORITY.

  • Create Column : Priority
  • Type : Number

image

Add some List Data

Just add a few items to the list – and pick some priorities.

image

You can do whatever VIEWS, or SORTING or GROUPING – whatever…!

Find some images

Use your favourite search engine to look up some status images – I found a cool set here (more instructions here) :

image

Save these images locally – and upload to your SharePoint site – eg. SiteAssets, or Style Library.  I’ve loaded mine into an “IMG” folder.

Eg.   /Style Library/IMG/red.png

Calculated column

We can now do the magic + fun bit.

  • Create a calculated column : Indicator
  • Type : Calculated
  • Formula :

=”<DIV><img src='” & IF(Priority=1, “/Style Library/IMG/red.png”, IF(Priority=2,”/Style Library/IMG/yellow.png”, IF(Priority=3,”/Style Library/IMG/green.png”, IF(Priority=4,”/Style Library/IMG/blue.png”)))) & “‘></DIV>”

This is essentially doing the following :

  • If Status = 1, put an IMG tag for RED
  • If Status = 2, put an IMG tag for YELLOW
  • etc

NB.  The way that the formula works is like EXCEL :

  • IF (condition, TRUE, FALSE)

This method (above) is nesting these :

  • IF (condition1, IMAGE, IF (condition2, IMAGE, IF (condition3, IMAGE, IMAGE ) ) )

Click back to your list to view your column :

image

What the !!?!    It’s just showing HTML !!

😦

The UberMagic Tip

You’d be thinking that this is a “dud” tip – and you’ll need some “TEXT2HTML” JavaScript and so on – which would be annoying.

But no – the amazingly simple tip is this :

  • Change the return type to be a NUMBER.

Yep.   If defies logic – and doesn’t make sense – might even be a BUG !

But – it works.

image

Take a bow

If you’ve done the steps correctly, it should look like this :

image

Too easy – right !!?!

** You might need to include the ‘site collection’ prefix within the formula – pro tip !

=”<DIV><img src='” & IF(Priority=1, “/sites/SC1/Style Library/IMG/red.png”, IF(Priority=2,”/sites/SC1/Style Library/IMG/yellow.png”, IF(Priority=3,”/sites/SC1/Style Library/IMG/green.png”, IF(Priority=4,”/sites/SC1/Style Library/IMG/blue.png”)))) & “‘></DIV>”

Next ideas

Check this blog for some other renditions – very cool !

http://sharepoint.alanchai.com/2012/05/conditional-formatting-for-list-sp2010.html

image

image

Let me know if you like this tip – my buddy DanCol was #MindBlown over this one !

🙂

Advertisements

3 thoughts on “Show status image in SharePoint column

  1. Hi,
    I used this trick already a couple of times and just tried it with SharePoint Online (O365)
    Using SP Online the HTML text is shown and not the Image.
    Any Suggestion to get it working on SP Online as well?
    Thanks a lot
    Salvador

    Like

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