1/02/2012

SharePoint: Not all column types can be used in Calculated or Lookup columns (plus a trick to add back four of them!)

 

A recent question in the MSDN forums got me to looking again at the limitations on the use of certain column types in calculations or lookups. The list of column types that can be used in lookups is quite limited, but with a trick using a calculated column we can add four more to the list.

 

For my test I added all of the following columns to a custom list. These include the basic columns plus a few variations of options including each type of calculated column.

image

 

Types that work for a Calculated column

I then added a Calculated column. This is the list of fields offered for use in a calculation:

    image

Not included in this list were these columns:

    image

 

Types that work for Lookups

I then added a lookup column to another list to see which column types could be used for lookups. Here’s what shows up:

    image

The only column types available for lookups are:

    image

And when a lookup has been selected, here is the list of columns that can also be displayed from the lookup list:

    image

Or, just these types:

    image

So, these are excluded from lookups:

    image

 

A workaround for some missing lookup column types!

If you look back at the types that did show up in the list for lookup columns you will find:

    image

And if you go back to the list of types that can be used in calculated columns you will see that we can create calculated columns for some of the missing lookup types, as long as the calculation returns “Single line of text”!

With a calculation we now get to:

  Yes / No

  Choice (Drop-down or Radio Buttons, but not checkbox)

  External Data (and external data additional columns)

  Currency (but without the currency ($) symbol)

The calculation for Choice and External data is pretty straight forward:

   =[fieldname]

The currency type will just return the value, with commas and decimal points. You may want to add the currency symbol.

   =[fieldname]

or

   =”$” & [fieldname]

The Yes / No is a bit of a problem as the simple calculation just returns a 0 or 1. To get the words Yes and No you will need to do just a little more work:

  = if( [yesnofieldname], “Yes”, “No” )

Remember for all of these you will still need to set the "data type returned" to "Single line of text".

 

A few additional resources for what column types work where…

 

SharePoint Columns – Features and limitations

http://www.sharepointusecases.com/index.php/2011/07/sharepoint-columns-features-and-limitations/

 

Create list relationships by using unique and lookup columns

http://office.microsoft.com/en-us/sharepoint-server-help/create-list-relationships-by-using-unique-and-lookup-columns-HA101729901.aspx

 

Column types and options

http://office.microsoft.com/en-us/sharepoint-online-enterprise-help/column-types-and-options-HA010302193.aspx?CTT=1

 

SharePoint 2007 Supported Lookup Column Types

http://sharepoint.nauplius.net/2010/09/sharepoint-2007-supported-lookup-column.html

 

.

10 comments:

Anonymous said...

An excellent post, I have used the calculated method myself a couple of times but didn't know all the variations. Thank you.

Anonymous said...

Another excellent nugget of info. I find myself returning to your blog again and again for the great posts- Thank you!

Nancy said...

Awesome real world, practical workaround for SharePoint's list lookup limitations. I tried this calculated trick on linking one of my multiple choice (dropdown) lists and bingo - worked like a charm! Thanks Mike, your blog rocks.

Vivek said...

How calculated column works in Sharepoint? If a calculated column is added to list and the next moment it'll update list items. How it happens?

Mike Smith said...

Vivek,

Calculated columns are only calculated when a list item is updated. I.e. only when New or when Edited. Calculated columns are not updated when list items are displayed, i.e. they are not dynamically updated. That is why they don't allow the use of [Today] in calculations. If you need updates outside of New/Edit then you will need to write a workflow or external application that will update the items on a schedule.

Mike

Sruti said...

Hi Mike - this is super helpful but I'm struggling to figure out where to put in the calculation ("=[fieldname]"). I am trying to do a lookup to a Choice. Do you create a new column in the original table with the type " Calculated (calculation based on other columns)"? Is the [fieldname] the name of the column?
Thank you!!!
Sruti

Mike Smith said...

Sruti,

Add the calculated column to the same list as the choice. I.e. both are in the look up table.

Mike

Amos Garcia said...

This was possible in SharePoint 2010 before SP1, but after SP1 it seems to be no longer possible. I can't see a good reason why, and it is very difficult.

Anonymous said...

Hi Mike,

It works.. thanks!!, but had to modify the formula a bit, don't know why.
Modified as:
=IF(Outdated;"Yes";"No")

Mike Smith said...

> =IF(Outdated,"Yes","No") vs. =IF(Outdated;"Yes";"No")

This appears to be caused by culture settings on the web servers.

Note to spammers!

Spammers, don't waste your time... all posts are moderated. If your comment includes unrelated links, is advertising, or just pure spam, it will never be seen.