How to use Sharepoint Lookup fields, Part 1.

August 6, 2008

Sharepoint Lookup fields can be powerful additions to any list and can emulate some of the behavoir of a relational database within a Sharepoint site.  This article will provide an overview of the Sharepoint lookup field, including how to add one to a list, create a site column that can be used from subsites, and implement extendable lookup lists and relations between lists.  Future articles will describe how to use these powerful fields in combination with list item event receivers to further implement relational behavoir between Sharepoint lists.

Phase 2 offers hosted Microsoft SharePoint and provides expert support with the subscription.

What is a SharePoint lookup field?  Let’s explore them by first setting up two lists that will be joined together with a look up field.  In this case we will look at using one list as an extensible lookup list.  This enables users with contribute permissions to add choices to a drop down control on the new and edit forms for the main list.  Otherwise the standard choice drop down field must be updated by a user with manage list permissions.

Starting with a Team site, we will create a lookup list for region, and add this field to the Contacts list.  First create a Contacts List then create a Custom List.

Create a contacts list.

Site Actions > Create > Contacts

Create a Custom list

Site Actions > Create > Custom List

For this demo the list names are Contacts and Region.

Creating a lookup field in one list is relatively easy.  Go the the Contacts list, List Settings, Create Column.  Select “Lookup”, and choose the Region list from the Get Information from Drop Down.  Leave the column Title, and we are done.

Now we can go to the Region list, and add some regions.  Then we can use those regions as we add our contacts.

Although this is a simple example, it sets us up to implement some kind of relataional database with Sharepoint lists.

Look for thenext blog in the series where I will show you how to set up a site column as a lookup field and use it from subsites as well.  Finnally in part three we will look at using Event handlers to maintain relationships and uniqueness of key fields.

�

