Supporters of End User
Web

Tips and Tricks: Use a lookup table instead of a choice dropdown list

 
Item is currently unrated. Press SHIFT+ENTER to rate this item.1 star selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+ESCAPE to leave rating submit mode.2 stars selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.3 stars selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.4 stars selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.5 stars selected. Press SHIFT+ENTER to submit. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.
Categories: 
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.

Comments

Greg Maass

Tips and Tricks: Use a lookup table instead of a choice dropdown list

Even better- create a site column to hold these values- if it is a global type of lookup, you will be able to manage the contents from one place.

Posted 30-Oct-2007 by Greg Maass
Mark Miller

Tips and Tricks: Use a lookup table instead of a choice dropdown list

Greg,

When creating the site columns, you will still have to manage through the Choice menu, which I find kind of hard to manage.

I recommend using the custom lookup list because you can create metadata about each of the lookup list items along with the list, itself.

Either method will work, I just find the custom list more robust. Thanks for the input.

Mark

Posted 30-Oct-2007 by Mark Miller
For The User

Tips and Tricks: Use a lookup table instead of a choice dropdown list

The Importance of MetaData in SharePoint In SharePoint, metadata is good. Metadata is very good. "That's great," you say, "but

Posted 01-Nov-2007 by For The User
Joseph Corpe

Tips and Tricks: Use a lookup table instead of a choice dropdown list

Mark,
I thought this was a good way to keep our customer list up to date across mutiple document libraries, but there is one problem. From what I've tested, when you create a lookup list, SharePoint no longer finds that data during a search. We have a site where we want the ability to type in a customer's name and it will find it throughout the libraries. Currently we only use WSS 2.0 and not Portal. If I use the Choice menu, the names will be found during a search. If I use a lookup list, they are not found.

Any ideas about how to work around that problem?

Posted 01-Nov-2007 by Joseph Corpe
Mark Miller

Tips and Tricks: Use a lookup table instead of a choice dropdown list

I'm going to ask Chris Quick if he has any visibility into that type of problem. It might be as simple as indexing the list, but he'd know better than I. Chris?

Posted 01-Nov-2007 by Mark Miller
Chris Quick

Tips and Tricks: Use a lookup table instead of a choice dropdown list

Mark,

I haven't seen this behavior in WSS 3.0 or MOSS, but I imagine it has to do with the way items are stored in a lookup field. In WSS 3.0 or MOSS, when you look at the stored value for a lookup field it actually will return as #43;Customer. This way, the item will still be indexed by SharePoint.

In WSS 2.0, I'm not sure how the data is being stored (it has been some time since I last explored 2.0). I believe the data in a lookup field for 2.0 is stored only as the ID. This means that you would only return 43 without an associated field, thus why the indexing fails.

I have confirmed using my site (MOSS 2007) that lookup fields will return appropriate values. We have a list of job positions maintained by our HR department. The list has a lookup to a departments list. I am able to find the postings for a specific department by typing the name of the department. Additionally, I turned off indexing of the departments list when it was first created so they wouldn't cloud the search results.

I wish I could be more assistance.

Posted 01-Nov-2007 by Chris Quick
Joseph Corpe

Tips and Tricks: Use a lookup table instead of a choice dropdown list

Our IT department plans on upgrading in the near term so I could build the lookup knowing it will work with WSS 3.0 and MOSS 2007. Thank you for the swift response.

Posted 02-Nov-2007 by Joseph Corpe
Linc

Tips and Tricks: Use a lookup table instead of a choice dropdown list

Question about lookup fields
We created a task list and a document lib
On the task list we created a lookup list that points to the document lib - problem is that the document name is not available for choice only ID & Title - ID is useless and Title is rarely filled out by the users. Any suggestions?

Thanks
Linc

Posted 14-Nov-2007 by Linc
Mark Miller

Tips and Tricks: Use a lookup table instead of a choice dropdown list

Linc,

Yes, I have run into this behavior, too, and it's really frustrating. For some reason, only specific fields are exposed as part of the lookup list.

One thing you might try is to make the title field required. That way people would have to fill in a title before they could upload or check in a file. Not the best of solutions, but it might be what you can use until I find a better way to do it.

Regards,
Mark

