Running nested queries in Sharepoint

Question

Is it possible to run nested queries in Sharepoint? It seems like the asynchronous demand of Sharepoint stops me from doing what I'd like. Basically I have two lists. One list, Tickets, describes a particular event while the other, Updates, lists updates to those events. The primary key in Tickets links to a foreign key in Updates. I am trying to create a dashboard to show the Tickets along with their updates.

The following code creates the table fine and adds the correct entries from the Tickets table but does not add the information from the Updates table. I'm assuming this is because of the asynchronous nature of Sharepoint's list query and am unable to find a way around it. Using a JQuery callback looks the most promising so far but I have no experience with it and am trying to make sure that's the path I should go down. Further complicating the issue is the fact that I do not have access to sharepoint builder so I seem to be stuck with the ECMA code available through the text editor.

<script type="text/javascript">

ExecuteOrDelayUntilScriptLoaded(showTable, "sp.js");
var  tableField = "<table><tr><th>Site</th><th>Ticket</th><th>Last Update</th><th>Last Update Time</th><th>Controls</th></tr>";

function showTable()
{
 var context = new SP.ClientContext.get_current();
 var web = context.get_web();
 var list = web.get_lists().getByTitle('BCTickets');

 var query = new SP.CamlQuery();

 query.set_viewXml('<View><Query><Where><Eq><FieldRef Name="Active" /><Value Type="Integer">1</Value></Eq></Where></Query><ViewFields><FieldRef 

Name="TicketNumber" /><FieldRef Name="Site" /><FieldRef Name="Site" /></ViewFields></View>');

 this.collListItem = list.getItems(query);
 context.load(collListItem);

 context.executeQueryAsync(Function.createDelegate(this, this.createTable), Function.createDelegate(this, this.failed));
}

function createTable()
{
 var context = new SP.ClientContext.get_current();
 var web = context.get_web();
 var list = web.get_lists().getByTitle('Updates');

 var query = new SP.CamlQuery();

 var ListEnumerator = this.collListItem.getEnumerator();

 while(ListEnumerator.moveNext())
 {
  var currentItem = ListEnumerator.get_current();

  tableField += '<TR><TD>' + currentItem.get_item('Site') + '</TD><TD>' + currentItem.get_item('TicketNumber') + '</TD><TD>';

  query.set_viewXml('<View><Query><Where><Eq><FieldRef Name="BCTicketsID" /><Value Type="Integer">' + currentItem.get_item('ID') + 

'</Value></Eq></Where></Query><ViewFields><FieldRef Name="UpdateEntry" /><FieldRef Name="UpdateDate" /></ViewFields></View>');

  this.updateList = list.getItems(query);

  context.load(updateList);

  context.executeQueryAsync(Function.createDelegate(this, this.updateTable), Function.createDelegate(this, this.failed));

  tableField += '</TD><TD></TD></TR>';

 }

 tableField += "</TABLE>";

 document.getElementById("innerTable").innerHTML = tableField;
}

function updateTable()
{
 var ListEnumerator = this.updateList.getEnumerator();

 tableField += "<TABLE>";

 while(ListEnumerator.moveNext())
 {
  var currentItem = ListEnumerator.get_current();

  tableField += "<TR><TD>" + currentItem.get_item('UpdateEntry') + "</TR></TD>";
 }

 tableField += "</TABLE>";
}

function failed(sender, args)
{
 alert("failed. Message:" + args.get_message());
}</script>

<DIV ID="innerTable"></DIV>

Joe Joe · Accepted Answer · 2013-10-24T21:57:30

Kind of an older post, but just went through the exercise of needing an asynchronous query to finish before starting the next step. Scot Hillier's article on Promise objects solved it for me:

http://www.shillier.com/archive/2013/03/04/using-promises-with-the-javascript-client-object-model-in-sharepoint-2013.aspx

Running nested queries in Sharepoint

1 Answers