Banner for top of my blog

LinkedIn Twitter Flickr Email Alan
Socialtext Website Socialtext Blog Videos on Youtube Customer Community Developer Community Socialtext on Twitter Socialtext Help on Twitter
Planet Lotus Taking Notes Podcast OpenNTF Bleed Yellow IdeaJam Lotus User Groups YellowCast Developers Podcasts Lotus devWorks Notes 6/7 Forum Notes 8 Forum
By Category By Month

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.

Image:Searching for blank fields

Many thanks to my IBM colleague John Curtis for telling me about this "undocumented" search feature.