Posted 14-Nov-2007 by Mark Miller
Maru

Tips and Tricks: Use a lookup table instead of a choice dropdown list

I use look-up Columns pointing to lists as suggested above - great idea.

Do you know of a way to extend the principle so that a 2 column list can be used where the options available in the second lookup are determined by the choice made in the first look-up?

I might have a list of major subjects (Maths, Science, Phys Ed) and a list of minor subjects (Maths might have 'Algebra', 'Statistics', 'Geometry'; Science might have 'Biology', 'Physics'; 'Chemistry'; etc).

When I choose Maths from one lookup column, it would be great to only see 'Algebra', 'Statistics' and 'Geometry' in the second lookup.

Any ideas?

Posted 25-Nov-2007 by Maru
Mark Miller

Tips and Tricks: Use a lookup table instead of a choice dropdown list

Maru - I have used the connection functionality of web parts to do what you are asking, but I've never seen OOTB column functions that would allow what you are asking for.

Check out connecting two web parts on a page and it might give what you are looking for. Here's a link to a previous post where I demo how to do it: http://www.endusersharepoint.com/?p=59

Posted 26-Nov-2007 by Mark Miller
Torbjorn

Tips and Tricks: Use a lookup table instead of a choice dropdown list

It seams to be a problem when you use the lookuplist in a library when the field you points to for "Get information from" is calculated. (I have created a custom list with two colums, ProjectName and Company that is concatenated to a Company_ProjectName) field. Company_ProjectName is used in "Get information from". There is two problems 1) Content types using it views a selection list where all items starts with string;# before the item name. 2) Views that are using the column for grouping fails. Any idea?

Posted 27-Nov-2007 by Torbjorn
Torbjorn

Tips and Tricks: Use a lookup table instead of a choice dropdown list

An addition to my post #12 see http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1279818&SiteID=17

Posted 27-Nov-2007 by Torbjorn
Antoine Pichot

Tips and Tricks: Use a lookup table instead of a choice dropdown list

Hi,

You also can create fields lookup dynamically with a link to your list.
I've post somme code on http://www.codeplex.com/FieldLookupBuilder

Hope this helps

Posted 27-Nov-2007 by Antoine Pichot
Mark Miller

Tips and Tricks: Use a lookup table instead of a choice dropdown list

Torbjorn - You found your answer before I could even get around to posting! Thanks for the update.

Mark

Posted 27-Nov-2007 by Mark Miller
Heather F

Tips and Tricks: Use a lookup table instead of a choice dropdown list

Any help on my lookup problem would be appreciated.

Lets say I have have twenty customers in a Customer List. Fifteen records have a Status column value of Active and five that have a Status column value of Inactive. If I create a lookup column using the Customer List, is there any way to limit the items returned to only the fifteen active records and not the entire twenty?

Thanks a bunch

Posted 29-Nov-2007 by Heather F
Mark Miller

Tips and Tricks: Use a lookup table instead of a choice dropdown list

Heather - What you are asking for makes sense in a more robust environment, but you can't do it with OOTB SharePoint in this iteration. There are many shortcomings in the way lookup columns work, and this is one of them.

This could probably be done with code, but I don't think that's the solution you're looking for.

Sorry I couldn't be of more help.
Mark

Posted 29-Nov-2007 by Mark Miller
Tracie F

Tips and Tricks: Use a lookup table instead of a choice dropdown list

What about when your lists are on 2 different sites? I.e. the days of the week list is on the main site, and the list you want to use the DOW list in is at a different level.

Posted 03-Dec-2007 by Tracie F
Rich Rockwell

Tips and Tricks: Use a lookup table instead of a choice dropdown list

I am using a couple lookup lists for users to select metadata for document libraries. When the user sees the document on a list, the value from the lookup list appears as a link. If they click the link, it takes them to the page for the list item or if they don't have permission, they get an error message that they don't have permission. Is there some way I can disable this link so the user only sees the text value and not a link to the list?

Posted 12-Dec-2007 by Rich Rockwell
Mark Miller

Tips and Tricks: Use a lookup table instead of a choice dropdown list

Rich - Modify the view that is displaying the link. It probably says something like "... with Edit" turned on or something like that. Uncheck the box for that item and select the one that does not offer the Edit capability.

