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!
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.
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 none@none.com
DELETE FROM civicrm_email WHERE email='none@none.com';
(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 none@none.com using javascript (works from browser console if logged in with appropriate rights).
CRM.api3('Email', 'get', {
"sequential": 1,
"email": "none@none.com",
"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.
-
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? – Chris Burgess Feb 8 '17 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 "none@none.com" 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. – Peter Dodge Feb 8 '17 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. – Chris Burgess Feb 9 '17 at 18:21
-
Fun fact:
none@none.comis 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'sexample.org,example.com,example.netand friends, but these are a small list of specifically reserved domains. – Chris Burgess Feb 9 '17 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 '19 at 0:44
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:
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
