You can find documentation about on KQL (Keyword Query Language) used in SharePoint and Office 365, but there’s a question I still get asked very often from my customers: "How can I filter for empty values in Search?"
Some use cases when this would be useful:
- Filtering for tasks that have no Due Date
- Filtering for tasks assigned to nobody
- Filtering for documents where Customer is empty
As SharePoint doesn’t store the empty values in the index, we cannot run a query searching for “NULL” values. Instead, we have to do some workarounds.
Let me explain by using a very common use case as an example: let’s build a query that returns tasks with no Due Date.
Query for tasks with no Due Date
The first step is searching for tasks, which is pretty obvious. Our query is:
You can see, in my demo environment, I have six tasks in the result set.
Next, let’s search for a specific Due Date: November 30, 2014. The query is:
The syntax to use here is: YYYY-MM-DD.
You can see there’s only one task due on November 30, 2014.
Next, let’s say we want to see the tasks that are due in a time range, for example, between January 1 and December 31, 2014. Our query has a time range now:
If we want to use a range in our date query, we have to use ‘..’ between the dates.
Let’s see how to filter for the tasks where the Due Date is out of this range. Actually, there are two syntaxes we can use here. First, we can use the NOT operator, or in its short form:
From this point, there’s only one small tricky step missing to achieve our original goal: let’s set the date range big enough to make sure we cover all the tasks with any Due Date. First, our inclusive query will be like this:
With this, we cover all the tasks in our search index. The last step is to exclude all of these tasks with any Due Date in our very wide date range:
The result is: we get all the tasks where Due Date is not in our “big enough” date range – which means tasks with either no Due Date at all or some very invalid Due Date values. In my demo environment, I have three of them, as the next figure shows.
Query for empty values in other data types
For tasks, the problem is solved. You might ask, “What about other value types?”
Numbers can be managed in a similar way—we can enter ranges by using ‘..’ between. If you want to search for empty numeric values, just use an exclusive query (NOT) with a “big enough” date range.
For text values the workaround is even trickier. Let me demonstrate this with an example using a custom field, namely "Customer"—let’s search for documents where Customer is empty.
First, we have to identify in the query how to include everything that has any value in Customer. Text values consist of alphabetical characters (a..z, A..Z) and numerical characters (0..9). For making our example as simple as possible, let’s say this covers all of the Customer values (e.g., no Customer names with special characters only).
The problem is, we cannot use the range operator ‘..’ in text properties as we did for dates and numeric values. There’s no query available like this: Customer:(a..z)*
Instead, we have to include these Customer names one by one, something like this:
Customer:a* OR Customer:b* OR … OR Customer:z* OR
Customer:1* OR Customer:2* … OR Customer:9* OR Customer:0*
The good news is that by default, queries are not case sensitive, so we don’t have to do this again to include Customer names starting with capital letters.
Let’s extend our query now, and filter for documents only:
IsDocument:true AND (Customer:a* OR Customer:b* OR …
OR Customer:z* OR Customer:1* OR Customer:2* …
OR Customer:9* OR Customer:0*)
To do the exclusive query, we have to use the NOT operator again. For your reference, I'm inserting the whole query here:
IsDocument:1 AND NOT(Customer:a* OR Customer:b* OR Customer:c* OR Customer:d* OR Customer:e* OR Customer:f* OR Customer:g* OR Customer:h* OR Customer:i* OR Customer:j* OR Customer:k* OR Customer:l* OR Customer:m* OR Customer:n* OR Customer:o* OR Customer:p* OR Customer:q* OR Customer:r* OR Customer:s* OR Customer:t* OR Customer:u* OR Customer:v* OR Customer:w* OR Customer:x* OR Customer:y* OR Customer:z* OR Customer:1* OR Customer:2* OR Customer:3* OR Customer:4* OR Customer:5* OR Customer:6* OR Customer:7* OR Customer:8* OR Customer:9* OR Customer:0*)
This final query is not really user friendly and the query is very long, but in pre-defined scenarios it can be used to collect documents with empty text values, in order to fix them and improve our information architecture.