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.

 

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:

   

Not included in this list were these columns:

   

 

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:

   

The only column types available for lookups are:

   

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

   

Or, just these types:

   

So, these are excluded from lookups:

   

 

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:

   

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

 

.