Hope that helps.
Mark

Posted 13-Dec-2007 by Mark Miller
Rich Rockwell

Tips and Tricks: Use a lookup table instead of a choice dropdown list

Thanks, but there is no such option for a column that comes from a look-up list. I tried everything I could think of to try to prevent the field from being a hyperlink and nothing worked.

I did find a solution, in the meantime. The place where this was specifically a problem was in grouping by the field from lookup list. On the group headers, the column value was a link and users instinctively clicked on it. That takes them to a detail page for the item, then if they click "Close," they land on the lookup list page.

To remove the link from the group headers, I used SharePoint Designer to convert the webpart to an XSLT Data View, then I edited the field as a hyperlink, and removed the link.

Posted 13-Dec-2007 by Rich Rockwell
Mark Miller

Tips and Tricks: Use a lookup table instead of a choice dropdown list

Rick - Thanks for the update.

Posted 13-Dec-2007 by Mark Miller
Greg Maass

Tips and Tricks: Use a lookup table instead of a choice dropdown list

I've run into a problem after setting this up- depending on user's permissions, they do not see the item from the linked list. Has anyone run into this/

Posted 28-Dec-2007 by Greg Maass
Mark Miller

Tips and Tricks: Use a lookup table instead of a choice dropdown list

Greg - The End User will have to have permissions to access the list being pointed to in order for the link to work.

Hope that helps,
Mark

Posted 28-Dec-2007 by Mark Miller
Greg Maass

Tips and Tricks: Use a lookup table instead of a choice dropdown list

I've been using lookup fields per Mark's method for a while, and recently discovered a gotcha, which also has potential good uses. We have dedicated project sites for our projects, and use a Role assignment list to assign people to roles. There is a role definition table that is used by the role assignment table with a lookup to display the definition of the role the user is assigned to.

Both the Role Assignment and Role Definition lists were saved as list templates so that they can be recreated on new sites. However, the lookup field in the Role Assignment list still refers back to the original Role Definition list in the original site where it was created. So, while the intention was for the lookup column to lookup against the role definition column in the list in the same site in which it was created, it was actually holding onto a reference to the list id of the original list.

This could be used in a good way, as we might not need to copy the role definition list to each new project site- the role assignment table could always refer back to the original role definition table. By design I know that lookup columns are only supposed to do lookups against lists on the same site, but this is a way of getting around that.

Posted 28-Dec-2007 by Greg Maass
Mark Miller

Tips and Tricks: Use a lookup table instead of a choice dropdown list

Greg - I have used the same technique to pass information to new lists through the template. What I have run into insome implementations is that the data being pointed to was proprietary, they didn't want anyone to even know it existed... major problem if there was an oversight.

However, as you say, it is a nice way to get around the site list limitiation.

Thanks,
Mark

Posted 28-Dec-2007 by Mark Miller
Lori

Tips and Tricks: Use a lookup table instead of a choice dropdown list

This is an interesting solution. My only problem is that in a list like days of the week or months, the site column list doesn't maintain the same order as the custom list. That's kind of a pain in the neck when Friday is the first item on the list.

Thanks,
Lori

Posted 04-Jan-2008 by Lori
Mike

Tips and Tricks: Use a lookup table instead of a choice dropdown list

Mark,

I like the approach, although I find your examples somewhat strange. When are you expecting that the days of the week or months of the year will change? Isn't that something you want to store in a site column?

BTW I just discovered Schemalogic.com, a great solution for large enterprise solutions I think.

Posted 28-Jan-2008 by Mike
Mark Miller

Tips and Tricks: Use a lookup table instead of a choice dropdown list

Mike - As Steve McQueen said in The Magnificent Seven, "It sounded like a good idea at the time." You are correct in your observation. I was trying to give universal examples that everyone could relate to.

Posted 28-Jan-2008 by Mark Miller
lavanya

Tips and Tricks: Use a lookup table instead of a choice dropdown list

Dear All,

Please let me know if it is possible to restrict write access to only one column in a list or library. the request is as follows:

There will be 10 or more columns in a list or library. Group A will have write access in all the fields. Group B will have read access for all the fields but write access for only one column. Does anyone have any idea how to implement this? Thanks in advance for the feedback.

