Tips and Tricks: Use a lookup table instead of a choice dropdown list
One of the column types used when creating new columns for a list or library is the Choice (menu to choose from) type. This is OK to use when there is a short list that will never change, but if the list is long or something that’s going to changed and be updated over time, you’ll need a better solution. That’s where a lookup list comes in.
A look up list is a custom list that holds all the choices you would want for a dropdown list. The trick is to point to the custom list from another list or library when the list is needed. Examples include the days of the week (7), months of the year (12), states in union (50), planets in the solar system (8 or 9, depending upon who you talk to).
Follow these steps to create your lookup table and link it to another list:
- Create a lookup list
- Site Actions -> Create
- Custom Lists: Custom List
- Configure New screen
- Name: Lookup_DaysOfWeek
- Description: Days of the week
- Display on Quick Launch?: No
- Click Create button
- Use the New button or Datasheet view to enter the names of the week in the title column
- Use the lookup list in a library
- Open an existing library or create one (that needs days of the week as a column)
- Settings -> Create Column
- Column configuration screen
- Column name: Day of the Week
- The type of information in this column is: Lookup (information already on this site)
- Description: Choose a day of the week
- Require that this column contains information: Yes
- Get information from: Lookup_DaysOfTheWeek
- In this column: Title
- Click OK button
When you create a new item in the library, you will now have a dropdown list available to you that contains the days of the week. This is great when you have to use the same lookup list in multiple places on your site. Create it once and then point to it as needed.
Moral of the story? If your list is longer than three items and has the potential for reuse, create a lookup list and relax.
Cheers Kev,,
But I’m talking from a SPS 2003 site.. Really didn’t think it would be this hard. Searching over the net only ever brings up 2007 examples..
It is not possible in 2003, and bugged in 2007…
I’m sorry :(
I was also enthousiastic about this at first but soon removed it… :l Life of Sharepoint my friend
Interesting thread but I think i’m missing the “Get information from” fields.
I don’t see any option for that. I’m using Moss2007.
I don’t see these items listed in this site:http://www.kwizcom.com/ProductPage.asp?ProductID=611&ProductSubNodeID=612
My list of radio button actions stop after “Date Time:”
Jack – Did you choose “Lookup using a list on this site” as your datatype?
I tried to access through your link and got a 500 error… no way to view anything. Even if I could, I wouldn’t have admin rights to see how the column is configured.
Regards,
Mark
I am having 3 problems using this, some of which have been asked above:
(1) How to order the items in the lookup list the way I like, not defaulting to alphabetical.
(2) How to set a default value.
(3) Is there any way to add a value ad hoc?
In other words, make lookup more like a choice, but have the data come from somewhere else.
As I’ve written some time ago, OOB lookup has no support for sorting and filtering, so you need to install custom field types like View Filtered Lookup for Sharepoint.
This particular solution allows you to make cross site, filtered and sorted lookups in web view, but in Document Information Panel in Office client apps, like Word, Excel etc. it would act as normal not filtered lookup. This is Microsoft bad design and hard-coding of some fetures in Sharepoint and Office and as for now is not possible to work around.
Hi all,
Talking about lookup columns and “allow multiple values” feature, I want to share with you the following problem. When you have lookup column that allows multiple values in people and groups list, you can’t insert new user. Yes, it throws exception:
Exception occurred. (Exception from HRESULT: 0×80020009 (DISP_E_EXCEPTION)) at Microsoft.SharePoint.Library.SPRequestInternalClass.UpdateMembers(String bstrUrl, Guid& pguidScopeId, Int32 lGroupID, Int32 lGroupOwnerId, Object& pvarArrayAdd, Object& pvarArrayAddIds, Object& pvarArrayLoginsRemove, Object& pvarArrayIdsRemove, Boolean bSendEmail)
at Microsoft.SharePoint.Library.SPRequest.UpdateMembers(String bstrUrl, Guid& pguidScopeId, Int32 lGroupID, Int32 lGroupOwnerId, Object& pvarArrayAdd, Object& pvarArrayAddIds, Object& pvarArrayLoginsRemove, Object& pvarArrayIdsRemove, Boolean bSendEmail)
When you uncheck the “allow multiple values” for the lookup column you don’t have problems..
Anyone that have solved this issue?
Thank you
To Chris – I’ve run into Joseph Corpe’s issue noted above in a MOSS environment. It appears the only results being returned are for items where the search term is equal to the first item in the lookup field. If the term is anywhere after the first lookup field, then it will not return in results.
For Example:
Let’s say you have a lookup field connected to a list of Departments (Finance, HR, IT, Legal).
You have two items in your list.
List Item #1 has Finance and HR selected, so it displays as “Finance, HR”.
List Item #2 has HR, IT and Legal selected, so it displays as “HR, IT, Legal”.
When you perform a search on “HR”, only List Item #2 is returned.
Any ideas?
I have the same issue as Brent and have been searching across the web for the last couple hours to find this thread. Any luck? I tried going over to Stump the panel but have not found a related thread. Anyone know of a work around or exactly what the issue is created by please post a link if you could. Thanks!
I have two SharePoints lists, Project List and Effort List. Multiple records in the Effort List tie to an individual record in the Project List.
I can use the Lookup (information already on this site) field/column in the Effort List to allow uses to select a field in the Project List.
Where I can’t figure out what next is it will only bring back the field they selected from the Project List, but what I want is it to bring back multiple fields from that selected record.
Example:
Project List has multiple records, with four fields each:
Project ID, Project Description, Project Cost, Project Due Date
Effort List has multiple records, with three fields each:
Project ID, Effort Description, Effort Status
If Project ID abc is selected in a drop down within the Effort List New Item entry, I want to bring back more than Project ID abc, I also want to display Project Description, Project Cost and Project Due Date.
I have SharePoint Designer 2007. Any help is appreciated.
Do you guys know if there is a work around for the following issue:
I setup a custom column ‘Product type’ to pull a list of possible values from a lookup list, it works fine until I remove a value from the lookup list.
The deleted value also disappears from the metadata of a document it’s describing.
So if we have a document that relates to product x, y and z (x, y, z appears in the ‘Product Type’ column) and we discontinue product ‘z’ (ie. delete it from the lookup list) the ‘z’ value will also disappear from the ‘Product Type’ column of that document (even if the document is fully approved and in its major version).
When doing the same with the choice option it works fine, any modifications to the choice list do not affect existing files’ metadata. I would use this method only our lookup needs to be modified often and choice option is cumbersome to manage.
Jacek – Post your question on Stump the Panel and you’ll get a more timely response. — Mark
hi, i have the same question as larry (#60) above — any ideas on this one? thanks!
Lookup lists in SharePoint only pull back one column from the lookup table. For a more complex solution, you’ll probably want to hit on Paul Grenier in Stump the Panel to see if he can do that with jQuery. — Mark
wow, you really are on the ball with keeping up with comments! :) thank you for your help and for such a prompt reply.
Hi All,
I’m new to the SharePoint,
Thanks for the great discussions, i got the same issue which is posted by Maru in the above Post#10 .
Any help on this problem would be appreciated.
Thanks in advance.
I am also new to SharePoint. Using WSS 3.0 with Office 2003.
I built a Contacts and a ContactTypes table in MS Access and exported each to SharePoint.
Several of the columns in Contacts have codes, such as ContactType which in Access I perform looups to another table to provide the long description (rather than displaying a code.
My expectation was to do the same type of lookups in SharePoint, rather than store the long version in the actual list.
Is this possible without having some other tools other than WSS 3.0 and Office 2003?
Please advise.
Thanks,
Larry
I’m using SharePoint Server 2007. I have a List called Color. The list has a single column, Color, renamed from the default Title column. The list contains the following entires: Green, Yellow, and Red. I have another list that tracks customer issues. This second list has a Color column that is set as a lookup to the Color column on the Color List. I have made this a required field. The default text box is blank. When I open the form, the Color field defaults to Green, the first value in the list. I want to force the user to make a choice, and don’t want a default value. With the required field checked and the default check box empty – why is going wrong that I ened up with a default value? Thanks very much! Bill
Sorry – that last last line have read “with the default ‘text’ box empty…”
Question:
This seems to work well for lists and when you use the generic “upload” function in document libraries. How do you make it work when you use the “upload multiple documents” function?
Craig – One of the problems with the multiple upload feature is that there is no request for metadata, so this is not a problem with the lookup column, it’s a bug in the SharePoint interface. — Mark
Ok, so how do I render my lookup as a radio button list instead of a dropdown?
Hi. I asked this question a while back, but never did get a response. (August 2009) I’ll word it a bit differently. My problem is with using a Lookup column that is also designated as a required field. I would expect in that situation that when a new form is opened that the lookup field would be blank, and that the user would be forced to populate the field from the choices in the drop down before “saving”. What I find is that the field defaults to the first value in the lookup list – and the user is not forced to do anything. Instead, they are likely to save the form with the wrong value. This bavoir changes depending upon how many values are returned in the drop down for the look up field. If there are less than 21 entries – what I have just described is what happens. if there are 21 or more values, then the field works as I would expect – the field is blank and the user is forced to make a choice. Is this a bug, or am I doing somehting wrong? I’m using SharePoint Server 2007.
Bill,
Try adding “_Select One” in your lookup list (no brackets). The first option defaults to blank. It worked for me.
Julie,
I appreciate the feedback. If I enter _Select One as an entry, and the total number of entries in my list are under 21 – then the first value in the list doesn’t default to blank, it defaults to _Select One, and if the user doesn’t change it, _Select One is what is recorded in the record. If the field is made to be required, I only get a blank choice if there are 21 or more items in the lookup list. 21 items seems to be the key to this bug\problem. How many items in your lookup list that this is working for you? Are you on Sharepoint 2007, or 2010?
Thanks again for your help!
Bill
Bill and Julie,
I am having this same issue. Did you ever get it solved?
Thanks!
Bill,
So sorry, I have to retract my solution. Long story short, there were 2 of us trying to solve this issue at my place of work and what I thought was a solution was a a view of a choice drop-down (with a blank default value) that my colleague had inserted at the same time I had added the _Select One in the lookup list. My bad for not double-checking before posting a reply. So the question is still out there and I will continue to monitor this blog for a solution.
Julie
Hi:
I’m trying to implement Sharepoint 2007 to do a good deal of tracking and reporting in my business area. I’ve got 2 lists that should have a 1 to many relationship, so the second is a lookup to the first. Works great on input, but when trying to report out the ID of the second table is not shown in any view I can find usable: they appear as links in Standard view but go away in Spreadsheet. In trying to work around I linked them both in Access hoping to join them there, but the lookup column simply does not appear. Does anyone have a workaround that doesn’t involve add-ons to the server as my corporate IT won’t be adding anything to the farm?
Thanks,
Jim