Lookup Columns in SharePoint 2010
Maintaining all your options in a Choice field can be cumbersome and prone to error. SharePoint 2010 uses a similar model to relational databases by separating the lookup information from the transaction list.
Think of all the lookup data that could be maintained in separate lists. For example, computer hardware inventory lookup lists could include hardware type, maintenance contract, and department location. These lists can be maintained independently of the transaction list — the inventory itself.
For example, you can create a Department custom list with a single field — Title — and populate it with the names of departments in your organization. Build the Volunteer Projects transaction list (where you track employees working on volunteer projects), and Departments is a column in this list.
Rather than build a Choice field, use the Lookup data type to connect to your Departments list and use the Title field as data for the Department column in the Volunteer Projects list.
You can also add other columns from the lookup list to the drop-down list to help users select the proper choice. When a user selects the value from the drop-down list, values for the additional columns also display.
Here is a scenario that uses a lookup column to display a customer’s sales territory. The customer’s name and sales territory are stored in one list and displayed in another list using a lookup column.
For users familiar with databases and referential integrity, SharePoint 2010 adds options to support this implementation. Lookup columns can also be used to create a chain of joined lists that can be used to query and display values from additional columns.