0

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

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

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.

3
SPQuery query = new SPQuery();

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

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 Jan 28 '17 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 Jan 28 '17 at 19:25
  • Could you please take a look at this – Hiep Lam Feb 3 '17 at 7:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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