5

How could I specify in a Querytext that I want all the elements that doesn't have defined a value for a property?

For example, let's say that I'm interested in the property CheckoutUserOWSUSER, how can I search all the documents where that property is empty?

I'm thinking on maybe: (CheckoutUserOWSUSER:"") but I think is not a correct query

  • I'm using REST api – yngrdyn Jul 13 '15 at 15:38
6

Well I've found a solution, not very friendly but works, is based on this article

So basically the article says that you need to identify in the query how to include everything that has any value:

Text values consist of alphabetical characters (a..z) and numerical characters (0..9) and use a NOT at the beginning to exclude all the possible values... The "good new" is SharePoint isn't case sensitive so you have to do it only once per letter, examples:

If you want to filter, let's say by CheckoutUserOWSUSER as in the original question, by an empty value you have to include this in your querytext:

NOT(CheckoutUserOWSUSER:a* OR CheckoutUserOWSUSER:b* OR CheckoutUserOWSUSER:c* OR CheckoutUserOWSUSER:d* OR CheckoutUserOWSUSER:e* OR CheckoutUserOWSUSER:f* OR CheckoutUserOWSUSER:g* OR CheckoutUserOWSUSER:h* OR CheckoutUserOWSUSER:i* OR CheckoutUserOWSUSER:j* OR CheckoutUserOWSUSER:k* OR CheckoutUserOWSUSER:l* OR CheckoutUserOWSUSER:m* OR CheckoutUserOWSUSER:n* OR CheckoutUserOWSUSER:o* OR CheckoutUserOWSUSER:p* OR CheckoutUserOWSUSER:q* OR CheckoutUserOWSUSER:r* OR CheckoutUserOWSUSER:s* OR CheckoutUserOWSUSER:t* OR CheckoutUserOWSUSER:u* OR CheckoutUserOWSUSER:v* OR CheckoutUserOWSUSER:w* OR CheckoutUserOWSUSER:x* OR CheckoutUserOWSUSER:y* OR CheckoutUserOWSUSER:z* OR CheckoutUserOWSUSER:1* OR CheckoutUserOWSUSER:2* OR CheckoutUserOWSUSER:3* OR CheckoutUserOWSUSER:4* OR CheckoutUserOWSUSER:5* OR CheckoutUserOWSUSER:6* OR CheckoutUserOWSUSER:7* OR CheckoutUserOWSUSER:8* OR CheckoutUserOWSUSER:9* OR CheckoutUserOWSUSER:0*)

So now you are sure that you exclude all the possible values for that field and voilá.

Long and no friendly, so if you have another idea please hit it =)

2

CAML Queries do support checking for Null values (unlike the REST api) and you can use CAML in REST. So you can do something like this:

http://somesharepointsite/_api/web/lists/getbytitle('yourlist')/getitems(query=@v1)?@v1={"ViewXml":"<View><Query><Where><IsNull><FieldRef Name='CheckoutUserOWSUSER'/></IsNull></Where></View>"}

Inspired by this blog.

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.