I want to query for items where a lookup has a title which contains some text.
The list has over 5000 items, but the query result will return fewer, so it should work.

This query works, querying for title:

<Contains><FieldRef Name='Title' /><Value Type='Text'>test</Value></Contains>

This query does not work, querying a lookup:

<Contains><FieldRef Name='MyLookup' /><Value Type='Lookup'>test</Value></Contains>

Both fields are indexed. I don't want to first fetch IDs for the lookup list items (LookupId works, but only on ID). I have tried Type='Text'. I have tried with <Eq>.

  • Filtering with lookup on a list with over 5k items works with REST api weirdly but not CAML Query...
    – Akhoy
    Nov 22 '18 at 14:29

You cannot use <Contains> with Lookup columns. Only Text and Note field types are supported.

See this MSDN article.

  • 1
    It works if the list has less than 5000 items, so I think works.
    – eirikb
    Apr 8 '16 at 10:48
  • Contains isn't indexed, the UI tells you this when you try to create a view, so it stands to reason that it would fail once you go over 5000. Apr 8 '16 at 12:08
  • 1
    @EricAlexander Isn't it the field which is indexed, and not the query part? And if <Contains> is not indexed, why does it work with Title?
    – eirikb
    Apr 8 '16 at 12:17

Include your query inside a Where clause/tag.

<Where><Contains><FieldRef Name='MyLookup' /><Value Type='Lookup'>test</Value></Contains></Where>
  • Yes I also need a View, since I use CSOM. Sorry I should have mentioned this, but since I mention my first example works it was proof enough the surround bits are in place.
    – eirikb
    Apr 8 '16 at 10:49
  • can you please explain me a bit more & if you can send me a entire line than it would be a great to understand
    – Hardik
    Apr 8 '16 at 11:02
  • I'm not sure how to explain it any better. But I've thrown together a quick example for you here: gist.github.com/eirikb/8e71c5cb31b4d64f779cbcd7b3b06692 It's using JSOM, whilst I actually use CSOM, but it's the same result.
    – eirikb
    Apr 8 '16 at 11:23
  • You may get solution from shareyourpoint.net/2015/07/21/…
    – Hardik
    Apr 8 '16 at 11:29
  • I'm not sure how that link helps? You mean the core problem is on a higher level (e.g., structural, search, etc)? I see QueryThrottleMode is mentioned, but I don't think this works with CSOM (it's for SPQuery, not CamlQuery).
    – eirikb
    Apr 8 '16 at 11:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.