I spent way too long yesterday trying to get a CAML query that would allow me to query on a lookup field and figured I’d share.
First, one thing I find very annoying about SharePoint is that if you have an invalid query, the call to the GetItems() method returns all rows. What?? How ’bout a friendly exception that I have an invalid query??
So here was the solution: the CAML query should be formatted with a type attribute of Lookup in the Value tag and the text of that element should be the same value that you’d see in the SharePoint UI.
<FieldRef Name="GiftsForMom" />
Looking at it now, this is reasonably straightforward. However, it didn’t click in my development-damaged brain, as the value you see in a lookup field when you inspect it in the debugger is something like 4;#Gift Types.
When constructing CAML queries you must use internal names by this wonderful exception:
Exception caught Microsoft.SharePoint.SPException: One or more field types are not installed properly. Go to the list settings page to delete these fields. —>
System.Runtime.InteropServices.COMException (0x81020014): One or more field types are not installed properly. Go to the list settings page to delete these fields.