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.
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
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?
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?
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.
