SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

So for example I have field 'TestField' which is indexed. How can I caml for items where 'TestField' value is null? My example down here will fail and won't find what I am querying for.

      <FieldRef Name='TestField' />

On the internet I only found people complaining about this problem but couldn't find any answer.

share|improve this question
As workaround I can propose using default value for those field, say "empty". On views you can modify xsl transformation, so it will replace value "empty" with empty string, but in CAML you can use query where TestField = "empty" – Kai Mar 24 '13 at 19:54
Seems to be the same as… – Dave Paylor Mar 24 '13 at 23:54
I needed fast solution, so I ended up with getting all list data as DataTable and then using linq to get rows where my desired field value was null. There is just that I don't know how good performance is in my solution. But Kai's idea I think culd be very good, because then it's possible to caml for null values also in views. Looks like there isn't good looking solution... – Helminth Mar 29 '13 at 17:19
@DavePaylor - that other question isn't for Indexed columns, which IsNull doesn't seem to work for, apparently because the indexer doesn't include null values. – johnnycardy Feb 16 '15 at 13:15
up vote 0 down vote accepted

The conclusion I've come to is that you cannot perform an IsNull query against an indexed column.

Workarounds include:

  • not indexing the column (and be careful of the view threshold...)
  • query all items and perform the filtering in code (inefficient)
  • change your data such that null values are replaced with a special 'dummy' value you can query against. This is hardly ideal, but probably the best solution.
share|improve this answer

If the field in question is a lookup field adding "LookupId='True'" will convince the CAML query to work:

      <FieldRef Name='TestField' LookupId='True' />

This also works when dealing with front end designed views - but those need to be adapted in SharePoint Designer. There is no other way to add this to the CAML query used in the view. Oh and one other thing: Anytime somebody edits a view modified like this in the front end the above mentioned statement needs to be added manually again - even if no changes are made to the view in the front end.

share|improve this answer
This also works with user/"People Picker" columns (e.g. AssignedTo) which I guess can be thought of as a special kind of Lookup column. – Benjamin Warren Feb 23 at 1:28

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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