CAML Implementation

Coordinator
Nov 9, 2009 at 7:33 PM

I'm attempting to implement CAMLQuery and CAMLQueryOption with GetListItems. In this particular example, I have a list called Opportunities that contains 7 rows of data; the "Title" field containing "Opportunity A" through "Opportunity G", and the "Business Area" field which is a choice. My intentions here are twofold. Firstly, I want to be able to query the list to return all cases where "Business Area" is "A" and secondly, sort the Title field in the results set. It's only important to me that the Title is displayed (which will become a link later).

I'm not sure what I'm doing wrong. The CAMLQuery string, when added to my SPServices call, breaks the script all together and I get a blank dataset. The CAMLQueryOptions doesn't break the script, but doesn't work. I intentionally inputted my "test opportunities" out of order, I would have expected them to be returned alphabetically, but that's not happening. My guess is that I'm just missing some stupid little thing, but any direction would be welcome. I used the U2U CAML Query Builder; which outputted correctly in the tool, but when I put that in my SPServices call it doesn't work.

<script language = "javascript">
_spBodyOnLoadFunctionNames.push("ListCalls");
function ListCalls() {
	waitMessage = "Loading...";
	$("#WSOutput").html(waitMessage).SPServices({
		operation: "GetListItems",
		listName: "Opportunities",
		CAMLQuery: "<Query><Where><Eq><FieldRef Name=\"Business_x0020_Area\" /><Value Type=\"Choice\">A</Value></Eq></Where></Query>",
		CAMLQueryOptions: "<QueryOptions><OrderBy><FieldRef Name=\"Title\"/></OrderBy></QueryOptions>",
		async: false,
		completefunc: function (xData, Status) {
			$("#WSOutput").html("");
			$(xData.responseXML).find("z\\:row").each(function() {
				$("#WSOutput").append("<li>"+$(this).attr("ows_Title")+"</li>");
			});
		}
	});
}
</script>

 

Coordinator
Nov 9, 2009 at 7:47 PM
Edited Nov 9, 2009 at 8:04 PM

Michael:

Unfortunately, you don't have a sorting option with the CAMLQueryOptions in the GetListItems operation.  The SDK outlines the available allowable parameters for the operation, albeit not tremendously well.

You may also need to provide a ViewFields section so that you get something back:

<ViewFields>
  <FieldRef Name="ID" />
<FieldRef Name="Title" />
</ViewFields>

 

CAML is such a beastie for many people to get right, myself included.  One of my tricks is to drop a Data View Web Part (DVWP) on a page and set my filters and such and then look at the resulting CAML, tweaking from there.

Speaking of DVWPs, depending on what you are planning to do with the output, a DVWP can be a better starting point.  You can still use jQuery and/or the Web Services to manipulate the rendered result on the client side, but it's a good way yo get the data onto the page initially.

M.

Coordinator
Nov 9, 2009 at 8:01 PM

Marc,

I solved the issue. I was thinking that the sorting had to be passed through CAMLQueryOptions. I build my query in the U2U tool then put it directly in the CAMLQuery parameter and it's working now. Sorting is also working (surprisingly). Here's my code in case anyone else can learn from my mistakes. I also highly recommend the U2U CAML Query tool (http://www.u2u.be/Res/Tools/CamlQueryBuilder.aspx).

<script language = "javascript">
_spBodyOnLoadFunctionNames.push("ListCalls");
function ListCalls() {
	waitMessage = "Loading...";
	$("#WSOutput").html(waitMessage).SPServices({
		operation: "GetListItems",
		listName: "Opportunities",
		CAMLQuery: "<Query> \
		   <OrderBy> \
			  <FieldRef Name=\"Title\" /> \
		   </OrderBy> \
		   <Where> \
			  <Eq> \
				 <FieldRef Name=\"Business_x0020_Area\" /> \
				 <Value Type=\"Choice\">A</Value> \
			  </Eq> \
		   </Where> \
		</Query>",
		async: false,
		completefunc: function (xData, Status) {
			$("#WSOutput").html("");
			$(xData.responseXML).find("z\\:row").each(function() {
				$("#WSOutput").append("<li>"+$(this).attr("ows_Title")+"</li>");
			});
		}
	});
}
</script>
Coordinator
Nov 9, 2009 at 8:04 PM

Great! Nice to be able to write the CAML without all the &gt; and &lt; notation, IMHO.

M.

Jul 13, 2010 at 7:51 PM
Edited Jul 13, 2010 at 7:54 PM
You can sign in with this tool, then use the GUI to construct the CAML string for you. Such a lifesaver! It signs into your site and pulls back all the libraries/lists/etc. http://www.u2u.be/res/tools/camlquerybuilder.aspx