Friday, September 26, 2008
EndUserSharePoint.com: Best Practices – Replace Choice Lists With Lookup Lists
Choice lists are easy to setup but you really don’t want others modifying your content types just to change the choice options. Instead, create your column as a Lookup column and point to a Custom List (Title only).
Now you can give permission to others to manage the list of values without risking your information architecture to untrained users.
There is one key advantage of lookup lists over choice lists.
Imagine you make a typo when creating your choice list. Or the name in the choice list changes after a couple months (picture a list of projects or contacts for example). With lookups, you only need to update the lookup list. With a choice list you need to update all the places where you used the misspelled or outdated choice.
On the other hand, a shortcoming of lookups is that you cannot use them in calculated columns.
Christophe,
Great point! We had some questions about calculation columns and lookups on the STP forums this week.
I usually balance the value of lookups against the extra work I will need to do for a data view web part or content query web part if I need to “calculate” those columns.
Lookups still win out on my sites in most cases because they offer built-in navigation between master and child data objects.
This is how we manage this at our company. One thing you have to be careful of is modifying the lookup list. When a user modifies one of the selection values, that cascades into existing records into the other lists/document librarieS that read from the lookup. This can cause unintended consequences if a user sees a value is no longer useful and attempts to replace it with something different. The will cascade into existing records that had the previous lookup value.
John,
Maybe another best practice is to set lookup lists to “Read All” and “Edit None.” If someone needs a drastic change to an existing lookup value, a request should go through the administrator.
John – Yes, this can be a real issue. It points to the fact that someone needs to be the “owner” of the lookup list. There is just too much going on to let everyone be able to change things.
I coined the term “The Theory of Lockdown” based upon Bob Mixon’s suggestions for how to allocate security and permissions. This is a good example of why things should be locked down and managed from a central location.
Mark
Mark,
You refer to Bob Mixon’s suggestions on security and permissions above. I sometimes have a problem making people understand “why” I need to lock down permissions so all change requests go to the “owner”. Can you point me to Bob’s suggestions please?
Mark,
A major issue I’m having with Lookup Lists used in Libraries is that WSS (3.0) refuses to save changes in these columns when in DataSheet Edit mode. I’d get “Wait for Pending Changes?”, then “Retry or Discard Changes”, and Retry never works. Today I’m getting a different error which may or may not be related to same issue, since I can save in Standard View just fine: “List Structure Has Changed – Another user has changed the structure of this list. All your pending changes to this list will be discarded. To retrieve the latest copy of the list, click Refresh.” (I’m the only one working on these libraries and I have not changed structure.)
Joan – The refusal to save changes is usually because a mandatory/required field is not filled out. Many times I see this is the title field, but the title field is not exposed to fillout, a pretty weird Catch-22. Take a look at the list structure and see what fields are required.
Regarding the structure change, can’t help there. Either someone has access that you don’t know about, the central administrator has changed something or the system is going weired. If I had to take my pick, I talk with the central admin first and have them help you track it done.
Hope that helps,
Mark
Mark, Thanks. I included the required field in the View I was using for DataSheet Edit and it worked. Also, I did not get the structure message today, so I must have been doing something somewhere yesterday on the site (which is a subsite of a top-level site under /sites managed path) to cause a glitch that cleared up. I am the Central Administrator and have not changed any settings there for a while.
I’ve found this post, and like the concept. In testing it however, I notice that my new lookup column isn’t available when editing a previously posted item… is this expected behaviour? (If I post net new, voila works like a charm.)
Teena – You’ll have to remove the existing column from each library and replace it with your new site column. Sorry for the bad news. — Mark
Hi – I am trying to setup a reference table (list) that has all values in the Title column. Then I added 4 additional columns:
IsSpecial? – Yes/No
IsSports? – Yes/No
Special – Calculated, if IsSpecial then Title
Sports – Calculated, if IsSports then Title
The problem I’m having is that if I use the Sports column in a Lookup field, it displays a blank row for every Title in that table that is not IsSports. Any way to remove the blanks in this situation? thanks in advance
I have setup a sharepoint list in custom datasheet view for multiple users to load data in it, while the erros doesn’t appear on my end (I’m the site admin) it does appear to some users “Another user has changed the structure of this list. All your pending changes will be discarded. To retrieve the latest copy of the list, click Refresh”
I have removed one calculated column, that had commas and the issue got resolve to some extent, as some users report not having the issue anymore. Still many others report the issue. I still have some calculated columns (though no columns in the formulas). Need help
Lock down the list to Contribute level. No one except yourself should have the permission level to change the structure of the list or library. Lock it down.
How do I lock it down, without impacting the rest of the folders or file that reside in the SharEPoint? I assume that the people that contribute in loading that must have “contribute” access only right?
Mike – This sounds like it goes deeper than contributor access. You’re talking about folders now, which is a completely different thing. In the list settings you can setup permissions for contributor access to the list, which will make it so that no one besides the owner of the list can change the structure. Then, go into the advanced settings of the list and turn off the ability for anyone to create folders.
OK, here’s what I’ve done so far
URL with no commas OK: http://enhanced1.sharepoint.hp.com/teams/gbspmocenter/gbsiepmo/Lists/GQ_Report%20TEST/Allitemsg.aspx (don’t know if “_” might be an issue)
Removed “unwanted access to my list”
Indexed columns to avoid resource consumption
Removed all calculated columns from a TEST view, still same result
I do have 7 columns with dropdown menu options (no commas on any) that users must use, though some of the options do inclide the ampersand (&) dash (-) and dot (.) I hope those aren’t causing the issue.
I created a SharePoint list as a datasheet view. I then went to SharePoint Designer 2007 and modified the Look & feel of the NewForm.aspx page. Up until here everything is work as usual, but when I go back to my datasheet view and click on “new” I get a message saying “invalid URL page” How do a link up the “new” button to the NewForm.aspx I just recently modified?
This is how I resolved it : we use 30 complex calculated columns & for one user she couldn’t update part of the list in the data access view. That record contained a column with one of its values contains “comma” !!! i felt sorry for the comma & had to remove it :( BAD SHAREPOINT
the user was then able to update (after deleting those records so he can start fresh)
btw, i don’t have MOSS SP2 installed …
I’m typing with one finger (broken hands) but I had to share this for this LOVELY & BEST SHAREPOINT WEBSITE EVER