Posted 11-Feb-2008 by lavanya
Mark Miller

Tips and Tricks: Use a lookup table instead of a choice dropdown list

Lavanya - I have seen this type of request before but have not seen a solution. There is probably a programatic way to do it, but OOTB, I don't find that functionality.

I am also interested if someone has a working solution to demo. -- Mark

Posted 11-Feb-2008 by Mark Miller
lavanya

Tips and Tricks: Use a lookup table instead of a choice dropdown list

Hi Mark,

Thanks a bunch for your prompt reply.

I have found a solution using workflows(primary and secondary). The solution is as follows:

I created a custom list with say 10 columns and gave edit access to Group A.Let's say column 9 is the one that can be edited by Group B. There is no direct way this can be implemented in the list. So I created a primary workflow which runs when a new item is created in this custom list. This workflow creates collect data from user task in the task list. In this collect data form I have added a field by the name column 9 that needs to be entered by the Group B. Primary workflow pauses until the data is entered by Group B. Group B gets a task mail. In the task list I have created a secondary workflow that runs when a new item is created. Also I have added columns 1 till 10(except 9) in the task list. The secondary WF will update this columns by copying the column values from the custom list. When Group B edits their task, they get only one column to edit, i.e column 9. In the view, they see all the fields. Once the data is entered by the user, the column 9 in the custom list is updated with this value by the primary WF.

I updated the columns in the task, so that the look and feel is same as the custom list. This is optional. Group B has only read access in the custom list and write access in the task list.

I hope what I have written is clear. This may not be the right solution. But it works for me. I have a con call with my client today. Hope he likes the solution. We have not yet started coding, so for the moment we look only for solutions without coding.

-Lavanya.

Posted 14-Feb-2008 by lavanya
Mark Miller

Tips and Tricks: Use a lookup table instead of a choice dropdown list

Lavanya - Thanks for the followup. I hope this helps others with this commonly requested functionality. Please let us know how your client responds. If all works well, maybe we can do a screencast of your solutions as the "Case Study of the Month". -- Mark

Posted 14-Feb-2008 by Mark Miller
Silent Mule

Tips and Tricks: Use a lookup table instead of a choice dropdown list

Does anyone know how to get it so I can have a Lookup field that uses a calculated field in another table? There doesn't seem to be any information anywhere that I can find yet it seems such an obvious thing to want to do. My requirements is to create a concatenated name from first name and last name in Contacts (not the Full name that it comes with) and use that as a lookup in another table. It seems beyond doing though!

Posted 25-Feb-2008 by Silent Mule
Mark Miller

Tips and Tricks: Use a lookup table instead of a choice dropdown list

I just tried it and there doesn't seem to be a problem. What type of errors are you getting?

Mark

Posted 27-Feb-2008 by Mark Miller
Rodney Moreland

Tips and Tricks: Use a lookup table instead of a choice dropdown list

Hello, Mark.
You had replied earlier to a user (Oct. 30, 2007) regarding Site Columns and stated that you still have to manage through the choice menu which you found difficult to manage. You can actually setup a site column that is a lookup from a custom list that you create. This is a good way of creating site columns by using actual lists.

For example, on the top level site I create different custom datasheet lists that I will use as site columns. By default I call them SC_. I use "SC_" in the beginning because it groups all of these types of lists together when I "View all Site Content".

So as an example, I would create a custom list called SC_GEOS and in the list I would create AMER, APAC, and EMEA. Then I would go create a Site Column called GEOS and select Lookup (information already on this site) and then point the lookup back to SC_GEOS. Now I have a site column that can be dynamically updated by adding to SC_GEOS.

Of course, you could have people just go directly to SC_GEOS to do their lookups for the information but the reason why you want to promote site columns is so that there is one repository or location where you can look for common data.

Just a thought.... :-)

Posted 01-Mar-2008 by Rodney Moreland
Mark Miller

Tips and Tricks: Use a lookup table instead of a choice dropdown list

Rodney - Yep, you are correct. I saw Bob Mixon doing that a couple months back and have actually changed the way I create lookups. The advantage to using a custom list in a site column is that the list is visible through the site column across the entire site collection, not just from within the site it was created.

Thanks for the update.
Mark

