Searching Null or Blank Fields

[Bill Scheurer]

We have not found any success in searching for records without data in important fields.

For example, we would like to easily tag or create a smart group for people without an email or mailing address. None of the search functions -- !=, =, IN, NOT IN, LIKE, NOT LIKE -- seem to deliver this.

A previous post by administrator Lobo said that leaving the data field blank would work: http://forum.civicrm.org/index.php/topic,297.msg1137.html#msg1137 -- but that did not do it either.

We also tried to use the groups and tags functions to work around it. For example, we created a smart group that includes every record that does have an email address, and we also tagged it. But, we could not find a way to search for people who were not in that group or did not have that tag. The != and NOT IN and NOT LIKE functions work a bit differently.

We were hopeful about the tags, but that function required us to enter the tag "ID" -- which we cannot seem to find anywhere (unlike groups, which have a disclosed ID number).

So, does anyone know how to do this? It would seem to be one of the most basic and important tasks.

[Donald Lobo]

We hope to make search more flexible and extensible in a 2.x release. We do agree that not having the full power of SQL in creating and managing groups is a limitation in the current model.

You can get the tag id, by hovering on the link and checking the url, typically the id is denoted with the GET parameter id=XXX

lobo

[Bill Scheurer]

Thanks!

I am now able to find the tag id, but still no way to find contacts who do not have that tag. None of the negative operators -- !=, NOT IN, NOT LIKE (with or without wildcard) -- delivered any results.

This should be a simple 3-step SQL operation -- 1) build a temp_tag table with everyone that has the tag id; 2) build a temp_add table of everyone IN the contacts table and NOT IN the temp_tag table; 3) tag the contacts table with everyone IN the temp_add and contacts table. I don't understand why this is not there?

Is there any way to get at the mySQL> command line prompt for my data (we are on the CivicSpaceLabs host)? That way, we could do this kind of thing (as well as find NULL values, per my other recent post).

[Donald Lobo]

Integrating general SQL with an automated SQL generating system (which search is), is not a trivial project (IMO). If you think it is, please do submit a patch to make it happen. We hope to address it in our search rewrite planned for 2.x, but would be great for someone to tackle the problem earlier (makes it easier for us)

You will need to chat with the CSOD folks to get mysql command line access. You could integrate your mysql queries and CiviCRM using import/export and/or some custom code

lobo

[Bill Scheurer]

Until the CiviCRM/mySQL implemenation supports NULL searches, here is our "clever little monkey" workaround:

1) Create a Y/N custom field (as a kind of flag) for each field that we want to search/group by NULL values -- such as contacts without email addresses.

2) Batch update ALL contacts to the "N" value in that field, even those who are not NULL -- such as contacts that DO have email addresses (it sure would be great to find a way for CiviCRM to allow more than 100 batch updates at a time!**).

3) Create a smart group for everyone that is not NULL -- eg., those that have email addresses (we use the wildcard "%" value to find them).

4) Batch update these people to set the value to "Y" in the flag field.**

5) Create a smart group for everyone who still has a "N" value in the flag field.

For new contacts, set the default value to "N" in the flag field, to be able to skip steps 1-2 above. Also, when importing, you can set the Y/N values correctly before, to skip steps 1, 2 & 4 above.

** Maybe a clever way to batch update more than 100 is to reimport stripped down records with the "fill" function for duplicates? That may be a way to clean things up without the manual steps? I don't see why not.

[GingerFeet]

Hello All,

Yes, I know it is a topic from long ago, but it is more relevant to add to it than to start a new one.

Working with the tags "called" and "not called". When searching for tags "not called", the results did not add up to total number of records. Realized that a search for a tag cannot handle NULL (in our 3.3.5).  Just wouldn't do it.

So, after finding Bill's post, did a variation on Bill's theme:

• Tagged all records "not called"
• Searched for all records "called" and untagged the "not called" tag from those records

Thanks,

GF