Searching for blank fields
March 31 2006 08:00:00 AM
Add/Read Comments [34]
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.
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.
