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.