Cross lookup in Sharepoint lists

In SharePoint 2010, you can create relationships between lists by using a combination of unique columns, lookup columns, and relationships (cascade and restrict delete), all of which enhance your ability to create more sophisticated business solutions and help preserve the integrity of your data.

In the following I will show how to create a unique lookup column that can you use in other lists for lookup.

How to create a unique and lookup column

Create a unique column

Note:

Before we start we have to consider a couple scenarios where a unique column cannot be defined:

  1. You cannot break inheritance from the list to the list-item if the list contains a unique column.
  2. The other way round :  You cannot create a unique column in a list where inheritance has beem broken
  3. You cannot either change an existing column to be unique if inheritance has been broken.
  4. If your list has content approval enabled you won’t be able to create a unique column.
  5. You cannot copy a file to a liste with Open with Explorer command if you have a unique column on that list

Now that it’s said let’s get going…

  1. Navigate to the list where you want to create the column
  2. In the ribbon, click the List tab then in the Manage Views group click Add column
  3. In the column name, type a name for the column.
  4. Under the type of information in this column choose one of the followings
    Single line of text
    Multiple lines of text
    Choice Number
    Currency
    Date and Time
    Yes/No
    Lookup (single value)
    Person or Group (single value)
  5. In the Additional Column Settings section, under Enforce unique values click Yes.

Note : If the Enforce unique values section isn’t displayed you have chosen a type that doesn’t support uniqueness.

Now that the column is unique there is at least one thing that you cannot do.

  1. You cannot remove the index of a unique column unless you change the column from a unique one to one that allows duplicate values.

Note : If you want to change the data type of unique column you must do the following:

  1. Change the column to allow duplicate values
  2. Remove the index
  3. Change the column to another supported data type.
  4. Make the column unique.

Create a lookup column

  1. Navigate to the site where you want to create a lookup column
  2. In the ribbon, click the List tab then in the Manage Views group click Add column
  3. In the column name, type a name for the column.
  4. Under the type of information in this column choose Lookup
  5. In the Get information from box, select a target list (the one with your unique column)
  6. In the In this column box, select a primary column
  7. Under Add a column to show each of these additional fields click the checkbox for each secondary column you want to add. Only columns with supported data types are listed.
  8. If you want to create an enforced relationship, select Enforce relationship behavior and then choose Restrict Delete or Cascade Delete
  9. You can also choose if you want the column to be displayed in the default view.

Now you’re done…you’ve made a unique column and are able to display additional fields via the key lookup.

In another post I will treat the resource threshold of lists with large number of items and the pros and cons of Enforced relationships.

Happy sharepointing !

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: