Exporting Emails via the Database

    Overview

    There may be occasions when you wish to export emails from your Sugar® instance. While the export action cannot be performed via Sugar’s user interface, you can export emails by running a SQL query directly against your instance’s database.

    Use Case

    As an example, we will run a query to retrieve all emails from Sugar as well as all emails related to a specific module. If you are using a database management tool (e.g. phpMyAdmin), the results can be exported. Alternatively, they can be stored in a new table on your database. The example queries are for MySQL databases and may need to be adjusted for instances using an Oracle, DB2, or Microsoft SQL Server database.

    Prerequisites

    • You must have access to Sugar’s database via command-line interface or a data management tool such as phpMyAdmin.
    • You must be comfortable with SQL queries and the Sugar database.
      Note: If you need assistance running the SQL query and already have a relationship with a Sugar partner, you can work with them to perform the necessary actions. If not, please refer to the Partner Page to find a reselling partner to help with your development needs. 

    Steps to Complete

    Retrieving All Emails

    Use the following query to export all emails from your Sugar instance:

    SELECT emails.id, emails.name as Email_Subject, txt.description as Email_Text, emails.date_sent, 
    txt.from_addr as From_Email_Address, txt.to_addrs as To_Email_Address,
    GROUP_CONCAT(DISTINCT bean.bean_module SEPARATOR ',') as Module_Attached_To ,
    COUNT(DISTINCT bean.bean_module) as NumOfModulesAttached
    FROM emails_text txt
    INNER JOIN emails_beans bean ON txt.email_id = bean.email_id
    INNER JOIN emails ON emails.id= txt.email_id
    GROUP BY emails.id
    ORDER BY emails.date_sent DESC

    The query will return information about the emails including their text and the modules of the records to which each email is related. Please note that is will not return the actual record(s) to which the email is associated.

    output of query for exporting emails

    Retrieving Emails by Module

    You can also export emails that are related to a particular module (e.g. Accounts or Contacts), but the query you will use differs for person-type and regular modules. Similar to the query above, it will return information about the emails including their text and the modules of the records to which each email is related. Please note that is will not return the actual record(s) to which the email is associated.

    Person-Type Modules

    Use the query below to retrieve all emails related to a person-type module (i.e. Contacts, Leads, and Targets). For this example, we will retrieve all emails related to the Contacts module. 
    Note: Replace the module name for the third inner join to retrieve emails related to another person-type module (e.g. Leads). 

    SELECT emails.id, emails.name as Email_Subject, txt.description as Email_Text, emails.date_sent, 
    txt.from_addr as From_Email_Address, txt.to_addrs as To_Email_Address,
    bean.bean_module as Module_Attached_To, concat(contacts.first_name,' ',contacts.last_name)
    as Record_Name_Where_Email_is_attached FROM emails_text txt INNER JOIN emails_beans bean ON txt.email_id = bean.email_id INNER JOIN emails ON emails.id= txt.email_id INNER JOIN contacts ON contacts.id = bean.bean_id WHERE bean.bean_module= 'Contacts' GROUP BY emails.id ORDER BY emails.date_sent DESC

    Non-Person Type Modules

    Use the following query to retrieve all emails related to a non-person type module. For this example, we will retrieve all emails related to the Accounts module. 
    Note: Replace the module name for the third inner join to retrieve emails related to another non-person type module (e.g. Opportunities).

    SELECT emails.id, emails.name as Email_Subject, txt.description as Email_Text, emails.date_sent, 
    txt.from_addr as From_Email_Address, txt.to_addrs as To_Email_Address,
    bean.bean_module as Module_Attached_To, accounts.name as Record_Name_Where_Email_is_attached
    FROM emails_text txt
    INNER JOIN emails_beans bean ON txt.email_id = bean.email_id
    INNER JOIN emails ON emails.id= txt.email_id
    INNER JOIN accounts ON accounts .id = bean.bean_id
    WHERE bean.bean_module= 'Accounts'
    GROUP BY emails.id
    ORDER BY emails.date_sent DESC  

    Application

    If you are using a data management tool (e.g. phpMyAdmin), you can download the query results to your local computer. Alternatively, you can store the results in a new table in your Sugar database by wrapping your query in a CREATE TABLE statement. The following example creates a table using the first query provided in this article:

    CREATE TABLE name_you_want_to_use AS
    (SELECT emails.id, emails.name as Email_Subject, txt.description as Email_Text, emails.date_sent,
    txt.from_addr as From_Email_Address, txt.to_addrs as To_Email_Address,
    GROUP_CONCAT(DISTINCT bean.bean_module SEPARATOR ',') as Module_Attached_To,
    COUNT(DISTINCT bean.bean_module) as NumOfModulesAttached
    FROM emails_text txt
    INNER JOIN emails_beans bean ON txt.email_id = bean.email_id
    INNER JOIN emails ON emails.id= txt.email_id
    GROUP BY emails.id
    ORDER BY emails.date_sent DESC);
    ShawnS.png
    in Import and Export

    Reach out to us for help