by Adam Smith |
Comments |
How To, SharePoint
  • Adam,

    I have a slightly different question. I have built a custom contact list . I would like to use the Field Type of "Person or Group" to ensure that each person entered is validated against our Active Directory (AD) . When I get a user who is in AD, there is other information that I would like to store in my contact list from AD.

    So, in the contact list I created a field called "Name" and defined it as "Person or Group" and selected Name (with Presence) as the field to be displayed.

    When I add a new person to the list, I enter the persons name in the Name field which uses the People Finder. Once added, you see a hyperlink for each person in the Name field. If you click on the hyperlink, you can view the person's name, email, office phone, and several other fields that are coming from Active Directory.

    What I would like to do is once the Name is found in the People Finder, I would like to have other fields in my contact list to be populated with the data that you see when you click on the person's hyperlink.

    If I create my list using a separate Person or Group field type for each field that is available in the Person or Group field type (Account, E-Mail, Department, Job Title, ...), you have to enter the person's name in the People Finder, for each of these fields in order to get it to store/ display the information for the field you want displayed.

    I would like the user to only have to type the name once in the People Finder and display all the fields that you would see if you clicked on the hyperlink of the person's name.

    I have tried using a field type of Lookup and Calculate, but don't see the Name field (which has the Field Type of Person or Group) displayed.

    Is there some way to do this using WSS 3.0?
  • AAdam,

    I have a slightly different question. I have built a custom contact list . I would like to use the Field Type of "Person or Group" to ensure that each person entered is validated against our Active Directory (AD) . When I get a user who is in AD, there is other information that I would like to store in my contact list from AD.

    So, in the contact list I created a field called "Name" and defined it as "Person or Group" and selected Name (with Presence) as the field to be displayed.

    When I add a new person to the list, I enter the persons name in the Name field which uses the People Finder. Once added, you see a hyperlink for each person in the Name field. If you click on the hyperlink, you can view the person's name, email, office phone, and several other fields that are coming from Active Directory.

    What I would like to do is once the Name is found in the People Finder, I would like to have other fields in my contact list to be populated with the data that you see when you click on the person's hyperlink.

    If I create my list using a separate Person or Group field type for each field that is available in the Person or Group field type (Account, E-Mail, Department, Job Title, ...), you have to enter the person's name in the People Finder, for each of these fields in order to get it to store/ display the information for the field you want displayed.

    I would like the user to only have to type the name once in the People Finder and display all the fields that you would see if you clicked on the hyperlink of the person's name.

    I have tried using a field type of Lookup and Calculate, but don't see the Name field (which has the Field Type of Person or Group) displayed.

    Is there some way to do this using WSS 3.0?

    Thanks,
    Larry
  • Hi,
    i would like to offer tutorial on how to create basic sharepoint lookup field within visual studio. here is a tutorial with screenshots and comments. it could help to someone.

    http://sharepoint-anthony.blogspot.com/ - Sharepoint Lookup Field
  • adamontherun
    Radovan, we'd love to take a look at that and possibly publish it. Can you please send me your contact information to smitha @ phase2

    thanks!
  • kryptonite2010
    i need to think of ideas how to create comment feature, such as this, can someone please give me some tips
  • wmulcahy
    Can I take this discussion out a bit further? When setting up the lookup column, choose the option to make this a required field. If there is less than 21 entries in the list that feeds the lookup column, when you add a new entry to the list, the lookup field defaults to the first value in the drop down (instead of a blank). So in reality - it doesn't actually enforce required entry. Instead, it sets up a situation where a user is likely to just save the default. Now the stramge part is that, if you have more than 21 entries in the list that feeds the lookup column - then it actually works the way i would expect it to. When you add a new entry to the list, the lookup column is blank, and you are forced to actually choose a value in order to save and exit. Is this a bug or am I doing something wrong? I can't find an explanation anywhere.
  • ashokjingar
    Hi, I liked your posting about Lookup fields. I wonder if you can point me in the right direction regarding the following please?

    Let's say we have a list with 3 columns. (e.g. Region, Region Code, and Contact). So we would have actual names of different regions in Column 1; In column 2, these regions can be in Region Codes 01,02 or 03. And finally, we would simply have the appropriate contact names of people who work at these regions.

    My query is, - I know how to retrieve the values from the 1st Column (and populate a dropdownlist accordingly), but how do I retrieve the corresponding values from the 2nd and 3rd Column based one the 1st olumn value that I have selected?

    Any help would be greatly appreciated.
  • wmulcahy
    I'd love to get the answer to this one too!
  • ashokjingar
    Fortunately, I have found the answer, but it will involved coding in Visual Studio.

    So the scenario again: Let's say we have a SharePoint List called 'Our_Regions', that has the first column, RegionName, where we list all our regions. (In coding it will always have to be referred to as 'Title' by the way as it's the first column and that's the name SharePoint gives it even if you change it!)

    For our second column, we have RegionCode, which can be anything, but let's say we have codes 01,02,03, etc. to identify our regions.

    So, what we want is a dropdownlist called dropRegionName to be populated with region names from our first column in the Sharepoint list. Let's say we also have a label control called lblDisplayRegionCode. Depending on which region we select, lblDisplayRegionCode displays the corresponding region code from our SharePoint list, e.g. 01, 02, 03, etc. This is achieved by the following code, which must be within the Page_Load event:

    protected void Page_Load(object sender, EventArgs e)
    {
    //This part populates the dropdownlist with values from a SharePoint list called Our_Regions
    if (!Page.IsPostBack)
    {
    using (SPSite site = new SPSite("http://mycompany/sitename/"))
    {
    using (SPWeb web = site.OpenWeb())
    {
    //Populate my drop down list (dropRegionName) from SharePoint List
    SPList listRegions = web.Lists["Our_Regions"];// This is the name of your list in quotes
    dropRegionName.DataSource = listRegions.Items;
    dropRegionName.DataValueField = "Title"; // 1st column = always called "Title"
    dropRegionName.DataTextField = "Title";
    dropRegionName.DataBind();
    }
    }
    }

    //For this next bit, we have a label called lblDisplayRegionCode. Whatever we select as our region in dropRegionName,
    //the appropriate Region Code is displayed in lblDisplayRegionCode. The code below performs this query for us.

    SPListItemCollection itemCol = null;
    SPSite site2 = new SPSite("http://mycompany/sitename/"); //Point at the same site as before
    SPWeb web2 = site2.OpenWeb();
    SPList list2 = web2.Lists["Corp_Regions"];//Point at the same list as before
    SPQuery qryRegionCodeLookup = new SPQuery();
    qryRegionCodeLookup.Query = "<where><eq><fieldref name="Title"><value type="Text">" + dropRegionName.SelectedItem.Text + "</value></fieldref></eq></where>";
    qryRegionCodeLookup.ViewFields = "<fieldref name="RegionCode">";

    try
    {
    itemCol = list2.GetItems(qryRegionCodeLookup);
    foreach (SPListItem item in itemCol)
    {
    lblDisplayRegionCode.Text = item["RegionCode"].ToString();
    }
    }
    catch (NullReferenceException)
    {
    lblDisplayRegionCode.Text = "Some Kind of Error!";
    }

    }

    - That's it - hope it's of any use to anyone!



    </fieldref>
blog comments powered by Disqus