i have two lists. the first list name is "cases" and the second list name is "issues". in the cases list i have a text column that holds a case number. in the issues list i have lookup column name "connceted case". i want this lookup column to do a lookup against the case number field in the cases list, and to retrieve the values from that field.

I also want to do this: i have a choice column which name is "status" in the cases list. If the value of the status field is "closed" for a specific case item in the cases list, i want to make a restriction that the specific case item with the status of closed, wont be able to chooce in the lookup field connected case.

I dont know where to start, is this possible to do programmatically from visual studio??

Update i have tried the method now. The method adds the column just fine. But something is still wrong with the method, because when i add the method in my feature receiver for the site, it does the jobb for the lookup column, but i dont execute the following methods after the lookup methods. for example: Helpermethods.AddLockup, HelperMethods.CreateQuickLaunch.. it doesnt execute the CreateQuickLaunch

SPList sourceList = web.Lists["cases"];
         SPList dependList = web.Lists["issues"];

            string lookupColumn = dependList.Fields.AddLookup("connected case", sourceList.ID, true);
            SPFieldLookup primaryLookup = (SPFieldLookup)dependList.Fields.GetFieldByInternalName(lookupColumn);
            primaryLookup.LookupField = sourceList.Fields["casenumer"].InternalName;
            primaryLookup.Indexed = true;
            primaryLookup.RelationshipDeleteBehavior = SPRelationshipDeleteBehavior.Restrict;

**//Why do i need this secondary column? I have just copy and paste this one from the article. What function does the Last Name field does in my situation?** 

            string strSecondaryCol = dependList.Fields.AddDependentLookup("Last Name", primaryLookup.Id);
            SPFieldLookup secondaryCol = (SPFieldLookup)dependList.Fields.GetFieldByInternalName(strSecondaryCol);
            secondaryCol.LookupField = sourceList.Fields["Last Name"].InternalName;

2 Answers 2


Retrieving Values
If you're running SharePoint 2010 the CAML has been extended to support joins via Lookup columns.

So you'll be able to write a single CAML query against the issues list with a join to the cases list to do the filtering and retrieve the information from both lists.

See List Joins and Projections

If you're running a prior version then you need to do multiple queries and join the values yourselves as @Ray describes

Limit Selects
Regarding limiting selection to only non-closed item I'd recommend the following non-code solution:

  1. In the Cases list create a calculated field with a formular like IF([Status]="closed","",[Title]) (You can replace the [Title] part with whatever you want to show when selecting items
  2. In the Issues list create the lookup column using the calculated field, but with [Title] as an additional field (Requires SP2010)
  3. In views of Issues list don't use the lookup column, but instead the Lookup:Title column

This will create a column in cases which is blank for items you don't want to be able to select and happily enough SharePoint won't show blank items. The reason for including Title as an additional field and using that in views is that otherwise your views will show blank values when the corresponding case is closed.

Info to update in Question
If you followed my description of adding a calculated column to Cases which is blank when the case is closed (let's call this CaseNumberIfNotClosed) and otherwise has the value you want want to display (can be Title or custom field) (let's call that source field CaseNumberAlways)

Then what you need in the event receiver is something like (replace CaseNumberIfNotClosed and CaseNumberAlways with right names):

// Create look up field only allow selection of non closed cases
SPList sourceList = web.Lists["cases"]; 
SPList dependList = web.Lists["issues"]; 

// Create look up field only allow selection of non closed cases
string lookupColumn = dependList.Fields.AddLookup("Connected Case Selection", sourceList.ID, true); 
SPFieldLookup primaryLookup = (SPFieldLookup)dependList.Fields.GetFieldByInternalName(lookupColumn); 
primaryLookup.LookupField = sourceList.Fields["CaseNumberIfNotClosed"].InternalName; 
primaryLookup.Indexed = true; 
primaryLookup.RelationshipDeleteBehavior = SPRelationshipDeleteBehavior.Restrict; 

// Create additional field for using in views which has case number even if case is closed 
string strSecondaryCol = dependList.Fields.AddDependentLookup("Connected Case", primaryLookup.Id); 
SPFieldLookup secondaryCol = (SPFieldLookup)dependList.Fields.GetFieldByInternalName(strSecondaryCol); 
secondaryCol.LookupField = sourceList.Fields["CaseNumberAlways"].InternalName; 
  • Yes iam running SharePoint 2010. thats nice to hear, that only one query is neded. But after i have retrieved the information, how do i handle this when people will add items to both of the list. Should it be in some listEvent or? Thanks for your reply!
    – David
    Oct 10, 2012 at 21:00
  • Added section about the lookup to the answer Oct 11, 2012 at 7:07
  • Hey Per! Thanks for your suggestion it works fine doing this manually in sharepoint. But i dont understand, why cant i do this with code? i can se you mentioned "Regarding limiting selection to only non-closed item" but i dont really understand. isnt it possible to this with code?? Thanks so much to for your help! iam a beginner sharepoint developer, so many things are new to me.
    – David
    Oct 11, 2012 at 8:18
  • Yes you can do all of the adding of fields through "code" (which I recommend) and you can also do all the work to make this in forms using code (which I DON'T recommend). But ALWAYS remember, that the most important skill for a SharePoint developer is not to write code unless there is a really good reason to do it. Oct 11, 2012 at 8:33
  • all my fields are defined in XML from visual studio, shouldnt it be possible to create the calculated field and the formula there and also create the lookup field there? i have also created my custom views for the lists in visual studio, then i could define which columns i want to show in the views?
    – David
    Oct 11, 2012 at 8:46

To do this programmatically, as you described, one option would be to execute a series of CAML Queries against the target lists:

//grab the list - several different ways to accomplish this - this is only one particular way
SPList targetList = SPContext.Current.Web.Lists["titleOfList"];

//build the CAML Query
SPQuery spQuery = new SPQuery();
String camlQuery = @"somequerygoeshere";

SPListItemCollection filteredItems = targetList.GetItems(spQuery);

//do something with your filtered list of items down here

Details of how to build a CAML Query for SharePoint can be found here. Also search the sharepoint.stackexchange as there are lots of excellent discussions on building CAML for SharePoint.

  • i know about how to get list, and i have also read about that you can use CAML querys for a problem like that i described. So i should write a CAML query against the cases list and then retrieve all case numbers and filter thoose against thoose that have the status of closed? It would be nice with some examples querys, because i find it hard to query against many columns and many lists. Thansk for your reply!
    – David
    Oct 10, 2012 at 18:34
  • As I hint, I would suggest taking a look at using a CAML Query builder. Search There are some good free ones out there, but if you read some basic articles on CAML, building even a more complex query should be easy.
    – Ray
    Oct 11, 2012 at 2:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.