March 31st, 2006
Searching for blank fields
Today's tip is a little advanced, but for
those of you that can take advantage of it, you'll find it priceless!
Normally when you search a database you are trying to find a specific phrase,
or a name, or some other value. But have you ever wanted to do the
reverse, and search for the documents where a field is blank? For
example, perhaps you have a CRM database and you want to see which documents
are missing email addresses. Maybe you have an order tracking application
and you need to find documents where the zip code is blank. Or perhaps
you have a HelpDesk system and you need to see which documents don't currently
have a support analyst assigned to the problem.
The answer is to use the search term "NOT [fieldname] is present",
of course substituting "fieldname" with the actual name for your
application.
For example, searching in my Personal Address Book for "NOT [mailaddress]
is present" allows me to find all the contacts I don't have email
addresses for.
Many thanks to my IBM colleague John Curtis for telling me about this "undocumented"
search feature.
