Ability to search for and import blank fields

[TLange]

My two feature requests are the ability to:

(1) Search for blank fields
(2) Blank out fields on import

For instance, right now when we export our entire database we see we have a number of contacts that have no first or last names.  But I can't figure out a way to do a search for them.  It would be great to be able to specifically search for "" or <blank> in specific fields or some other way to say that we're looking to return all contacts that have blank values in specific fields.

We also need the ability to blank out a field on import.  For instance, sometimes a contact has an address in the data field, and then we later, after we clean the address information with an address cleaning service, we'll have several hundred contacts whose addresses we need to blank out because they're no longer valid (or maybe never were).  But now on import if we leave a field blank the importer assumes that we don't have data for that field, instead of that the data we actually have is "blank".  We'd like to be able to use a special signifier like "<blank>" or something on importing that would mean that the field should be blanked out (as opposed to being ignored).

Of course if either of these problems already have a solution then that would be great, too!

Thanks,

- Trent

[Dave Greenberg]

Searching for blank fields is not possible with the current version "out of the box". We're working on a framework to support custom search forms / queries for 2. - http://civicrm.org/node/254. In the meantime, your choices are:
 *  try the "smart group" hack described here: http://forum.osuosl.civicrm.org/index.php/topic,1510.0.html
 * install BIRT and use the sample CiviReport files to get you started on creating a custom report which retrieves the contacts who are missing field values

I think that Import Contacts SHOULD clear out values in a mapped field IF you select Update as your Duplicate Handling option. If you can recreate this NOT happening on our demo site (once it's up on our new servers - probably tomorrow) - please file an issue in the issue tracker for 2.0 fix.

[Denver Dave]

Yes, I just realized that I did not know how to search for blank fields - this would be very useful.  At present, I'm having to download the field for all contact records and deal with them in Excel, but I'm always discovering more blanks.

[greenmachine]

The functionality of importing blank field values to delete existing values within those fields for existing contacts does not seem to work/exist. I have tested this on my own copies of 1.9x, 2.0x, and also the 2.1 Drupal demo server. I do not see a corresponding issue in the Issue Tracker, though I am not sure how to search for this correctly. My steps:

-- set matching rule to use Contact ID
-- create an individual with a custom field value
-- use export to get a CSV with the contact's CID, name, custom field, and job title
-- modify the CSV to delete the value in the custom field column, and change the value in the job title column
-- use import with "update" selected
-- result: the contact's job title value is changed to the new value, but the custom field value is still in place (not deleted per the blank imported value)

This post was started a long time ago - was this feature discarded intentionally, not followed up on by the user, just not a priority, etc?

[Donald Lobo]

there is an issue here: http://issues.civicrm.org/jira/browse/CRM-2980

i suspect we'll tackle it when we upgrade import (in a 2.x / 3.x release). I'm not sure if we will work on import in the next release, so the issue might get deferred. Its a non-trivial issue (IMO)

lobo

[greenmachine]

Thanks for the response. I can see how some users might expect update to only update where there is explicit info in the incoming CSV file, so yeah, not trivial.

[Andrew Perry]

In light of Jun's comment today over at http://issues.civicrm.org/jira/browse/CRM-2980?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=34627#action_34627 - perhaps this is an opportunity for a Make-it-Happen!

It would certainly make it easier for me to be able to point the client/s I have with this problem at a page that enables them to pay to fix it!

Andrew

[Dave Greenberg]

Andrew, I don't know enough about the import code to know if Jun's suggestion makes sense / how big an effort it would be. Sounds like this is a pressing issue for some of your clients so perhaps raise it up w/ Kurund or Lobo on IRC??