Skip Ribbon Commands
Skip to main content

Home > Creating Parent Child Relationships in SharePoint 2010

There were a lot of attempts to work with relational data in SharePoint 2007 most of which resulted in moderate success at best. The easiest approach was just to use de-normalized data and rely on the fact that most SharePoint "applications" were quick-turn around affairs that didn't have long life spans. The benefits of rapid development outweighed rigor.

In SharePoint 2010, we can create real parent-child relationships between lists and maintain referential integrity. That's a techie way of saying SharePoint can now create applications without bunny ears. But before  you get too excited, remember that this is SharePoint so it's not that straight-forward: creating the relationships is easy, relating the data as it is entered requires some tricks.

Let's take a look. Here's the world's simplest Contact Relationship Management (CRM) application: it has a Clients list and a Note list to log contacts with each client. Each client can have mulitple Notes so there is a one-to-many relationship between Clients and Notes as shown in Figure 1.

Figure 1. The Simple CRM data diagram

fig01.PNG

Creating the Relationships

To build the relationships shown in Figure 1, follow these steps:

  1. Create a new SharePoint site named Simple CRM based on the Blank Site template. We always build applications in their own sub site so that their security can be managed easily and so that they can be reused as a site template if needed.
  2. Create a new list named Clients based on the Contacts list template.
  3. Create a new list named Notes based on the Custom list template.
  4. In the Notes list, create a new column named ClientID using the settings shown in Table 1.
  5. In the Notes list create a new column named Note using the settings shown in Table 2.

Table 1. The Notes list's ClientID column settings

​Field ​Setting
​Name ​ClientID
​Type ​Lookup
​Description ​This is the foriegn key used to associated the Notes list with the Clients list.
​Require... ​No
​Enforce unique... ​No
​Get information from: ​Clients
​In this column: ​ID
​Add a column to show... ​Last Name (linked to item)
​ ​First Name
​ ​E-mall Address
​ Comany
​ ​Job Title
​ ​Business Phone
​ ​Mobile Number
​ ​City
​ ​State/Province
​Add to default view ​Checked
​Enforce relationship... ​Cascade delete

 Table 2. The Notes list's Note column settings

​Field ​Setting
​Name ​Note
​Type ​Multiple lines of text
​Description ​Log information about this contact.
​Require... ​Yes
​Number of lines... ​12
​Specify the type... ​Enhanced rich text...
Append changes... No
​Add to default view ​Checked

 

Some explanation of what you just did:

  • The Add a column to show... settings in Table 1 create projected fields in the Notes list. That means you can see those Client fields from views of the Notes list. It's an easy way to create combined views of Clients and Notes without having to create a DataView web part.
  • The Enforce relationship... settings in Table 1 enforce referential integrity. That means if you delete a Client, all of that client's Notes are also deleted, so Notes don't become orphaned in the application. If you restore a deleted Client from the recycle bin, all of that Client's Notes are also restored. Very cool.

At this point, the data schema for the Simple CRM is done, but the user interface (how you add, edit, and view records) is incomplete.

Click here to see how to build that part.

References