The question came up this week of whether it's preferable to use a lookup or choice field for storing data within a site column.
I don't have a definitive recommendation, but I can offer some pros and cons, gleaned from various sources:
-
A list lookup will let you delegate management of the master list to an end user simply by giving them elevated rights to the list; to manage a site column you must be a site collection administrator.
-
A list lookup will allow approvals for selection choice changes, and will allow audit trails (great point by Nadeem as a comment to this post). There is no tracking or approval workflow for changing the choices in a choice column.
-
A list lookup can't be used as part of a formula in a calculated field. (from the Stump the Panel forum on End User SharePoint)
-
A list lookup can't be accessed in "edit in datasheet" mode, which means that a look up value should probably not be a required field or there’s no possibility of a “bulk edit.” (from Sue Hanley)
-
A choice field won't maintain referential integrity if changed, and a lookup will (?) (from Real World SharePoint Experiences by John Powell - I haven't tested this.)
-
A list lookup will render the data in the field as a hyperlink, which users could click through to see the master list record (this may be good, bad, or neutral depending on your data) (from this thread on SharePoint University)
-
A list lookup lets you add additional metadata fields about the list item (from Mark Miller on this thread on End User SharePoint)
-
If looking up to a document library, the document name field is not available for lookup (from a comment on this thread on End User SharePoint)
-
You can't filter or sort a lookup field so that the column displays the data in your desired order. It orders by ID, so your choices can't display alphabetically. One user on this thread on End User SharePoint (comment #57) says it is possible to install a custom field type "view filtered lookup" which will do this, but I have not tested this. With a choice field in a site column, you can alphabetize the data or sort it whichever way you like.
To Note:
I checked the SharePoint Server 2007 Best Practices book by Ben Curry and Bill English, and it did not contain any additional information on the subject.
This question was posted to the Experts Exchange, but the approved answer bascially describes how to implement either of the two options, not much about potential gotchas. Lame.
If you have any additional pros or cons on this subject, please leave a comment!