Posted 01-Mar-2008 by Mark Miller
me

Tips and Tricks: Use a lookup table instead of a choice dropdown list

Outlook can't handle lookups. So if for example: I have two contact lists, people and companies because we might have 20 people who work for the same company from the same building, so it makes sense to do a look up for their company name and address, etc rather than filling in manually each of the 20 contacts company details which are all the same. But then when you link the people contach list to Outlook 2003 or 2007 the persons company details are blank, because outlook doesn't do the look up. Now we can't do mail merges in Office becuase of this problem. So i tried linking webparts to get a combined list, I failed. I tried a datasheet view to combine lists using SPD2007, failed doing the look ups, couldn't link it to Outlook after as it's not a contact list. Any advice would be well received.

Posted 03-Mar-2008 by me
Obieg Dokumentow

Tips and Tricks: Use a lookup table instead of a choice dropdown list

lavanya,

To allow users edit item's fields selectively you should to:
1) enable item approval (this makes datasheet view read-only)
2) close standard ListItem web part on EditForm
3) in SPD insert 2x Custom List Form - one in Edit Mode and one in Display Mode - you will paste display-form-controls wherever you want your to limit your users possibilities.

4) on Custom List Form in EditMode insert








5) remove second webpart

Possible DESIRED_MASK values are:
ViewListItems - 1
AddListItems - 2
EditListItems - 4
DeleteListItems - 8
ApproveItems - 16
OpenItems - 32
ViewVersions - 64
DeleteVersions - 128
CancelCheckout - 256
PersonalViews - 512
ManageLists - 2048
ViewFormPages - 4096
Open - 65536
ViewPages - 131072
AddAndCustomizePages - 262144
ApplyThemeAndBorder - 524288
ApplyStyleSheets - 1048576
ViewUsageData - 2097152
CreateSSCSite - 4194314
ManageSubwebs - 8388608
CreateGroups - 16777216
ManagePermissions - 33554432
BrowseDirectories - 67108864
BrowseUserInfo - 134217728
AddDelPrivateWebParts - 268435456
UpdatePersonalWebParts - 536870912
ManageWeb - 1073741824
UseRemoteAPIs - 137438953472
ManageAlerts - 274877906944
CreateAlerts - 549755813888
EditMyUserInfo - 1099511627776
EnumeratePermissions - 4611686018427387904
FullMask - 9223372036854775807

Posted 10-Mar-2008 by Obieg Dokumentow
Obieg Dokumentow

Tips and Tricks: Use a lookup table instead of a choice dropdown list

Something happend to XSL I pasted in step 4, so I'll put it without markings:

For each field you want to limit edit possibility

xsl:choice
xsl:when test="ddwrt:ifHasRights(DESIRED_MASK)
edit-mode-ctrls-from-1st-webpart-stay-here
/xsl:when
xsl:otherwise
paste-disp-mode-ctrls-from-2nd-webpart-here
/xsl:otherwise
/xsl:choice

Posted 10-Mar-2008 by Obieg Dokumentow
Paul

Tips and Tricks: Use a lookup table instead of a choice dropdown list

2 comments and a question:

1. Mark --- you are the absolute best at keeping up with the site comments. So many sharepoint bloggers respond to only the first few comments (if any) and then act as if they never wrote the blog entry. Your follow through is exemplary.

2. This is an outstanding thread.

Question ref: lookup columns --- I greatly appreciate all the examples documented here; however - can anyone tell me why a sharepoint "lookup" column has to be self-defined (or am I missing the boat)? (yes, I know what a lookup column is... please read on).

As an example cited a few places here; a DYNAMIC client list. We have a client lookup source in "system A" that I can populate in an infopath form for form services via a SQL connection - why can't I leverage the same pre-existing SQL lookup table in sharepoint lists? All the examples I see in this thread require manual input to maintain the master source - be it site column - custom list, etc.

The ability to define values by a sql query seems to be a no-brainer; what am I missing?

Thanks

Posted 11-Mar-2008 by Paul
Mark Miller

Tips and Tricks: Use a lookup table instead of a choice dropdown list

Paul - "You are missing" the Business Data content type at the bottom of the data type check list when you create the column. "The type of information in this column is: Business Data".

