CAML Query question with GetListItems

Feb 9, 2011 at 5:02 AM

I'm very new at assembling CAML queries, so I'm stumped at getting this to work.  I am trying to build a utility for a MOSS site to take three variables from the URL query string and then use that to display filtered data on the page.  I am not able to use the Sharepoint designer, so I'm using jQuery and SPServices to generate the view in a CEWP.  I got the data to display OK with one filter criteria, but I seem to have gotten something wrong when nesting three.  My error for now seems to be in the string I'm using for the CAML query. 

I am using hard-coded values at the moment, but my next step it so get the criteria from the query string.

Thanks for any pointers!

--- My code, embedded in a CEWP ---

<script language="javascript" type="text/javascript"
src="../ResourcesjQuery/jquery-1.4.3.js">
</script>
<script language="text/javascript" type = "text/javascript"
src="../ResourcesjQuery/spjs-utility.js"></script>
<script language="javascript" type="text/javascript"
src="../ResourcesjQuery/jquery.SPServices-0.5.7.js"></script>

<script language="javascript" type="text/javascript">

var CamlQuery = "<Query><Where><AND><AND><Eq><FieldRef Name='SearchMonth' /><Value Type='Choice'>1</Value></Eq><Eq><FieldRef Name='SearchYear' /><Value Type='Text'>2011</Value></Eq></AND><Eq><FieldRef Name='TherapeuticArea' /><Value Type='Choice'>Immunology</Value>
      </Eq></AND></Where></Query>";

var ViewFields = "<ViewFields>" +
                          "<FieldRef Name='DrugName' />" +
                          "<FieldRef Name='TherapeuticArea' />" +
                          "</ViewFields>";

$(document).ready(function() {
  //alert("This function started!");
  alert(CamlQuery);
  $().SPServices({
    operation: "GetListItems",
    async: false,
    listName: "RoutineSurveillanceDetail",
    CAMLViewFields: ViewFields,
    CAMLQuery: CamlQuery,
      completefunc: function (xData, Status) {
      //alert(xData.responseXML.xml);     
      $(xData.responseXML).find("[nodeName='z:row']").each(function() {
        var liHtml = "<li>" + $(this).attr("ows_DrugName") + "   " + $(this).attr("ows_TherapeuticArea") + "</li>";
        $("#tasksUL").append(liHtml);
      });
    }
  });
});
</script>
<ul id="tasksUL"/>

</script>

Coordinator
Feb 9, 2011 at 6:11 AM
pjenrow: I'm looking at this on my iPhone at the moment so it's a little hard to tell, but I think the problem is simply that you are using 'AND' instead of 'And'. I just Binged for a good example for you and found this: http://social.technet.microsoft.com/Forums/en/sharepointgeneral/thread/34d6d303-301f-4199-bc0b-fe5c39fdbf9a Let me know how it goes... M.
Feb 9, 2011 at 10:30 AM

Since you're looking at it on your Phone, I tested the query in the CAML query builder. The query breaks definitely when using 'AND' in stead of 'And'.

Coordinator
Feb 9, 2011 at 1:30 PM
Thanks, Anita! M.
Feb 9, 2011 at 3:26 PM

Thanks so much for the help.  I corrected my case mistake (AND vs And), and I was able to get the query to run, but only with the two search criteria.  In other words, this runs:

var CamlQuery = "<Query><Where><And><Eq><FieldRef Name='SearchMonth' /><Value Type='Choice'>1</Value></Eq><Eq><FieldRef Name='SearchYear' /><Value Type='Text'>2011</Value></Eq></And></Where></Query>";

 

But if I add the third filter, the script fails to run at all:

var CamlQuery = "<Query><Where><And><And><Eq><FieldRef Name='SearchMonth' /><Value Type='Choice'>1</Value></Eq><Eq><FieldRef Name='SearchYear' /><Value Type='Text'>2011</Value></Eq></And><Eq><FieldRef Name='TherapeuticArea' /><Value Type='Text'>Immunology</Value>
      </Eq></And></Where></Query>";

Thanks again for the help!

 

Feb 9, 2011 at 3:48 PM

At first glance, all your syntax looks golden.  I use a free tool called "Stramit CAML viewer 2007" from right here on Codeplex (http://spcamlviewer.codeplex.com/)- you create you view in SharePoint, and this tool allows you to see the CAML that was created (also useful for CamlView)

Once you log in to your site using the tool (or it will accept and pass along your AD Credentials) find your list, then your view, then choose View tab.  Click the XML Format and cut and paste out everything from Query to /Query.

The only clean up will be to do a Find and Replace to change the double quotes to single quotes.

This tool also shows you all the internal field names, list and view guids, etc.  Not bad for free!!

Bill

Feb 9, 2011 at 4:54 PM

In your example code the query with 2 search criteria is on one line. The query with 3 search criteria is on multiple lines. I guess this is because you pasted it here.
It is one line in your code, isn't it?

The query itself looks fine.

Regards, Anita

Feb 9, 2011 at 5:44 PM

Thank you so much.  In the editing process I did manage to include a line break in there.  Once I got rid of that, it ran. 

 

Thanks for the help, and thanks also Bill for the new tool!

Coordinator
Feb 9, 2011 at 6:24 PM
Also look at the U2U CAML Builder, which is excellent and free as well. M.