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

One thought on “Show status image in SharePoint column

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