Aug

06

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.

  • Jaime Carrasco

    Hello,

    Great article, thank you.

    I have a lookup field that has 10 items in it. I want to make it a required field, but as soon as I do that I lose the ‘(NONE)’ and the default value becomes the first item on the list. This can be a problem if the user decides to ignore the field and leave the default value as is.

    Is there a workaround that?

    Thanks for your help

    Jaime

  • http://www.phase2int.com James

    Right, Jaime.
    I understand exactly what you mean.

    I suppose a simple work around would be to add a ‘(None)’ entry to your lookup list, but I am not sure how you would ensure that it would be first in the list, ie the default.

    A quick test indicates that the sort order is alphabetic, so ‘All’, would always preceed ‘None’ in the selection. But ‘(None)’ preceeds ‘All’ as I guess the parantheses are less than A so to speak.

    Hope that helps.

    James

  • Pingback: » How to use Sharepoint Lookup fields, Part 2.

  • http://www.carolinas.org Charles Ashford

    Is there a way to connect the lookup field to an external SQL database table?

  • Joe

    A legitimate response to Jamie’s question would be awesome. We are also experiencing the same issue.

  • Tim

    Hi,

    I have about 12 items in my lookup field and for some reason, our PM wants the display of these items to be in a certain order. Is there a way to sort the items from this lookup field manually?

    If this is custom code, can some give me some advice on how to proceed with building a custom site column?

    Thank you.

    Sincerely,
    Tim

  • http://www.phase2.com James McDowell

    Sorry for the delay in response.

    Charles, I think the only way to do that would be to create your own Lookup Field. Creating a Custom Field involves Creating a SharePoint Feature, UserControls for forms and an Class Library to handle any custom logic, ie selecting and connectiong to the SQL Database and specfic column or query for the the lookup. This is a subject for another post, which I hope to write within the next couple of weeks.

    Joe, as far as a Legitimate answer, I do believe I suggested a decent workaround. Again, to do this elegantly without the extraneous (none) entry in the lookup list would require creating your own SPField, hopefully extending the existing lookup field type. However, I have yet to delve into this area of SharePoint development.

    Tim, A quick test indicates that the default behavior is to alphabetically sort the selections. A simple if in-elegant workaround would be to number the lookup column’s values, so instead of a list of Values it would be a list of A) ValueA , B) ValueB, or with Numbers like 1) Value1, 2) Value 2, with the numbers or letters then controling the sort order.

    Otherwise we have to delve into creating a custom field. Sounds like fun, so I will explore that and blog about it when I have something.

    Thanks for your continued interest!

    James

  • Pingback: Performance, Scalabilty and Architecture - Java and .NET Application Performance Management (dynaTrace Blog) » SharePoint: Lookup value Performance

  • Willa

    Hi James,
    It’s so great to have your article available, so I could start using Lookup solution, just have 2 questions here,
    if we use look up field, it that possible to select more than one lookup field?
    How do we create a view based on the lookup field?
    Thank you.
    Willa

  • 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!

  • 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.

  • kryptonite2010

    i need to think of ideas how to create comment feature, such as this, can someone please give me some tips

  • 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></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!

  • http://www.facebook.com/people/Radovan-Skulsky/1399968712 Radovan Skulsky

    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!

  • 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!

  • http://twitter.com/lawmsw93 Larry

    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

  • http://twitter.com/lawmsw93 Larry

    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?

  • http://www.fewlines4biju.com/ Bijay

    Thanks
    Bijay (MCTS MOSS 2007)

    SharePoint 2010 Blogs – http://www.fewlines4biju.com/s
    MOSS 2007- http://www.fewlines4biju.com/s
    Personal Blogs – http://www.fewlines4biju.com/s
    Jobs- http://www.fewlines4biju.com/s
    Interview Questions – http://www.fewlines4biju.com/s
    C#.Net: http://www.fewlines4biju.com/s
    Asp.Net – http://www.fewlines4biju.com/s

  • Khushi Shaikh

    I want to query child list on parent key and fetch child fields only. I want to query customer on region='North America' to fetch cutomer names. How do I mention Lookup column of relational list in query.Query

  • moggyman11

    Hi, I don't have visual studio – can this code be added via Sharepoint Designer? If so, HOW???

    Thanks

  • ashokjingar

    Hi, I'm afraid you cannot do this using SharePoint Designer, but you can use Visual Web Developer (which is a cut-down free version of Visual Studio).

    Hope that helps.
    AshokJingar

  • ashokjingar

    Hi, I'm afraid you cannot do this using SharePoint Designer, but you can use Visual Web Developer (which is a cut-down free version of Visual Studio).

    Hope that helps.
    AshokJingar

  • Dave Godlewski

    My biggest problem now is that when I display the value that was “looked up” from a keyword list, that looked up value is click-able and takes that user to the keyword list, which I don't want my users to even know about.  How can I get this value to not be click-able?

  • Bob in Boston

    I have the same problem and came across your comment looking for a solution.  If you found something, please reply and I'll do the same.  :-)   I don't want my users taken to the source of the lookup when they click the link – I would prefer the value doesn't appear as a link at all except for as a dropdown in the new/edit dialogs.

  • http://post404.com/ Randall “texrat” Arnold

    I added a new column to my master site that uses a lookup against a local list.  Everythign is fine there.  But when I try to use that column on a subsite, ALL column properties are blank and the “List or document library” drop down is greyed-out.

    Any ideas what could be wrong?

  • johndurbinn

    nigger

  • Angelos

    The only way to achieve this, without coding in Visual Studio, is by designing your form within InfoPath, which allows you to filter your data sources as you select different values within the first drop down of Regions.

    I recommend you search for “infopath 2010 cascading drop down list” or 2013 and you will find what you are looking for.