Joining Two Lists using the Lists.asmx Service in SharePoint 2007

There are better ways of doing this in SP 2010, but this was for a project in SharePoint 2007. I had two lists, employees and events. An employee could either be a speaker or a panelist. The goal was to display a list of upcoming events along with any employee who was either a speaker or a panelist at the event in a marquee on the page. This code is based on Gordon Asbach’s post here:
http://www.sharepointclowns.com/2011/08/display-a-marquee-of-sharepoint-list-items/
See the above post for info on the marquee aspect. I have simply modified it to join together the two lists, instead of showing just one list.


<script type="text/javascript" src="resources/jquery-1.7.1.min.js"></script>
<script type="text/javascript" src="resources/jquery.marquee.js"></script>

<script type="text/javascript">

var marqueetext = "";

    $(document).ready(function() {
        //parent query: gets the title and dates of events with a startdate greater than today
        var soapEnv =
            "<soapenv:Envelope xmlns:soapenv='http://schemas.xmlsoap.org/soap/envelope/'> \
                <soapenv:Body> \
                 <GetListItems xmlns='http://schemas.microsoft.com/sharepoint/soap/'> \
                  <listName>Event</listName> \
                   <query><Query> \
                    <Where> \
                     <Geq> \
                      <FieldRef Name='StartDate'/> \
                      <Value Type='DateTime'> \
                       <Today/> \
                      </Value> \
                     </Geq> \
                    </Where> \
                    <OrderBy> \
                     <FieldRef Name='StartDate'/> \
                    </OrderBy> \
                   </Query></query> \
                   <viewFields> \
                    <ViewFields> \
                     <FieldRef Name='Title' /> \
                      <FieldRef Name='StartDate' /> \
                    </ViewFields> \
                   </viewFields> \
                  </GetListItems> \
                </soapenv:Body> \
            </soapenv:Envelope>";

        $.ajax({
            url: "_vti_bin/lists.asmx",
            type: "POST",
            dataType: "xml",
            data: soapEnv,
            complete: processResult,
            contentType: "text/xml; charset=\"utf-8\""
        });

        $('#Marquee').marquee();

    });
       
function processResult(xData, status) {
      $(xData.responseXML).find("z\\:row").each(function() {
         var eventtitle = $(this).attr("ows_Title");
         var liHtml = eventtitle;
         var eventdate= $(this).attr("ows_StartDate");

      if(eventdate!=null){
        liHtml += " " + eventdate.substring(0,10);
      }
//child query: gets the attendees who are either a speaker or panelist for the current event
//this query will be run for each parent event.
var soapEnv2 =
            "<soapenv:Envelope xmlns:soapenv='http://schemas.xmlsoap.org/soap/envelope/'> \
                <soapenv:Body> \
                 <GetListItems xmlns='http://schemas.microsoft.com/sharepoint/soap/'> \
                  <listName>Attendees</listName> \
                   <query><Query> \
                    <Where> \
                     <Or> \
                      <And> \
                       <Eq> \
                        <FieldRef Name='Event' />  \
                        <Value Type='Lookup'>" + $(this).attr('ows_Title') + "</Value> \
                       </Eq>  \
                       <Eq> \
                        <FieldRef Name='Speaker' />  \
                        <Value Type='Boolean'>1</Value> \
                       </Eq>  \
                      </And> \
                      <And> \
                       <Eq> \
                        <FieldRef Name='Event' />  \
                        <Value Type='Lookup'>" + $(this).attr('ows_Title') + "</Value> \
                       </Eq>  \
                       <Eq> \
                        <FieldRef Name='Panelist' />  \
                        <Value Type='Boolean'>1</Value> \
                       </Eq>  \
                      </And> \
                     </Or> \
                    </Where> \
                   </Query></query> \
                   <viewFields> \
                    <ViewFields> \
                     <FieldRef Name='Employee' /> \
                    </ViewFields> \
                   </viewFields> \
                  </GetListItems> \
                </soapenv:Body> \
            </soapenv:Envelope>";

 $.ajax({
   url: "_vti_bin/lists.asmx",
   type: "POST",
   dataType: "xml",
   data: soapEnv2,
   complete: function(xData, status) {
    $("#MarqueeItem").append(eventtitle + "  ");
    if(eventdate!=null){
      $("#MarqueeItem").append(eventdate.substring(0,10) + "  ");
    }

    $(xData.responseXML).find("z\\:row").each(function() {
    var personfielddata = $(this).attr("ows_Employee");
    var person = personfielddata.substring(personfielddata.indexOf('#')+1);

    var liHtml = person;
    $("#MarqueeItem").append(liHtml + "   ")

    });
   },
   contentType: "text/xml; charset=\"utf-8\""
  });
 });
}

</script>
<div id="Marquee">
<p id="MarqueeItem"></p>
</div>
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s