4

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.

<Where>
   <IsNull>
      <FieldRef Name='TestField' />
   </IsNull>
</Where>

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

Improve this question
4
  • 1
    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"
    – Sergei Sergeev
    Mar 24, 2013 at 19:54
  • Seems to be the same as stackoverflow.com/questions/1341289/…
    – Dave Paylor
    Mar 24, 2013 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, 2013 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, 2015 at 13:15

2 Answers 2

Reset to default
2

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

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

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.

Improve this answer
2
  • 2
    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, 2016 at 1:28
  • This should be the accepted answer. Thanks for the reminder. It makes no sense, but this issue is still prevalent in the latest versions of SharePoint. Hope that changes in 2019.
    – Dinerdo
    Aug 26, 2018 at 12:53
0

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.
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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