I have only been using SharePoint for a few days and I know this question has been asked many times before but I've tried all of them and none of them work.

These are my design

  • Document as a Document Library type have an Id

    Id | DocumentName
    1  | Document A
    2  | Document B
  • Activities list, have a foreign key 'DocId' that reference to the Document Id

    Id | DocId | Name
    1  | 1     | Activity A
    2  | 1     | Activity B
    3  | 1     | Activity C
    4  | 2     | Activity D

The problem is I need to get all the Activities that included the document name on the Document.

In SQL I can use Join query to get the additional information from another table. But I have tried some Join statement in the CAML query and none of them work. Here is the result that I need to get.

Id | DocId | Name       | DocumentName
1  | 1     | Activity A | Document A
2  | 1     | Activity B | Document A
3  | 1     | Activity C | Document A
4  | 2     | Activity D | Document B

Could anyone please suggest me the query. Here is my query

    <FieldRef Name = 'DocLeafRef'/>
    <FieldRef Name = 'ID'/>
    <FieldRef Name = 'e8_document'/>
    <FieldRef Name = 'Title'/>
    <FieldRef Name = 'Author'/>
    <FieldRef Name = 'Created'/>
    <Join Type = 'INNER' ListAlias = 'Documents'>
         <FieldRef Name ='e8_document' RefType = 'Id'/>
         <FieldRef Name ='ID' List ='Documents'/>
    <Field ShowField ='FileLeafRef' Type ='Lookup' Name ='DocLeafRef' List ='Documents'/>
           <FieldRef Name='e8_caseId'></FieldRef>
           <Value Type = 'Number'>23</Value>

I'm querying on the Activities list and the e8_document is the lookup field that reference to the Id in the Document. As i understand, we need the projection and i've added the ProjectedFields but the query throws an error.

1 Answer 1

SPQuery query = new SPQuery();

query.Joins = "<Join Type='INNER' ListAlias='Documents'>" +
                "<Eq>" +
                    "<FieldRef Name='e8_document' RefType='Id'/>" +
                    "<FieldRef List='Documents' Name='ID'/>" +
                "</Eq>" +

query.ProjectedFields = "<Field Name='DocName' Type='Lookup' List='Documents' ShowField='DocumentName'/>";

query.ViewFields = "<FieldRef Name='DocName'/>";

SPList lstActivities = web.Lists["Activities"];
SPListItemCollection items = customerList.GetItems(query);

Use 'Id' instead of 'Lookup' in Join of query.

  • I get the error "Value does not fall within the expected range" when i try to get the docname. Does this relate to the internal name in the Lookup field
    – Hiep Lam
    Commented Jan 28, 2017 at 18:56
  • I have one more problem with the <ProjectedFields> as the query above a need the FileLeafRef in the foreign list which is "File" type and i get the error "Value does not fall within the expected range". Does this error cause by the file is to long or the File type need another way to get
    – Hiep Lam
    Commented Jan 28, 2017 at 19:25
  • Could you please take a look at this
    – Hiep Lam
    Commented Feb 3, 2017 at 7:37

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.