Are Non-Primary Email Addresses Reportable?

Following the instructions in the link below, we are able to use the Primary Email Address in reports linked to a record. 

http://support.sugarcrm.com/04_Knowledge_Base/03End_Users/100Reports/Including_Email_Addresses_in_Re...

However, we'd like the ability to report both Primary, and Non-Primary email addresses linked to a record in Sugar Reports (version 7)

Is this possible, and if so, what documentation may we provide our clients in order to do so?

Thanks
Parents
  • It's been over 9 years since this question was originally posted. Has there been any new development in this area to allow reporting on non-primary emails?

    if I manually export a record, I get a column with "Non-primary emails". I want to include that column in a report to verify ALL the emails we have for our people/contacts. But this column doesn't seem to be an option to select when creating reports.

    We need to verify that all the emails (including non-primary emails) were migrated properly.

  • Hi  ,

    Unfortunately the option to report on non-primary email addresses on standard reports is not available.

    There is an internal Idea documented (#68555) which you could ask to get escalated via our Support Portal.

    In the meantime, you could get that information with a Custom Query in the Advanced Reports section (Reports -> Advanced Reports).

    The query would look something like this for the Contacts module:

    SELECT c.id AS contact_id, c.first_name, c.last_name, primary_emails.email_address AS primary_email, GROUP_CONCAT(non_primary_emails.email_address SEPARATOR ';') AS non_primary_emails 
    FROM contacts c 
    LEFT JOIN email_addr_bean_rel eabr_primary 
    ON c.id = eabr_primary.bean_id 
    AND eabr_primary.bean_module = 'Contacts' 
    AND eabr_primary.primary_address = 1 
    LEFT JOIN email_addresses primary_emails 
    ON eabr_primary.email_address_id = primary_emails.id 
    LEFT JOIN email_addr_bean_rel eabr_non_primary 
    ON c.id = eabr_non_primary.bean_id 
    AND eabr_non_primary.bean_module = 'Contacts' 
    AND eabr_non_primary.primary_address = 0 
    LEFT JOIN email_addresses non_primary_emails 
    ON eabr_non_primary.email_address_id = non_primary_emails.id 
    GROUP BY c.id, c.first_name, c.last_name, primary_emails.email_address 
    ORDER BY c.last_name, c.first_name;
    

    If you then create a Custom Data Format and use it to run your query, you will get something like this (image from a demo environment):

    I hope it helps but let me know if you have additional questions.

    Best,

    Francesc del Moral

  • Thank you  .  Would it be possible to add the Opted Out and Invalid Email indicator to this query For both the Primary and non primary emails?

    So it looks somewhat like the manual export?

    We're trying to verify over 500k emails from our migration process and that will help tremendously.  

Reply Children
  • Hi  , 

    for that result, your query would look like this:

    SELECT 
        c.id AS contact_id,
        c.first_name,
        c.last_name,
        primary_emails.email_address AS primary_email_address,
        IFNULL(primary_emails.opt_out, 0) AS primary_opt_out,
        IFNULL(primary_emails.invalid_email, 0) AS primary_is_valid,
        GROUP_CONCAT(CONCAT(non_primary_emails.email_address, ',', IFNULL(non_primary_emails.opt_out, 0), ',', IFNULL(non_primary_emails.invalid_email, 0)) SEPARATOR ';') AS non_primary_emails
    FROM 
        contacts c
    LEFT JOIN 
        email_addr_bean_rel eabr_primary 
        ON c.id = eabr_primary.bean_id 
        AND eabr_primary.bean_module = 'Contacts' 
        AND eabr_primary.primary_address = 1
    LEFT JOIN 
        email_addresses primary_emails 
        ON eabr_primary.email_address_id = primary_emails.id
    LEFT JOIN 
        email_addr_bean_rel eabr_non_primary 
        ON c.id = eabr_non_primary.bean_id 
        AND eabr_non_primary.bean_module = 'Contacts' 
        AND eabr_non_primary.primary_address = 0
    LEFT JOIN 
        email_addresses non_primary_emails 
        ON eabr_non_primary.email_address_id = non_primary_emails.id
    GROUP BY 
        c.id, c.first_name, c.last_name, primary_emails.email_address, primary_emails.opt_out, primary_emails.invalid_email
    ORDER BY 
        c.last_name, c.first_name;



    Here is an example of a Contact with 3 email addresses: 1 primary and 2 non-primary. In the 2 non-primary, one is opted-out and the other is marked as invalid.

    When I run the query, these are the results I get:


    I hope this is helps Slight smile

    Francesc del Moral

  • Thanks for the feedback  ! Happy to hear this is what you needed.