3

Is there a way to overwrite a Contact's email address with a null value?

I tried uploading contact info (via: Import Contacts) with a blank email field, but this doesn't override an existing email address.

Thanks!

2 Answers 2

2

A NULL value won't be a valid email address, so you'll struggle to get this into CiviCRM. This is intentional - why store an email that's not valid?

Remove address by contact edit

Correct procedure is to remove the address you want to get rid of. When editing a contact, you should be able to click on the email section, delete the text in the email field, and hit save.

Does that solve your problem? Your question doesn't indicate why you'd want to store a NULL instead of just deleting the relevant email entry.

editing email address

Remove addresses by SQL

Since you say you have a bunch of "bad" addresses, you can remove them via SQL. Accessing the underlying data in CiviCRM may have risks (eg other data might be associated with an address you delete), so this should be done with caution. Here's the SQL command to delete emails [email protected]

DELETE FROM civicrm_email WHERE email='[email protected]';

(If anyone can suggest issues to this approach, please do so in comments?)

Remove addresses via API

You can also use the API - slightly more complex, but you're updating CiviCRM via its proper interfaces and it will be better able to handle the business logic around removing related data.

There's an API explorer in CiviCRM at civicrm/api/explorer, and using the tools there you can compose API commands to do what you want. Example code is given in the API explorer for javascript, PHP etc. For example, to find then remove addresses [email protected] using javascript (works from browser console if logged in with appropriate rights).

CRM.api3('Email', 'get', {
  "sequential": 1,
  "email": "[email protected]",
  "options": {"limit":0}
}).done(function(result) {
  result.values.forEach(function(email) {
    CRM.api3('Email', 'delete', { "id": email.id });
  });
});

You could use the sample PHP provided there instead, via a custom extension or module.

5
  • Ah, maybe you're trying to "import" a batch of email addresses that you want removed? That's an interesting use case - if that's your situation, maybe clarify that as the reason for this and someone might have a clue how else you could achieve what you need to? Feb 8, 2017 at 1:34
  • Thanks, Chris, for the insight! Yes, we are wanting to import a batch of email addresses that we want removed. Probably a long time ago, someone thought they were cleaning our database and put "[email protected]" in any email address that was blank. This is causing one huge mess with deduping/merging as all of these people have the same email address. I'm basically wanting to run a report of anyone with this email address and mass-delete them. I could manually delete out the email as you described in the screenshot, but that's a lot of manual work... I was hoping to mass upload it. Feb 8, 2017 at 15:32
  • Aha ... so yes, overwriting with bad data to obliterate old data is the wrong approach, but you already knew that! Flagging addresses "do not contact"/"bounces" etc in CiviCRM would have given the org better data long term (eg org can't prevent re-addition of do-not-contact addresses if it no longer knows which addresses to not contact). Answer updated with some methods to clean up. Feb 9, 2017 at 18:21
  • Fun fact: [email protected] is a valid email address on a registered domain, so the approach of overwriting with a made-up email could have led to spamming a valid email account, affecting your mail reputation OR even leaking your org's contact data / logins to another site. There are legit domains that can be used for "fake" emails: RFC2606's example.org, example.com, example.net and friends, but these are a small list of specifically reserved domains. Feb 9, 2017 at 18:37
  • We have a scenario where we want to remove an email while doing an import - eg we are needing to enter an email for Location Type x and remove one for Location Type y. Importing NULL would be great. There are tickets about being able to do this for Address fields. Wondering if it seems rational to tweak the 'validate emails' so that it allows NULL to be imported?
    – petednz - fuzion
    Oct 17, 2019 at 0:44
2

Elaborating on Chris's great answer (and based on your clarification of what you're looking for): you can delete all instances of a particular email address directly from the API explorer with chaining; no need to use Javascript or PHP. Navigate to civicrm/api/explorer and enter the following values: enter image description here

Then hit "Execute".

As with any operation that may affect a large number of records, it wouldn't be a bad idea to make a backup of your database first, no matter which one of these methods you use.

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.