Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


Using Secondary SharePoint Lists as Field Lookups

By:   |   Last Updated: 2010-02-09   |   Comments   |   Related Tips: > Sharepoint


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?


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.

Last Updated: 2010-02-09

get scripts

next tip button

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

View all my tips

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Learn more about SQL Server tools