Simulate Parent / Child relationship in SharePoint 2007 with Folders & Content Types

One can create Parent / Child (A.K.A. Master / Detail) relationships in SharePoint 2007 by using lookups to reference a parent list from a child list. I will discuss another way of creating a Parent / Child relationship by adding a content type inside a folder.
We will create a parent / child relationship between company and employees. Here are our sample entities:

Company Parent (CompanyId, CompanyName)

Employee Child (Name, EmployeeId, Branch)
The strategy will be to make the "Company Parent" entity a folder which contains "Employee Child" items. To accomplish this we will create two new content types for each entity.

The "Company Parent" content type:

1) Site Actions ==> Site Settings ==> Site Content Types
2) Create

• Name: Company Parent

• Select parent content type from: Folder Content Types

• Parent Content Type: Folder

• Existing group: Custom Content Types

• Click OK
3) Click on "Add from new site column" on the "Site Content Type: …" page under column.
• Column name: CompanyId of type Number

• Click OK

NOTE: We will reuse the "Title" field to be the CompanyName.

The "Employee Child" content type:

1) Site Actions ==> Site Settings ==> Site Content Types

2) Create
• Name: Employee Child

• Select parent content type from: List Content Types

• Parent Content Type: Item

• Existing group: Custom Content Types

• Click OK
3) Click on "Add from new site column" on the "Site Content Type: …" page under column.
• Column name: EmployeeId of type Number

• Click OK
4) Click on "Add from new site column" on the "Site Content Type: …" page under column.

• Column name: Branch of type "Single line of text"

• Click OK

NOTE: We will reuse the "Title" field to be the EmployeeName.
Now we can go about our normal business of creating a custom list and using our two newly created content types "Company Parent" and "Employee Child".
1) Site Settings ==> Create

2) Click on "Custom List" under the "Custom Lists" column.
• Name: CompanyEmployees

• Click OK
3) Settings ==-> List Settings

4) Click on "Advanced Settings" under the "General Settings" column

5) At the very top, change Allow management of content types?" to Yes.

6) Click on OK

7) Under "Content Types" click on "Add from existing site content types".

8) On the "Add Content Types:" page
• Select site content types from: Custom Content Types

• Add "Company Parent" and "Employee Child" content types.

• Click on OK
Let’s add some dummy data.

1. Click on the "CompanyEmployees" list

2. Click on the little arrow beside New. You should see this:

3. Select "Company Parent"

4. Add the following bogus company data:
• Name: ACME Corporation; CompanyId: 123

• Name: Bogus Enterprises: CompanyId: 456
5. Add bogus company employees to "ACME Corporation"
• Click on "ACME Corporation". This causes the folder to open.

• Add the following employees to the ACME Corporation by clicking on New ==-> Employee Child:

• Title: Jane Doe; EmployeeId: 111; Branch: Downtown

• Title: James White; EmployeeId: 222; Branch: Airport

• Click on "Bogus Enterprises". This causes the folder to open.

• Add the following employees to Bogus Enterprises by clicking on New ==-> Employee Child:

• Title: Judy Black; EmployeeId: 333; Branch: Main Street

• Title: Brandon Wiley; EmployeeId: 333; Branch: Riverside

• Add another employee to ACME:

This concludes configuration and data entry. Let us now use the SharePoint 2007 Object Model to get at this data.
using System;

using System.Collections.Generic;

using System.Text;

using Microsoft.SharePoint;

public class CompanyEmployees {

static void Main(string[] args) {

SPSite site = new SPSite("http://srvr/");

SPWeb web = site.AllWebs["/"];

SPList list = web.Lists["CompanyEmployees"];

foreach (SPListItem order in list.Folders) {

Console.WriteLine("Company ID : {0}", order["CompanyId"]);

Console.WriteLine("Company Name : {0}", order["Title"]);

SPQuery empItemsQuery = new SPQuery();

empItemsQuery.Folder = order.Folder;

SPListItemCollection empItems = list.GetItems(empItemsQuery);

foreach (SPListItem empItem in empItems) {

Console.WriteLine("\tEmployee ID {0}: {1} {2}",

empItem["EmployeeId"],

empItem["Title"],

empItem["Branch"]);

}

}

}

}

The above application will produce the following result:
Company ID : 123

Company Name : ACME Corporation

Employee ID 111: Jane Doe Downtown

Employee ID 222: James White Airport

Company ID : 456

Company Name : Bogus Enterprises

Employee ID 333: Judy Black Main Street

Employee ID 444: Brandon Wiley Riverside