Do a little digging and then come back up for air. I think you might be busy for the next couple of days.

Regards,
Mark

Posted 11-Mar-2008 by Mark Miller
arindam

Tips and Tricks: Use a lookup table instead of a choice dropdown list

please give how to get a look up table in my web page using a global web page that would have a grid view to get table(stored in mysql-database server) values into my web site

Posted 11-Mar-2008 by arindam
Mark Miller

Tips and Tricks: Use a lookup table instead of a choice dropdown list

Arindam - Sorry, I don't understand what you are asking for. What is a "global web page"?

Posted 12-Mar-2008 by Mark Miller
Kevin Gauthier

Tips and Tricks: Use a lookup table instead of a choice dropdown list

For me, the biggest problem is: is there any way to have a required lookup column autofilled when uploading multiple documents, the same way a choice field would?

I want to help my user as much as I can, more so then helping myself in administration. Any suggestion on that would be appreciated.

Posted 24-Mar-2008 by Kevin Gauthier
Fahmy Farouk

Tips and Tricks: Use a lookup table instead of a choice dropdown list

Hi All,
i have created a list with Lookup field to another list on the site
Up till now everything is ok BUT i do not need that lookup field contains link to the related list
although i selected the field to associate with Title (Without link) BUT still linked to the original List Why?
Can anyone help me?

Thanks

Posted 26-Mar-2008 by Fahmy Farouk
Obieg Dokumentow

Tips and Tricks: Use a lookup table instead of a choice dropdown list

Hi Fahmy,

The link in a lookup column is generated by rendering template of the column itself - that doesn't matter what the target lookup field is. Normally (as a non-developer) you can't affect the way this field is rendered.
However you can insert a dataview web part on a webpart page and there you actually can format your lookup column, ie. remove the link.

Posted 26-Mar-2008 by Obieg Dokumentow
Andrea Gentilini

Tips and Tricks: Use a lookup table instead of a choice dropdown list

Hi all.
Why don't you think to create a sharepoint custom field to solve this problem?

Posted 16-May-2008 by Andrea Gentilini
Terry Irons

Tips and Tricks: Use a lookup table instead of a choice dropdown list

Has anyone tried using content approval on a list that has a column that is used in a lookup column in another list? When you update the column in the primary list and it is in a pendintg state you get two different values for the lookup column depending on what level of permissions you have. Users who don't have approval permissions see the current approved version and approvers and higher see the version that is pending approval. Not a nice scenario when you are building a safety related application.

Posted 04-Jun-2008 by Terry Irons
Dan

Tips and Tricks: Use a lookup table instead of a choice dropdown list

Is there anyway to use a lookup and select more than one item from another list.. For eg

I have an Course List, then I use a lookup to select students from another list.. but I can only select one student, but need to select at least 10.. Is there a way to do it..?

Posted 11-Jun-2008 by Dan
Kevin Gauthier

Tips and Tricks: Use a lookup table instead of a choice dropdown list

Yes Dan, when you create your lookup column, you have a checkbox that says "allow multiple values" or something similar. That will do what you want to do.

Although, I must warn you that many different bugs and problems have been reported with this features. You might want to browse around this a little bit before using it. Also, multiple values in lookup column is poorly integrated from Office 2007 Properties Pane.

Cheers, Kev

Posted 11-Jun-2008 by Kevin Gauthier
Dan

Tips and Tricks: Use a lookup table instead of a choice dropdown list

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..

Posted 11-Jun-2008 by Dan
Kevin Gauthier

Tips and Tricks: Use a lookup table instead of a choice dropdown list

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

Posted 13-Jun-2008 by Kevin Gauthier
Jack V

Tips and Tricks: Use a lookup table instead of a choice dropdown list

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:"

Posted 25-Jun-2008 by Jack V
Mark Miller

Tips and Tricks: Use a lookup table instead of a choice dropdown list

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

Posted 25-Jun-2008 by Mark Miller
Mark

Tips and Tricks: Use a lookup table instead of a choice dropdown list

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.

Posted 03-Jul-2008 by Mark
IT-Dev Sharepoint Components

Tips and Tricks: Use a lookup table instead of a choice dropdown list

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.

Posted 03-Jul-2008 by IT-Dev Sharepoint Components
Liv

