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

Dear Lori,

Yes I have a colleague that is great with SQL queries and so I can ask him to assist me.

Thank you for your assistance.

Kind Regards,

Kelly Turner

Kelly Turner

Solutions Support Consultant

Phone: 01202 308000 | Support: 01202 308001 | Fax: 01202 308002

Enhanced, Unit 3 New Fields Business Park, Stinsford Road, Poole, Dorset, BH17 0NF

<https://www.linkedin.com/company/389779?trk=tyah>   <https://twitter.com/EnhancedPoole>    <https://plus.google.com/u/0/+EnhancedCoUk/posts>    <https://www.youtube.com/user/EnhancedTechnology1>

This e-mail (and any attachments) is intended only for the above addressee. It may contain confidential and or privileged information. If you are not the addressee you must not copy, distribute, disclose or use any of the information in it. If you have received it in error please delete it and immediately notify the sender. Any opinions or views expressed in this message are those of the individual sender, except where the sender specifically states them to be otherwise.

Registered in England. Company Number: 02910340

Registered Office: 37 Commercial Road, Poole, BH14 0HU

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?

Hi Timothy Tran,

There is not a way to edit the query shown in a standard report within the application.

Lori's advice above about Advanced Reports involves copying the shown query into a local text editor, editing it to address your need, then pasting it into a Custom Query record in the Advanced Reports section of Sugar for use in an Advanced Report.

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.

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.