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?
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.
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?)
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.
[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
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.