SharePoint Internals – Hristo Pavlov’s Blog

15 May, 2008

Lookup Fields to the Same List – Defined as a Site Column

Filed under: SharePoint — Tags: , , , , , — hristopavlov @ 1:41 pm

Defining lookup fields that lookup in the same list can be quite useful. They are also easy to define when you add a field at the list level. Just select the type for the field to be “Lookup” and then for the “Get information from” list choose the same list as the one you are going to add the field into.

However you can’t do that if you want to define your field as a site column because the field is not added to any list at the time it is being defined and therefore your can’t lookup into a list that doesn’t exist yet. It would be useful though if somehow you could use some sort of “late binding” and choose the list later but when you are creating the site column from the UI you will have to pick up a concrete lookup list.

But there is antoher way. You indeed can define a reusable “late bindable” lookup field as a site column which will lookup into the same list as the list it is added to. How to achieve this is revealed if you look at the SPFieldLookup.LookupList property getter in Reflector:

public string get_LookupList()

{

    string fieldAttributeValue = base.GetFieldAttributeValue(“List”);

    if (fieldAttributeValue == “Self”)

    {

        while (base.ParentList != null)

        {

            return base.ParentList.ID.ToString(“B”);

        }

    }

    return fieldAttributeValue;

}

If you have defined lookup site columns using schema xml and SharePoint features you will know that the List attribute should be the ID of the list. What the code above tells you is that if you use the string “Self” as the value of the List attribute then this will indeed refer to the list itself.

So the solution is to deploy your field as a SharePoint feature and set its List attribute to “Self”. For example this is how SharePoint defines the “Related Issues” site column:

<Field ID={875FAB27-6E95-463b-A4A6-82544F1027FB}

       Name=RelatedIssues

       Group=$Resources:Extended_Columns

       Type=LookupMulti

       Mult=TRUE

       DisplayName=$Resources:core,Related_Issues;

       SourceID=http://schemas.microsoft.com/sharepoint/v3

       StaticName=RelatedIssues

       PrependId=TRUE

       List=Self

       ShowField=Title>

Interestingly there are two other special lookup lists, other than “Self”, which are “UserInfo” and “Docs”. “UserInfo” refers to the special peoples list SPWeb.SiteUserInfoList whlie “Docs” refers to the Docs table in SharePoint and allows you to lookup any of the database columns in the Docs table. This is what SharePoint does to retrieve the “Last Modified” column:

<Field ID={173f76c8-aebd-446a-9bc9-769a2bd2c18f}

       Name=Last_x0020_Modified

       SourceID=http://schemas.microsoft.com/sharepoint/v3

       StaticName=Last_x0020_Modified

       Group=_Hidden

       ReadOnly=TRUE

       Hidden=TRUE

       DisplayName=$Resources:core,Modified;

       Type=Lookup

       List=Docs

       FieldRef=ID

       ShowField=TimeLastModified

       Format=TRUE

       JoinColName=DoclibRowId

       JoinRowOrdinal=0

       JoinType=INNER>

About these ads

7 Comments »

  1. Briljant article, i’ve been strugling with this recursive lookup for quite some time. This solves my problem just before the live date (today) so I’ll hurry to implement it in time!!

    Thx.

    Comment by Ivo Vink — 20 May, 2008 @ 7:34 am

    • this is to Ivo Vink ..
      u told that u got u r issue got solved.
      i tried using self in schema .xml but it is not working for me

      Comment by jyothsna — 3 December, 2010 @ 12:11 pm

  2. [...] Lookup Fields to the Same List – Defined as a Site Column [...]

    Pingback by SharePoint, SharePoint and stuff : SharePoint Kaffeetasse #69 — 27 May, 2008 @ 11:39 am

  3. HI

    I am not good programmer, but know how to user SP Designer. Here what I am looking for:-

    I have 2 fields i.e. category/subcategory both have some static items information as mentiond below

    Category
    ——-
    Phone
    Printer

    SubCategory
    ———–
    Nokia
    Blackbery
    Brother
    Epson
    Konica

    Now If user select “Printer” from categor pulldown, so only Brother, Epson and Konica should appear in subcategory pulldown same will be applicable for If user select Phone, so only Nokia and Blackberry shoud appear in 2nd pulldown.

    How can I achieve this?

    Comment by Dishaniti — 3 November, 2008 @ 3:53 pm

  4. I tried creating a lookup to FolderChildCount column in docs list but receiving an error as “Cannot complete this action”. I am able to create a lookup to ItemChildCount column in docs list though.

    Any Ideas?

    Comment by Neelesh — 14 March, 2009 @ 5:35 pm

  5. Can you please give me more information regarding “UserInfo” and “Docs” attributes and its usage?

    Comment by Pradip Shrestha — 26 August, 2009 @ 4:12 am

  6. [...] across a couple of interesting resources. The first was an article by Hristo Pavol called ‘Lookup Fields to the same list‘ in which he posits that you can just provide the value ’self’ to make the column [...]

    Pingback by List Definition with Lookup Column that references itself | Infinite Dreamers — 6 January, 2010 @ 8:41 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Shocking Blue Green Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: