Are Your Lookup Lists Involved in a Relationship?

SharePoint 2010 adds the ability to define relationships between two SharePoint lists in which one list serves as a lookup to the other list. This capability existed in prior versions of SharePoint. However the lack of enforced relationships allowed users to modify the contents of the lookup list without considering whether individual lookup values were used by the referencing table. SharePoint 2010 corrects this missing feature by allowing the user to either restrict or cascade updates to the referencing list.

What does that mean? Suppose I have a list of Projects that my organization is working on. I want to use this project list as a lookup column source for a project field in a second list, such as a Lessons Learned list. In the Lessons Learned list, I define a column named Projects using the lookup column type. In the Additional Column Settings section, I edit the Get Information From field to point to Projects and use the Project Name column as the source for the In This Column field. This definition establishes the link as shown in the figure below.

Note that once I link the Project list to another list, I can also bring along any of the other fields in that list such as the Project Manager field to be displayed with the data in the Lessons Learned list. The emphasis on displayed comes from the fact that while the Lessons Learned list displays the project manager’s name associated with the selected project, I cannot change that name in the referencing list. I can only change the Project Manager’s name in the Projects list. Furthermore, the column name associated with the project manager’s name will be the concatenation of the list and column name of the lookup, Referenced Projects:Project Name in this case.

If I do not do anything else when defining the lookup, it would be possible for someone to come into the Projects list and change the name of a project that is already linked to one or more lessons learned or even to delete the project from the Projects list. Anyone familiar with referential integrity would refer to this action as creating an orphaned reference in the lessons learned table since the referenced project would no long exist.

In SharePoint 2010, a Relationship section was added to allow the list designer to enforce relationship behavior. The designer has three possible options. First, I can choose to ignore the enforcement of any relationship. Sometimes, this is the correct solution especially if I do not want to change the historical value of the lookup over time that could change the lookup values. Another possibility is to use the Restrict Delete option. This option prevents changes to the values in the lookup list that have already been used at least once in the referencing list. In other words, it would prevent changing the project name if that project had at least one lessons learned record. Finally, the third option is Cascade Delete. With this option, if I change the project name in the lookup list, SharePoint cascades that change to all lessons learned records automatically. Similarly, if I delete a project from the lookup list, it deletes all lessons learned records for that project.

With either relationship enforcement options, I must index the column before the referential connection can be created. This is similar to the behavior of referential fields in a database which require the linking fields to be indexed. The difference is that only the linking field in the referencing list must be indexed within SharePoint. If that field does not contain an index, SharePoint prompts for one using the following dialog.

Clicking OK builds the index and creates the relationship. I can review the relationships defined for a list by selecting List Settings from the List ribbon and scrolling to the last link in the Columns section to display the Indexed columns.

SharePoint then tells me the number of indices the current list has and the fields associated with each one.

In a similar fashion, I could add referential lookup columns to the Lessons Learned table for up to 20 different fields in the table. Let’s suppose I have three referential columns and therefore, three indexed columns as shown here.

Note that in order to review the type of referential link each field uses, I must return to the individual lookup table and review the properties of the lookup column. If I change the referential enforcement option, that change applies only to new or edited records going forward, not existing records that are not edited.

For the purposed of this example, I defined the Project lookup to be a Cascade Delete and the Status and Lesson Type lookups to be a Restrict Delete.

Let’s now add our first Lesson Learned as shown in the following figure.

For the purposes of this example, suppose I add a few more records to the Lessons Learned list so that the first few records look like the following figure.

Now suppose that I go into the Lesson Type list and decide that I want to delete the Coding option from the list. However, I already used the Coding lesson type in at least one Lesson Learned item (see above).

Upon clicking Delete Item from the options dropdown menu, I get a message first asking me if I am really sure that I want to do this. (Personally, I think SharePoint should check the referential option first and if Restrict Delete is set as it is here, to skip over this question.)

After confirming that I do want to delete this item from the lookup table, SharePoint tells me that it cannot delete the item because it is used in the Lessons Learned list.

(Note: Although the option Restrict Delete is in effect, changes to the lookup value will always cascade down to the referencing list.)

Next, let’s look at the Project lookup list. In the Lessons Learned list, I defined this relationship as a Cascade Delete. Next I go into the Project list and delete Project 1 at this point as shown in the following figure.

I would first get a warning message such as the one shown below that tells me that any related items in the Lessons Learned list will also be sent to the recycle bin and would ask me if I was really sure I wanted to do this.

If I click the OK button, not only does the project get deleted from the Project list, but SharePoint also deletes all of the lessons learned related to that project as you can see from this last figure of the Lessons Learned list which no longer has any lessons from Project 1.

That’s all for now.

C’ya next time.

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.