Tips and Tricks: Use a lookup table instead of a choice dropdown list

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: 0x80020009 (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

Posted 18-Jul-2008 by Liv
Brent Pliskow

Tips and Tricks: Use a lookup table instead of a choice dropdown list

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?

Posted 25-Sep-2008 by Brent Pliskow
Larry Bradshaw

Tips and Tricks: Use a lookup table instead of a choice dropdown list

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.

Posted 21-Oct-2008 by Larry Bradshaw
Jacek

Tips and Tricks: Use a lookup table instead of a choice dropdown list

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.

Posted 08-Dec-2008 by Jacek
EndUserSharePoint

Tips and Tricks: Use a lookup table instead of a choice dropdown list

Jacek - Post your question on Stump the Panel and you'll get a more timely response. -- Mark

Posted 08-Dec-2008 by EndUserSharePoint
Antoinette

Tips and Tricks: Use a lookup table instead of a choice dropdown list

hi, i have the same question as larry (#60) above -- any ideas on this one? thanks!

Posted 03-Apr-2009 by Antoinette
EndUserSharePoint

Tips and Tricks: Use a lookup table instead of a choice dropdown list

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

Posted 03-Apr-2009 by EndUserSharePoint
Antoinette

Tips and Tricks: Use a lookup table instead of a choice dropdown list

wow, you really are on the ball with keeping up with comments! :) thank you for your help and for such a prompt reply.

Posted 03-Apr-2009 by Antoinette
Modiyam

Tips and Tricks: Use a lookup table instead of a choice dropdown list

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.

Posted 16-Apr-2009 by Modiyam
Larry

Tips and Tricks: Use a lookup table instead of a choice dropdown list

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

Posted 21-Apr-2009 by Larry
Bill

Tips and Tricks: Use a lookup table instead of a choice dropdown list

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

Posted 28-Apr-2009 by Bill
Bill

Tips and Tricks: Use a lookup table instead of a choice dropdown list

Sorry - that last last line have read "with the default 'text' box empty..."

Posted 28-Apr-2009 by Bill
EndUserSharePoint.com: Top 10 Subscribed Posts | End User SharePoint

Tips and Tricks: Use a lookup table instead of a choice dropdown list

[...] Tips and Tricks: Use a lookup table instead of a choice dropdown list [...]

Posted 18-Aug-2009 by EndUserSharePoint.com: Top 10 Subscribed Posts | End User SharePoint
craig

Tips and Tricks: Use a lookup table instead of a choice dropdown list

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?

Posted 18-Aug-2009 by craig
EndUserSharePoint

Tips and Tricks: Use a lookup table instead of a choice dropdown list

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

Posted 18-Aug-2009 by EndUserSharePoint
Jais

Tips and Tricks: Use a lookup table instead of a choice dropdown list

Ok, so how do I render my lookup as a radio button list instead of a dropdown?

Posted 20-Jan-2010 by Jais
Bill

Tips and Tricks: Use a lookup table instead of a choice dropdown list

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.

Posted 20-Jan-2010 by Bill
Julie

Tips and Tricks: Use a lookup table instead of a choice dropdown list

Bill,
Try adding "_Select One" in your lookup list (no brackets). The first option defaults to blank. It worked for me.

Posted 22-Feb-2010 by Julie
Bill

Tips and Tricks: Use a lookup table instead of a choice dropdown list

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

Posted 22-Feb-2010 by Bill
Julie

Tips and Tricks: Use a lookup table instead of a choice dropdown list

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

Posted 24-Feb-2010 by Julie
Jim Huk

Tips and Tricks: Use a lookup table instead of a choice dropdown list

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

Posted 05-May-2010 by Jim Huk
Diane

Tips and Tricks: Use a lookup table instead of a choice dropdown list

Bill and Julie,
I am having this same issue. Did you ever get it solved?
Thanks!

Posted 16-Jul-2010 by Diane
Joseph Baarsch

Tips and Tricks: Use a lookup table instead of a choice dropdown list

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!

Posted 26-Jul-2010 by Joseph Baarsch

Notify me of comments to this article

E-mail:
   

Add Comment

Title:

 
Comment:
Email:

   


Name:

 
Url: