One of the things I was most excited to see in SharePoint 2010 was the introduction of true relational lists. (Am I betraying my database background here or what?)
Relational Lists seems like a
no-brainer, since SharePoint's data is resident in a SQL relational
database, right? Well, true though that is, the data for individual
lists is NOT maintained in distinct tables. Instead they are grouped
together into an "AllLists" table or an "AllLibraries" table. Weird huh?
The lookup field is not new to SharePoint 2010, but it now has
capabilities to enforce relationship behavior between two lists that
share common values. That means we can build joins between lists, we
can use "Projected Fields" (fields from the parent table can be shown
read-only in a view of a child table), and we can enforce relational
integrity between our tables (you can't delete a parent row if it is
used by a child row) .
Sounds great, right? So here's how you go about the business of building relationships between SharePoint lists.
1. Create a list to hold "parent" records.
In my example this will be a "Contacts" list.
2. Open the List Settings.
In the list of columns, ensure that whichever column you want to use for selecting
records in the child table is required and unique. This is not
required, but will help to ensure that record selection is done
2. Add data to this "parent" list.
3. Create a "child" list.
my example I will create a new Custom List called "Phone Conversations"
that will be used for documenting phone calls with contacts. In my
example I will
a. Rename "title" to "subject"
b. Create a new date/time field to store the Call Date
c. Create a new multiple-line text field to store Call Notes
d. Create a new LOOKUP field called Contact. Here are the properties as I set them to support a Relational Lookup:
"In this column" field determines what the person filling out the form
is going to be able to see when selecting a contact from the contacts
list. The checkboxes underneath will define the other columns
that can be displayed when creating a view from this list or when
viewing the properties of any particular list in this list.
The "Enforce relationship behavior" option defines a rule that can be summed up as this:
"No Orphans allowed!"
rule is often refered to as a "referential integrity" rule or
constraint upon the data. It means that in our example, we are not
allowed to delete a contact if that particular contact is in use by a phone conversation record. More on that in a moment.
5. Final Step - add some "child" list data.
Here is a new record being created in the phone conversations list calling for information in the contacts list:
And here is the record after being added:
you can see, we have several columns of "joined" data. Information that
comes from the contacts table can be displayed for related
records again and again without any new data entry.
Now about that
"Enforce Relationship Behavior" business. Even with the setting
configured for "Restrict Delete" I can delete phone conversations and
any unused contacts with impunity. However, this is the dialog box I will see if I try and delete a contact that is in use:
if I were to go back to the properties of the Lookup field in the Phone
Conversations list called "Contact" and change the referential
integrity option to "Cascade Delete" I would not get this message.
Instead I WILL be able to delete parent records... by also deleting all
the related children! Here is the message you would see if deleting a
contact that is attached to a phone conversation:
clicking OK the parent record deletes as usual, and both the contact
and the related phone conversations are moved to the recycle bin. What
if you want them back? You restore the Parent from the recycle bin - you
won't be able to see the "cascade-deleted" records - but the icons in
the recycle bin can help:
there you have it! Enjoy your truly connected lists with referential
integrity within SharePoint. I'm curious - what purposes would you have
for a SharePoint relational list? Please comment below!