Importing Email Addresses

Overview

Recommended approaches when Importing email addresses.

Importing via API

Sugar comes out of the box with an API that can be called from custom applications utilizing the REST interface. The API can be used to mass create and update records in Sugar with external data. For more information on the REST API in Sugar, please refer to the Web Services documentation.

Importing New Records

When importing new records into Sugar through the API, modules with relationships to email addresses can utilize the email1 field or the email link field to specify email addresses for a record.

Email1 Field

When using the email1 field, the default functionality is to import the email address specified as the primary address. Assuming the email address does not already exist in the database, the email address is then flagged as being valid and is not opted out. Using the /<module> POST endpoint, you can send the following JSON payload to create a contact record with a primary email address using the email1 field:
POST URL: http://<site url>/rest/v10/Contacts

  {
    "first_name":"Rob",
    "last_name":"Robertson",
    "email1":"rob.robertson@sugar.crm"
}

Note: For importing multiple email addresses, you will need to use the email link field described below.

Email Link Field

When using the email link field, you can specify multiple email addresses to assign to the record. You may specify the following additional information regarding each email address being added:

• invalid_email : Specify this email address as being invalid
• opt_out : Specify this email address as being opted out
• primary_address : Specify this email address as the primary

Using the /<module> POST endpoint, you can send the following JSON payload to create a contact record with multiple email addresses using the email link field:
POST URL: http://<site url>/rest/v10/Contacts

  {
   "first_name":"Rob",
   "last_name":"Robertson",
   "email":[
      {
         "email_address":"rob.robertson@sugar.crm",
         "primary_address":"1",
         "invalid_email":"0",
         "opt_out":"0"
      },
      {
         "email_address":"rob@sugar.crm",
         "primary_address":"0",
         "invalid_email":"0",
         "opt_out":"1"
      }
   ]
}

For more information on the /<module>/:record POST endpoint, you can refer to your instance's help documentation found at:

http://<site url>/rest/v10/help

Or you can reference the <module> POST PHP example.

Updating Existing Records

When updating existing records in Sugar through the API, modules with relationships to email addresses can also utilize the email1 field or the email link field to specify email addresses for a record.

Email1 Field

When using the email1 field, the default functionality is to replace the existing email primary address. Assuming the email does not already exist in the database, the new email address is flagged as being valid and is not opted out. Using the /<module>/:record PUT endpoint, you can send the following JSON payload to update a contact records primary email address:

PUT URL: http://<site url>/rest/v10/Contacts/<record id>

  {
    "email1":"rob.robertson@sugar.crm"
}

Note: This will replace the current email address on the record with the new data. The old email address will no longer be associated with the record.

Email Link Field

When using the email link field, you can specify multiple email addresses to update the record with. You may specify the following additional information regarding each email address being added:

• invalid_email : Specify this email address as being invalid
• opt_out : Specify this email address as being opted out
• primary_address : Specify this email address as the primary

Using the /<module>/:record PUT endpoint, you can send the following JSON payload to update a contact record with multiple email addresses:
PUT URL: http://<site url>/rest/v10/Contacts/<record id>

  {
   "email":[
      {
         "email_address":"rob.robertson@sugar.crm",
         "primary_address":"1",
         "invalid_email":"0",
         "opt_out":"0"
      },
      {
         "email_address":"rob@sugar.crm",
         "primary_address":"0",
         "invalid_email":"0",
         "opt_out":"1"
      }
   ]
}

For more information on the /<module>/:record PUT endpoint, you can refer to your instance's help documentation found at:

http://<site url>/rest/v10/help

Or you can reference the <module>/:record PUT PHP example.

Importing via Database

When importing records into Sugar directly via the database, it is important that you understand the data structure involved before loading data. Email addresses are not stored directly on the table for the module being imported in, but are related via the email_addr_bean_rel table.

The table structure for email addresses can be seen from the database via the following SQL statement:

  SELECT
 email_addr_bean_rel.bean_id,
 email_addr_bean_rel.bean_module,
 email_addresses.email_address
FROM email_addr_bean_rel
INNER JOIN email_addresses
 ON email_addresses.id = email_addr_bean_rel.email_address_id
 AND email_addr_bean_rel.deleted = 0
WHERE email_addresses.deleted = 0;

Checking for Duplicates

Email addresses can become duplicated in Sugar from a variety of sources including API calls, imports, and from data entry. There are a few ways to have the system check for duplicate contact records, but not many of those methods work for checking email addresses for duplicates. The following section will demonstrate how to find and clean up duplicate email addresses using SQL.

The following SQL query can be used to locate if any email addresses are being used against more than one bean record within Sugar:

  SELECT
 email_addresses.email_address,
 COUNT(*) AS email_address_count
FROM email_addr_bean_rel
INNER JOIN email_addresses
 ON email_addresses.id = email_addr_bean_rel.email_address_id
 AND email_addr_bean_rel.deleted = 0
WHERE email_addresses.deleted = 0
GROUP BY email_addresses.email_address
HAVING COUNT(*) > 1;

Note: If you convert a Lead record to a Contact record, both the Lead and the Contact will be related to the same Email Address and will return as having duplicates in this query. You can add the following line to the WHERE clause to filter the duplicate check down to only one bean type:

  AND email_addr_bean_rel.bean_module = 'Contacts'

Email addresses can not only be duplicated in the system but can occasionally be missing critical data. Each bean record with an email address assigned to it should have an email address designated the primary. The following query will locate any bean records that have at least one email address, where there is not an email address designated as the primary:

  SELECT
 email_addr_bean_rel.bean_module,
 email_addr_bean_rel.bean_id,
 COUNT(*) AS email_count,
 COUNT(primary_email_addr_bean_rel.id) AS primary_email_count
FROM email_addr_bean_rel
LEFT JOIN email_addr_bean_rel primary_email_addr_bean_rel
 ON primary_email_addr_bean_rel.bean_module = email_addr_bean_rel.bean_module
 AND primary_email_addr_bean_rel.bean_id = email_addr_bean_rel.bean_id
 AND primary_email_addr_bean_rel.primary_address = '1'
 AND primary_email_addr_bean_rel.deleted = '0'
WHERE email_addr_bean_rel.deleted = '0'
GROUP BY email_addr_bean_rel.bean_module,
 email_addr_bean_rel.bean_id
HAVING primary_email_count < 1;

Removing Duplicates

If it is determined you have duplicate email addresses being used in your system, you can use the following query to clean up the records:

  START TRANSACTION;
CREATE 
 TABLE email_addr_bean_rel_tmp
SELECT
 *
FROM email_addr_bean_rel
WHERE deleted = '0'
GROUP BY email_address_id,
 bean_module,
 bean_id
ORDER BY primary_address DESC;
TRUNCATE TABLE email_addr_bean_rel;
INSERT INTO email_addr_bean_rel
 SELECT
 *
 FROM email_addr_bean_rel_tmp;
SELECT
 COUNT(*) AS repetitions,
 date_modified,
 bean_id,
 bean_module
FROM email_addr_bean_rel
WHERE deleted = '0'
GROUP BY bean_id,
 bean_module,
 email_address_id
HAVING repetitions > 1;
COMMIT;