Writing a Sugar report on email module.

Kelly,

If you have SugarCRM Enterprise or Ultimate editions and are familiar with writing SQL queries, you can create a query that joins the email body into the report.  

For example, create a standard email type report from the Reports module, when editing the report, in the "Report Details" section check the "Show Query" checkbox and click "Preview". 

This will display the SQL query of the report at the bottom of the page.

You can then customize this query to add a join statement to the email_text table that contains the body of the email.

Click here more information on the database tables and fields.

Click here for information on Advanced Reporting.

Here is a basic example of a query that pulls in the email subject, description, and date created data:

SELECT IFNULL(emails.name,'') emails_name
,emails_text.description
,emails.date_entered emails_date_entered
FROM emails
LEFT join emails_text
ON emails.id = emails_text.email_id

WHERE ((1=1))
AND emails.deleted=0

Hope this helps.

Lori

Hello Lori!

We've also encountered a similar road block on 7.9.2.0. In that, we're trying to filter on the related emails_text table > 'from_addr' column.

We're able to check the 'show query' option, and that does display the query, but we're unable to edit the query behind the report. Was the option to directly the edit the 'show query' removed in later versions?

Here is another example of using SQL to pull in additional fields.

SELECT
  emails.date_entered 'Date Created',
  emails_text.from_addr 'From',
  emails_text.to_addrs 'To',
  emails_text.cc_addrs 'CC',
  emails_text.description 'Body'
FROM emails
LEFT JOIN emails_text
ON emails_text.email_id = emails.id
WHERE emails.date_entered > '2018-11-01 00:00:00';

When writing/modifying this SQL, it can help to have a local copy of the instance database to test with. This is easy to if you are hosting the database yourself. If not and are a Sugar Cloud customer, you are able to download a copy of the database backup for local testing by opening a support ticket. Support will then make the backup available as a download from within the Backup module of the cloud instance itself.

I apologize for resurrecting a 4 year old post, but I'm having this same issue. We am attempting to extract 700,000+ emails from SugarCRM Professional 10.1.0. I am using Excel because it's what I have access to that I know. I've never touched SQL before I started this process but have some VB.net knowledge, so I understand some programming mechanics. When I attempt to get data via ODBC with either of the snippets inserted into the SQL Statement box, I receive the following error: Details: "ODBC: ERROR [HY000] [Devart][ODBC][Sugar]Object 'emails' does not exist"

I've compared the code to the schema file on Sugar's site for 10.1.0 and I cannot see the error apidocs.sugarcrm.com/.../index.html. Can someone help? 

Hi Heather Wright, 

This seems to be an issue with the ODBC connection - which database are you using? Are you able to connect to the DB and run queries apart other than emails?

Also, I'm not sure if Excel would be able to handle 700k emails in a single file. Can you describe your use case for extracting those many emails?

One option is to use your SQL Query Editor itself to export the data for you. If you're using MySQL for example, you can use a free tool provided by MySQL called "MySQL WorkBench"

If you're using SQL Server, the Studio would be able to handle this for you as well.

Hi Heather Wright, 

This seems to be an issue with the ODBC connection - which database are you using? Are you able to connect to the DB and run queries apart other than emails?

Also, I'm not sure if Excel would be able to handle 700k emails in a single file. Can you describe your use case for extracting those many emails?

One option is to use your SQL Query Editor itself to export the data for you. If you're using MySQL for example, you can use a free tool provided by MySQL called "MySQL WorkBench"

If you're using SQL Server, the Studio would be able to handle this for you as well.

Honestly the use case is that I know how to use excel and can figure out the get data functions on it. I downloaded MySQL Express and attempted to use it, but I was lost. I could not figure out exactly how to connect MySQL to the database. 

The connection does seem to be working. I was able to pull 8900 client files with no issue. My plan with the emails was to get the provided code working, then tweak it to break the extracts down into six month chunks to make them more manageable. 

Ah okay, I was just trying to understand your requirement to provide an appropriate  answer .. Good idea to extract emails in 6 months chunk 

Since you mentioned that the connection seems to be working, are you able to query for "accounts" table, for example: Can you try running this query and see if it works for you - (limiting to 5 records for testing)

select * from accounts limit 5;

I am not. It says object accounts does not exist, but I ran an extract yesterday and pulled all accounts. I checked the ODBC 64-bit driver settings and it says connection successful on the SugarCRM Connection settings.  

It's possible I'm using the wrong process for pulling them. I'm teaching myself on the fly. The process I've been using is this: 

1. Open and save a new Excel File. 
2. Get Data > From Other Sources > From ODBC.
3. Select Sugar CRM and insert SQLstatement under advanced options.
4. Hit ok. This is when the error displays. 

I did just successfully open and refresh the accounts query from yesterday. 

Interesting. If you're able to query accounts, it should ideally work the same for emails as well - Maybe the same temporary issue that you faced with accounts is causing the issue with emails as well.

Can you try a simple query for example:

select * from emails limit 5;

or

select * from emails_text limit 5;

If not, can you try running a manual query in your Excel's Microsoft Query and see if you are able to see results from running the queries above

I connected to the table through the wizard and I am able to see records (partially covered window). I then created a new query not using the wizard and input each snippet as SQL, but neither worked. The errors were the same on each: 

(I hit OK)

My workday is almost over, but I'll be chasing this again on Monday and will check back in then. Thank you very much for your help.

Some specifics: I need to export all emails with their full content, including the body text. I also need to add in the SugarID for the account with which the email is associated, the same ID that shows up in the address bar when viewing a client. As I extract, I'll need to limit the date range within the coding and run multiple queries to have better control over the size of the downloads. I'm downloading 9 years of data, 766530 emails. 

Interesting, I had the exact same popup as you had and I didn't get the "Could not add the table" error. Maybe try to query for specific fields and see if that works?

select id, name from emails;

If the Microsoft Query is working, then you could probably use that to insert data into excel instead of using Saved Queries? Apologies, unfortunately, this is something related to Excel and I have no idea why it doesn't work. 

The only alternative that I can suggest is to use a Visual Query Editor tool like MySQL Workbench where you can create a simple DB connection and start running queries out of it. It can also export data to Excel/variety of other formats.

It's a simple visual tool provided by MySQL itself: https://dev.mysql.com/downloads/workbench/

And you just need few seconds to connect to a DB and run queries:

Once you run the query, you can click on "Export" to save it as Excel/CSV etc.

Thank you! I have some things I have to complete first, but I'll circle back to this later. Would you be able to help me by tweaking the code to insert the account id and the date parameter field? I'm certain you can do it in a fraction of the time it would take me to look up the parameter specifics and make the changes myself.

Sure, happy to help.

If you're looking at email contents, emails_text is the table that you need. It has From/To/Email Body

Subject comes from emails.name

If the emails are sent from Accounts module, the link is established in a table called "emails_beans", with bean_module = 'Accounts'.

Finally, use the date_sent in emails table to filter when this email was actually sent.

So combining all three, the query that you need is something like:

select eb.bean_id as account_id,
       e.id as email_id,
       e.name as subject,
       e.date_sent as date_sent,
       et.from_addr,
       et.to_addrs,
       et.cc_addrs,
       et.description -- Use this or description_html
from emails e,
     emails_beans eb,
     emails_text et
where e.id = eb.email_id
  and e.deleted = 0
  and eb.deleted = 0
  and e.id = et.email_id
  and et.deleted = 0
  and eb.bean_module = 'Accounts'
  and e.date_sent between '2020-01-01' and now();

Thank you! I'll try this later. I really appreciate your help. 

Now I need to add SQL to my list of things to teach myself.