SharePoint: In a CAML query, filter by lookup item ID, not by its value

 

Your filter for the CAML query probably looks like this currently:

 

<Query>
  <Where>
    <Eq>
      <FieldRef Name ="Customer"/>
      <Value Type ="Text">
        Dunder Mifflin
      </Value>
    </Eq>
  </Where>
</Query> 

 

But the "Customer" field is a lookup column to a different list of customers and on most view pages for customers, you probably need to fetch the related data using the Customer ID (the ID column for the customer list) and not the customer name.

Also, Sharepoint designer 2007 does not, using the UI, allow you to set a filter on a data form webpart and using a lookup column's ID field. You can only use the value of a lookup column in the filter.

Here's how I was able to do it after a bit of research...

 

<Query>
  <Where>
    <Eq>
      <FieldRef Name ="Customer" LookupId="true" />
      <Value Type ="Lookup">
        15
      </Value>
    </Eq>
  </Where>
</Query> 

 

Just add the LookupId = "true" attribute to the FieldRef tag and change the Value Type attribute to "Lookup". Your filter now looks up the value of the lookup column by using the ID of the item instead of the value.

 

 

http://www.vishalseth.com/post/2009/12/02/SharePoint-In-a-CAML-query-filter-by-lookup-item-ID-not-by-its-value.aspx