SharePoint column lookup and calculation limitations
List lookup columns in SharePoint are great. Easy to setup, simple to use, and powerful. But they have some limitations that can be frustrating.
Let me paint you a picture..
You have a SharePoint list that contains information about a customer entity (yes it should probably be in CRM, but lets assume you don’t have one) – fields like contact names/numbers, addresses, unique systems, notes, etc. Some of these fields are single lines of text, pull-down menus, yes/no radio buttons, multiple lines of text, you name it.
You have another list that relates to sales of products to customers. Unsurprisingly, you want to link a sale to a customer, and you want to leverage the power of lookup columns to make that a simple and seamless process.
Not an unrealistic scenario. Sure there are better ways of doing it with the likes of webservices into CRM or BCS connections into LOB databases, but they all involve additional systems, coding skills, and generally more effort. All things that aren’t always readily available.
By adding a lookup column type to the sales list you can allow a customer entity to be selected from your customer list. Where this gets handier is you can have the sales list pull other values from the customer list without adding extra columns. Awesome.
But… not all the columns from your customer list are available. Why not?
SharePoint can only perform a lookup of values from columns that contain a ‘text’ value, and then only if it contains a single line of normal text (ie. “Single line of text”, a “number”, or “date”). Any field that contains multiple lines of text, other lookups, or multi-select items won’t be available to you, as SharePoint will automatically hide any columns that it knows it can’t return.
This same restriction applies to using these column types in calculated columns, and there is a great post by Dessie Lunsford on getting around this limitation in terms of calculated columns which you’ll find here – http://www.endusersharepoint.com/2009/06/17/taming-the-elusive-%E2%80%9Ccalculated-column%E2%80%9D-referencing-multiple-lines-of-text-column/
The workaround involves creating your problem field as a “single line of text” column, then creating a second calculated column that references the first column name – eg. [=ColumnName]. You then delete the first column and recreate it with the exact same name but this time selecting your column type of choice.
While Dessie’s post deals specifically with referencing these columns via calculated fields, by dint of good fortune and SharePoint consistency, the same workaround fixes the lookup problem as well. Thanks Dessie!
This issue applies to all versions of SharePoint since 2007, including SharePoint Online (BPOS/Office365)
On The Socials
7 Responses
Werbeartikel Kugelschreiber says:
Great blog post, cool blog style, continue the good work
July 9, 2011 at 1:16 am
Erwin says:
Great idea, unfortunately it only works half for what I wanted to do : re-use the value of a Lookup column in a Calculated field. The principle works, but what is returned in the calculated field is the ID of the lookup result, not its value. And the ID is not something I can put to use. Too bad. Still, if anyone know a neat trick to re-use the value of a Lookup, do let me know. Oh, and I”m only a site owner who is not allowed the use of SP Designer, InfoPath or script 🙁
December 14, 2011 at 1:36 am
JB says:
Hi Erwin. What is the column type you”re trying to reference? Only certain column types return the item ID. Also worth pointing out that this is a workaround, not a fully-supported, problem-free solution.
January 26, 2012 at 10:30 pm
Jordan says:
Julian:
When I first read this I thought it may be a trick to get the string value of a LookUp column. When I tried it, just like Erwin, I found it only returned the ID from the LookUp field.
It’s still entirely necessary to use a workflow to create a mirrored string value of the lookup field. Unless, I guess, you only want the ID.
April 5, 2013 at 5:33 am
JB says:
As noted in the post, it’s entirely dependant on the column type you’re looking up – you can only use this method for fields stored as native text – columns containing lookups or multi-line text data wont work.
This isn’t a perfect solution – if you don’t have workflow options, or dev time, it *might* do what you need. Worked in my original use-case, but its not going to work for everything.
April 11, 2013 at 3:08 pm
David says:
My lookup is a simple list of words ‘single line of text’ in SharePoint 2010, it also is returning the index value… Years later we still have this problem… grrr
May 7, 2016 at 7:33 am
MG says:
Thanks JB, it worked for me!
A little convoluted (oh Microsoft!) but after paying careful attention throughout the steps I got it.
June 2, 2016 at 2:02 am