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.

Cartie

Answer 1

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).

Velour

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. – Cartie

Ascending values aren't the same either, but I've tested it both ways it still won't order in the C# query. – Cartie

Woops I had a typo. You also shouldn't include the <Query> node when using SPQuery. See my update. – Velour

Hmm, I tried it without the <Query> and the script blows up each time I run it. – Cartie

Oh whoa... There's an error further down in my code. It looks like it works without the <query>, it's failing otherwise. I caught it when I went into debug mode. – Cartie

This might be solved... It seems to work without the <query></query> 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! – Cartie

Thanks, the issue is with the <query> tags. When I remove them it works. The code blows up later on for other issues, I'll deal with that. – Cartie

Answer 2

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.

Elemi