Using Secondary SharePoint Lists as Field Lookups

By: Rob Fisch   |   Updated: 2010-02-09   |   Comments   |   Related: > Sharepoint Design

Problem

When creating business solutions with SharePoint lists, one of the field types is the 'choice' column. This is handy to offer users a static list of values. This can be important to help users not have to lookup categorical or other frequently used information. It can also help when building custom views to have consistently chosen (and correctly spelled) data. However, I continually get requests to modify the list of choices. Can I give a few select users the power to maintain their own choices without giving them control over the rest of the list structure?

Solution

Yes! The solution is to use a secondary SharePoint list as a source for the list of values for the lookup, and then train a few 'power users' on how to maintain the list.

The following example starts with showing a 'choice' column in a document library called "Document Category". The choices are shown below.

Here is what the library looks like.

To enable a lookup, we create a new SharePoint Custom List called "Document Category". Then enter the choices as multiple items in the list. Leaving the default column "Title", we simply enter the desired options.

Next add a new column in the original library by clicking "Settings", then "Create Column".

For column type, choose "Lookup". Choose the new Custom List created for the Categories (or whatever you have), and choose the proper source field. In this case, I didn't create any custom fields, and left the default of "Title" for the entries.

Now back in the original library, you can see I have two dropdowns. The first one (showing "Discography") is the original 'choice' field. The one on the bottom is the new lookup from the Custom List. They look and behave the same from this screen.

Now we can see the old and new columns side-by-side in the library. The first line is the one I edited. You will need to add data to the new column. Then you can hide the original or delete it entirely.

If you were converting an existing library or list, to finish this up, you would copy the data from the old column to the new column and then delete the old column.

(Tip: If you want to use datasheet view to streamline copying and pasting your data, you will need to temporarily turn off any fields that are "required" and, if it's on, turn off the requirement to check out a document. Once the data is copied you can turn those options back on.)

Next Steps

• Check out MSSQLTips.com for great information about Microsoft SQL Server.

About the author

Rob Fisch has worked with SQL Server since version 6.5 as a dba, developer, report writer and data warehouse designer.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips