GetListItems returns no items when filtering by lookup columns

Question

I am using the SP Lists service, the GetListItems method as follows (guidView is set to the "All Items" view):

listasSoap.getListItems(
  guidList,
  guidView,
  null,
  viewFields,
  "0",
  null,
  "");

And getting back all 22069 items.

Now, I retrieve the contents of the ows_Contrato field of one of those items, who happens to be, for example:

667;#ABAP14

And try the following queries:

<Query><Where><Eq><FieldRef Name="Contrato"/><Value Type="Lookup">667;#ABAP14</Value></Eq></Where></Query>

<Query><Where><Eq><FieldRef Name="Contrato" LookupId="True"/><Value Type="Lookup">667</Value></Eq></Where></Query>

But those queries return 0 items (despite the values being copy pasted from the ows_Contrato field.

I am generating the query Strings and SOAP object programmatically, and they work as they should for other lists. Yet no matter how many times I try, I cannot retrieve any items (0 elements with "z:row" tag name).

OTOH, a where directly to the ID works as expected.

<Query><Where><Eq><FieldRef Name="ID"/><Value Type="Counter">23984</Value></Eq></Where></Query>

What could be the issue that I am facing? As I said, other lists of the site work perfectly with similar queries.

The client is Java, the server is SharePoint 2007.

Answer 1

Actually there are two errors:

• Looking up "by value" (without the LookupId="True" attribute) needs me to pass only the value of the field referenced/show; in other words the value right of the ;#:

  <Query>
    <Where>
      <Eq>
        <FieldRef Name="Contrato"/>
        <Value Type="Lookup">ABAP14</Value>
      </Eq>
    </Where>
  </Query>
  

  Anyway this error is kind of secundary to me, since I usually use search by ID (safer in case the value is repeated, and more straight-forward); I tried it out of the issues with the search by ID

• The issues of looking up "by ID" seems related to the fact that the list is indexed by that field. The solution for these situation is changing the type of the value to Integer; since this way (with Integer) seems to be working for all the situations (field either indexed or not indexed) I'll change my code to always use it:

  <Query>
    <Where>
      <Eq>
        <FieldRef Name="Contrato" LookupId="True"/>
        <Value Type="Integer">667</Value>
      </Eq>
    </Where>
   </Query>