Using Secondary SharePoint Lists as Field Lookups

By:   |   Comments   |   Related: > Sharepoint Design


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.

1 show choice field

Here is what the library looks like.

2 library before change

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.

4 Create New Custom List with choices

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

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

1 Create Column Options

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.

2 New Lookup Field

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.

3 Updated Field

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 for great information about Microsoft SQL Server.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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.

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

View all my tips

Comments For This Article

get free sql tips
agree to terms