Help with Relational Lists

Oct 4, 2012 at 5:38 PM

I'm new to SPServices but absolutely love the burden it has removed with interaction between SharePoint data and the web services.  I'm building a site index navigation page.  I have it working just fine but wanted to pull related child list info in for each record of the parent.  I'm using the latest version of SPServices and JQuery.  I figured I could loop through the initial GetListItems operation and then pull related using the .SPDisplayRelatedInfo command but it looks like it's only for forms where a user selects something, not a loop operation.  I'm probably wrong, I'm new to this, does anyone have a way and/or examples of pulling parent/child relational data that isn't a form related example?  Any help would be most appreciated, thanks!

Coordinator
Oct 4, 2012 at 5:50 PM

You have the right idea. It's much like you would do in XSL in a DVWP or any other code. You need a GetListItems operation to get the items in the parent list and the an inner loop to get the child items for each parent item.

M.

Oct 4, 2012 at 7:27 PM

I have done this several times on custom built form interfaces.  Here's a code example that should help.  The '$(document).ready(function()' initializes a simple <select> control called 'access_DirectorateFilter'.  That control has an onchange event which fires 'refreshDivisions()' which populates/updates another <select> control calles 'access_DivisionFilter.' That select control contains a filtered set of divisions based on the selected directorate. All of the divisions and directorates are stored in a single list.  The Divisions have a parent (one of the Directorates) whereas the Directorates do not have a parent.

$(document).ready(function(){
  //Populate Directorates
  var strHTMLDirectorates = "";
  $().SPServices({
      operation: "GetListItems",
      async: false,
      listName: "Customers",
      CAMLViewFields: "<ViewFields><FieldRef Name='ID' /><FieldRef Name='Title' /></ViewFields>",
      CAMLQuery: "<Query><Where><IsNull><FieldRef Name='Parent' /></IsNull></Where><OrderBy><FieldRef Name='Title' /></OrderBy></Query>",
      CAMLQueryOptions: "<QueryOptions><IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns></QueryOptions>",
      completefunc: function(xData, Status) {
        $(xData.responseXML).SPFilterNode("z:row").each(function() {
          strHTMLDirectorates += "<option value='" + $(this).attr("ows_Title") + "'>" + $(this).attr("ows_Title") + "</option>";
        });
        $("#access_DirectorateFilter").html(strHTMLDirectorates);
      }
  });
  $("#access_DirectorateFilter").val(null);
});


function refreshDivisions(){
  //Refresh the Divisions pick list based on the selected Directorate
  var strHTMLDivisions = "";
  $().SPServices({
      operation: "GetListItems",
      async: false,
      listName: "Customers",
      CAMLViewFields: "<ViewFields><FieldRef Name='ID' /><FieldRef Name='Title' /></ViewFields>",
      CAMLQuery: "<Query><Where><Eq><FieldRef Name='Parent' /><Value Type='Text'>" + $("#access_DirectorateFilter").val() + "</Value></Eq></Where><OrderBy><FieldRef Name='Title' /></OrderBy></Query>",
      CAMLQueryOptions: "<QueryOptions><IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns></QueryOptions>",
      completefunc: function(xData, Status) {
        $(xData.responseXML).SPFilterNode("z:row").each(function() {
          strHTMLDivisions += "<option value='" + $(this).attr("ows_Title") + "'>" + $(this).attr("ows_Title") + "</option>";
        });
        $("#access_DivisionFilter").html(strHTMLDivisions);
      }
  });
  $("#access_DivisionFilter").val(null);
}
 
Oct 4, 2012 at 7:49 PM

I'm kind of lost now, all I wanted to do was have a person hover over, say an "A", which triggers the event that fires GetListItems operation for SPServices.  In the completefunc it would do the usual for each z:row.  In that loop i want to get the related child records from a related list.  I thought i was suppose to use the $().SPServices.SPDisplayRelatedInfo but all the examples are for input forms.  I don't have an input form to reference as it's just pulling a hash tag of a letter to user for triggering the required GetListItems query operation.  Can anyone point me in the right direction on how to loop inside the GetListItems operation and append child related items?  Appreciate the input so far, thanks!

Oct 4, 2012 at 8:15 PM

My example is actually using two <select> controls.  The first <select> is the Directorate, which serves as a filter item for the second <select>.  I'm not sure what sort of controls are on your custom form.  You are correct in that the SPServices function SPDisplayRelatedInfo will only work on the SharePoint list's NewForm.aspx and EditForm.aspx.  Since you've designed a custom interface, that function will not work out of the box.  That function does, however, utilize 'GetListItems' with a CAMLQuery filter argument then loops through the result set to populate the table below the target.  The function 'refreshDivisions()' above is doing something similar but not creating a table with the results but rather a list of <options> for the <select> control.  You can fire that function on whatever event you want but you must grab a value from somewhere to serve as the filter criteria for the GetListItems call.  My example uses the text value of the <select> control 'access_DirectorateFilter.'  It plugs that into the CAMLQuery argument of 'refreshDivisions.'  Once I have the return, the 'completefunc' takes over and creates a list of '<option>' elements to populate the <select> control.  What sort of object are you trying to populate with the returned list?  If it's a <select> control, the example in 'refreshDivisions' is real close to what you need.  If not, you can modify the completefunc to create whatever HTML string you need to populate your control.  At the end of the loop, the HTML string I built is assigned as the inner HTML of the <select> control 'access_DivisionFilter.'

Oct 5, 2012 at 12:39 AM
Edited Oct 5, 2012 at 12:39 AM

Very rudimentary untested example:

$(document).ready(function() {
  $().SPServices({
    operation: "GetListItems",
    async: false,
    listName: "Parent",
    CAMLViewFields: "<ViewFields><FieldRef Name='Title' /></ViewFields>",
    completefunc: function (xData, Status) {
      $(xData.responseXML).SPFilterNode("z:row").each(function() {
    var theID = $(this).attr("ows_ID");
    $().SPServices({
    operation: "GetListItems",
    async: false,
    listName: "Child",
    CAMLViewFields: "<ViewFields><FieldRef Name='Title' /></ViewFields>",
    CAMLQuery: "<Query><Where><Eq><FieldRef Name='ParentColumn' LookupID='True'/><Value Type='Lookup'>"+theID+"</Value></Eq></Where></Query>";
    completefunc: function (xData, Status) {
     $(xData.responseXML).SPFilterNode("z:row").each(function() {
     //do stuff
     }); 
   }
 });
});

 
}
});
Oct 5, 2012 at 5:47 PM

This is more what I was looking for, thank you PirateEric.  I though i would be able to use the initial GetListItems and loop inside it with the SPDisplayRelatedInfo with reference to the lookup id but i haven't found any examples of this.  I just wanted to use my Relational List lookup in an efficient way.  I'll try out your example today and see where I get, thanks for posting this!

Oct 5, 2012 at 7:33 PM
PirateEric wrote:

Very rudimentary untested example:

$(document).ready(function() {
  $().SPServices({
    operation: "GetListItems",
    async: false,
    listName: "Parent",
    CAMLViewFields: "<ViewFields><FieldRef Name='Title' /></ViewFields>",
    completefunc: function (xData, Status) {
      $(xData.responseXML).SPFilterNode("z:row").each(function() {
    var theID = $(this).attr("ows_ID");
    $().SPServices({
    operation: "GetListItems",
    async: false,
    listName: "Child",
    CAMLViewFields: "<ViewFields><FieldRef Name='Title' /></ViewFields>",
    CAMLQuery: "<Query><Where><Eq><FieldRef Name='ParentColumn' LookupID='True'/><Value Type='Lookup'>"+theID+"</Value></Eq></Where></Query>";
    completefunc: function (xData, Status) {
     $(xData.responseXML).SPFilterNode("z:row").each(function() {
     //do stuff
     }); 
   }
 });
});

 
}
});


I was using part of your statement and nothing was working.  Scratching my head and doing a little research came up empty handed.  Then i realized LookupID='True' really should be LookupId='True' with a lowercase "d" in the "Id" because CAML is case sensitive, well just plain sensitive, all works now.  So yes, your proposed solution works.  I will need to test against a very large list to see what the performance is and let you know, thanks though for your input! :)

Coordinator
Oct 8, 2012 at 3:17 AM
Edited Oct 10, 2012 at 4:46 PM

One caveat to this: every outer loop causes a new Web Service call to get items in the inner loop. If you have a lot of items, this will slow things down a lot. Depending on your data, you may want to get all of the items from the child list in one call and then grab the ones you need in each iteration.

M.

Oct 8, 2012 at 9:12 PM

Yes performance could be an issue. With the inner call being passed highly specific CAML, it shouldn't be too heavy handed, depending on what the parent is doing I suppose.