CAML query to a SharePoint list, order by a lookup field

Question

I'm attempting to pull a list from SharePoint via CAML and I want the list returned ordered by a specific field. The field is a lookup field. The query comes back unordered when I set the OrderBy to be the lookup field, if I use a text field it's fine.

The U2U CAML query builder will return this query ordered when I build it in the editor.

Here's a code snippet of how I build and execute the query:

String baseQuery = "<Query><Where><Eq><FieldRef Name='paApproved' /><Value Type='Boolean'>1</Value></Eq></Where><OrderBy><FieldRef Name='paState' Ascending='True' LookupValue='TRUE' /></OrderBy></Query>";

qStates.Query = baseQuery;

SPListItemCollection byState = web.Lists["paUpdates"].GetItems(qStates);

The rest is a for loop that parses the collection and displays it. I can post that if necessary.

Here's the SOAP call made by the CAML query tool, I scraped it from the HTTP stream with wireshark.

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope 
      xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" 
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
      xmlns:xsd="http://www.w3.org/2001/XMLSchema">
 <soap:Body>
  <GetListItems xmlns="http://schemas.microsoft.com/sharepoint/soap/">
   <listName>paUpdates</listName>
   <query>
    <Query xmlns="">
     <Where>
      <Eq>
       <FieldRef Name="paApproved" />
       <Value Type="Boolean">1</Value>
      </Eq>
     </Where>
     <OrderBy>
      <FieldRef Name="paState" Ascending="False" />
     </OrderBy>
    </Query>
   </query>
   <viewFields>
    <ViewFields xmlns="" />
   </viewFields>
   <queryOptions>
    <QueryOptions xmlns="" />
   </queryOptions>
  </GetListItems>
 </soap:Body>
</soap:Envelope>

For whatever reason the CAML query tool works, my code doesn't. Anyone know why? Thanks in advance.

Edited to reflect the code I'm actually testing. I had some code that had incorrect values.

Kit Menke · Accepted Answer · 2011-02-07T16:52:16.603

8

The code sample you posted doesn't match the wireshark query:

<Query><Where><Eq><FieldRef Name='paApproved' /><Value Type='Boolean'>1</Value></Eq></Where><OrderBy><FieldRef Name='Title' Ascending='True' /></OrderBy></Query>

Should be:

<Where><Eq><FieldRef Name="paApproved" /><Value Type="Boolean">1</Value></Eq></Where><OrderBy><FieldRef Name="paState" Ascending="False" /></OrderBy>

You don't need the <Query></Query> elements (see here for an example).

edited Feb 07 '11 at 16:52

answered Feb 07 '11 at 16:00

Kit Menke

7,046
1
32
54

You're right. Sorry, I was testing something. I was using "title" to see if it would sort on a text field, instead of a lookup. The code I was using did have the "paState" as the field, and it doesn't work. I cut and pasted the wrong version. It will sort on a text field, but now on a lookup field. – kevingreen Feb 07 '11 at 16:08
Ascending values aren't the same either, but I've tested it both ways it still won't order in the C# query. – kevingreen Feb 07 '11 at 16:20
Woops I had a typo. You also shouldn't include the `` node when using SPQuery. See my update. – Kit Menke Feb 07 '11 at 16:52
Hmm, I tried it without the and the script blows up each time I run it. – kevingreen Feb 07 '11 at 16:54
Oh whoa... There's an error further down in my code. It looks like it works without the , it's failing otherwise. I caught it when I went into debug mode. – kevingreen Feb 07 '11 at 16:58
This might be solved... It seems to work without the tags. I'll come back in a minute and mark it solved, I'm just going to get the whole thing to work to be sure. Thanks! – kevingreen Feb 07 '11 at 17:00
Thanks, the issue is with the tags. When I remove them it works. The code blows up later on for other issues, I'll deal with that. – kevingreen Feb 07 '11 at 17:21

score 1 · Answer 2 · answered Feb 07 '11 at 17:09

I tried to reproduce the problem. Your query really doesn't sort correctly. I've made two changes to make it work - removed Query element and removed LookupValue='TRUE' (there is no attribute with such name in element schema). After that all seems fine.

CAML query to a SharePoint list, order by a lookup field

2 Answers2