In a MOSS-Development workshop I held this week I was asked how to access the data of lists joined with Lookups. Thinking that the answer to this question might be interesting to many people, I decided to write a post about that issue.
Let's start with something I think you should be familiar with – a database diagram with one 1:1 relationship and one 1:N relationship
In SharePoint this would be implemented using the following List – quick and dirty I am using the Title Column in the Person List for the Person Name – I am going to write a post later on how to hide the title column in a List Definition.
The 1:N Relationship in Person would be modeled as displayed below.
SharePoint provides a SPFieldLookupValue and SPFieldLookupValueCollection for accessing the data of 1:1 and 1:N relationships. You can use the LookupValue Property to access the actual data – the LookupID Property would be the ID of the ListItem in the referenced List.
static void Main(string[] args)
{
SPSite col = new SPSite("http://chiron");
SPWeb web = col.AllWebs["Lookups"];
SPList list = web.Lists["Person"];
foreach (SPListItem item in list.Items)
SPFieldLookupValue job = new SPFieldLookupValue(item["Job"].ToString());
string skills = GetSkills(item);
Console.WriteLine("{0} works as a {1} with skills {2}",item["Title"],job.LookupValue,skills);
}
static string GetSkills(SPListItem Item)
string result = string.Empty;
SPFieldLookupValueCollection skills = new SPFieldLookupValueCollection(Item["Skills"].ToString());
for (int i = 0; i < skills.Count; i++)
SPFieldLookupValue skill = skills[i];
result += skill.LookupValue + ";";
return result;
The resulting output would be
You can download the sample including a site definition containing the list and sample data.
Sun is shining in Vienna – That's it for today
Alexander Pajer