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)
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:
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: