Is there any field for the body of the email?
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_enteredFROM emailsLEFT join emails_textON emails.id = emails_text.email_id WHERE ((1=1)) AND emails.deleted=0
SELECT IFNULL(emails.name,'') emails_name,emails_text.description,emails.date_entered emails_date_enteredFROM emailsLEFT join emails_textON emails.id = emails_text.email_id
WHERE ((1=1)) AND emails.deleted=0
Hope this helps.
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.
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
We've also encountered a similar road block on 126.96.36.199. 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 emailsLEFT JOIN emails_textON emails_text.email_id = emails.idWHERE 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.
Retrieving data ...