1

I am creating an applicant tool which contains the following lists:

Applicant; Applicant Answers; Job Questionnaire; Job Title

The lists are related as follows:

Applicant.ID = Applicant Answers.ApplicantID;
Applicant.JobApplicant = JobTitle.Title;
JobQuestionnaire.JobTitle = JobTitle.Title;
JobQuestionnaire.ID = Applicant Answers.QuestionID;

I need to load the questions and answers. I can load the answers from the Applicant Answer list, however, I cannot load the question text from there. Instead, I need to use the relationships listed above to retrieve the Question Text, which is a field in Job Questionnaire based on the Question ID.

I have done some research and noticed that I can't use one query to retrieve from two lists. What can I do in order to retrieve the Question Text?

2

You can query based on relations in two lists, please have a look at List Joins and Projections

You can use joins to get the projected field Question Text, something like:

SPQuery query = new SPQuery();

query.Join = @"
<Joins>
  <Join Type='INNER' ListAlias='Applicant Answers'>
    <Eq>
      <FieldRef Name='QuestionID' RefType='Id'/>
      <FieldRef List='JobQuestionnaire' Name='ID'/>
    </Eq>
  </Join>
</Joins>";

query.ProjectedFields = @"
<Field Name='ProjectQuestionText' Type='Text' List='JobQuestionnaire' ShowField='QuestionText'/>";

SPQuery.ViewFields = "<FieldRef Name='Title'/><FieldRef Name='ProjectQuestionText'/>";

SPList questionList = web.Lists["JobQuestionnaire"];
SPListItemCollection items = questionList.GetItems(query);                
foreach (SPListItem item in items)
{
    var questionText = item["ProjectQuestionText"].ToString();
}
  • Thanks for taking the time to answer. But I need to use all of the relations listed above in order to get the QuestionText. I need the Applicant ID in order to determine which applicant I'm working with. From there, I can get the Job Title, and from the Job Title I need to get the Question ID which will lead to the QuestionText. Thanks! – Adrian Nov 11 '14 at 13:10
  • It is also fair to note that I'm using SharePoint 2013 - Client, not Server. – Adrian Nov 11 '14 at 13:44
  • I just gave you an idea, you can add further joins.. CAML query should work on Client.. – Arsalan Adam Khatri Nov 11 '14 at 14:41
  • CAML does work, but the SPQuery query part does not. – Adrian Nov 11 '14 at 17:58
1

You can use the same CAML query with the SPSiteDataQuery. You could query on list type or supply guids to identify each list using the SPSiteDataQuery.Lists property

how to query multiple lists using CAML

query.Lists = "<Lists><List ID='7A9FDBE6-0841-430a-8D9A-53355801B5D5' /><List ID='3D18F506-FCA1-451e-B645-2D720DC84FD8' /></Lists>";

More info http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spsitedataquery.lists.aspx

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.