Is there a way to report on implicitly-linked Contact Emails?

Hi Vince,

I can confirm that unlike Outlook addon, the SNIP service does not create a physical relationship between an email archived and other Sugar entities - AFAIK, Contacts Email subpanel, alike other Emails subpanels, filter Emails records matching from/to fields (maybe more complex condition) to the Contact's email address in order to display on the subpanel - and does that on the fly.
Sometimes this is an advantage - if there is a mailing with some email address that had been archived prior to the Contact is represented in CRM, you find those Emails on the Contact Subpanel too 

Actually, there are 2 options
1) to configure logic that would physically relate the Email record to the Contact record on email is Archived (on data save - I suppose I configured this with Logic Builder some years ago)
2) Express the email address matching criterion Email-to-Contact in the report.
However, I suppose only Advanced Reports capabilities are in a position to help with such a challenge - Advanced Reports allows writing SQL query for selection

Hi Dmytro,

I am having a similar issue where I need the Emails archived explicitly related to the Contact record so it triggers Process Definitions that update some custom Activity fields we use for reporting. Do you have something you can share about the "logic builder" you used? We are using cloud-based Sugar Sell 11.2. I tried making an additional Process Definition, but not sure the functionality is available.

Thanks

What you are seeing is a common problem when reporting on emails.

Emails can be connected to Contacts in two ways:

by the email address:

contacts <-> email_addr_bean_rel <->email_addresses<-> emails_email_addr_rel <-> emails

directly to the Contacts bean:

and contacts <-> emails_beans <-> emails

(See https://support.sugarcrm.com/Documentation/Sugar_Developer/Sugar_Developer_Guide_11.0/Architecture/Email/#Overview )

Every time an email is added it is related to the email addresses involved and the module that it was added on (Contact, Case, Opportunity...)

Emails created on a Contact or related to a Contact are connected with the Contacts module via the yellow path below.
But, if, for example, you write an email to a contact adding an Email from the Emails Subpanel on Opportunities, the yellow connection is between the Email and the OPPORTUNITIES module not the Contact module. 

So one should consider the yellow connection as: What is the email about

And the green-blue connection as: Who is involved in the email (to, from, cc, bcc)

When you build a report starting from Contacts and report on Emails (or vice versa) you are not reporting on the Emails this person is involved in, rather you are reporting on the Emails ABOUT this Contact RECORD. (Which to me, is counterintuitive in the case of People-type records, but may make sense when reporting on, for example Emails related to a Case or an Opportunity)

The query a report builds when looking at the Emails for a Contact looks something like:

SELECT IFNULL(l1.id,'') l1_id
,IFNULL(l1.message_id,'') l1_message_id
,IFNULL(l1.name,'') l1_name
,l1.date_entered l1_date_entered
FROM contacts
 INNER JOIN  emails_beans l1_1 ON contacts.id=l1_1.bean_id AND l1_1.deleted=0
 AND l1_1.bean_module = 'Contacts'
 INNER JOIN  emails l1 ON l1.id=l1_1.email_id AND l1.deleted=0

 WHERE (((contacts.first_name='Francesca'
) AND (contacts.last_name='Shiekh'
))) 
AND  contacts.deleted=0 

Which, as you noticed is restricted to emails directly related to the Contact record (the yellow connection above) via the emails_beans relationship table only, and it will, for example, exclude those emails related to Opportunities, Cases etc that were sent to that Contact's email address. (what you call the "implicitly-linked" emails)

What you really want, in order to get all the emails related to the addresses related to the Contact is to go through the green and blue links above.

SELECT 
e.id
,e.name as subject
,e.date_entered
,et.to_addrs
,et.from_addr
,c.id
from contacts c
join email_addr_bean_rel eabr on eabr.bean_id = c.id and eabr.bean_module = 'Contacts' and eabr.deleted = 0
join email_addresses ea on eabr.email_address_id = ea.id and ea.deleted = 0
join emails_email_addr_rel eear on eear.email_address_id = ea.id and eear.deleted = 0
join emails e on e.id = eear.email_id and e.deleted = 0
join emails_text et on et.email_id = e.id
where c.first_name = 'Francesca' 
and c.last_name = 'Shiekh'
and c.deleted = 0

There are a few ways I know of that you can get such a report, none are optimal, and all require coding.

1) If you are lucky enough to be working in Enterprise, use a SQL query Advanced Report (I'm on Professional so I can't give you much more information on how to do that).

2) If you are OnSite, execute the sql in MySQL workbench or similar tool (not really a report. more like something you can do as a one-off for someone)

3) Create your own Custom Scheduler to run the sql and build the report for you in the format you need and email it using MailerFactory (I do some like that), though it works best for pretty static reports - e.g. this week's whatever as opposed to emails for contact A vs emails for contact B since you have no good way of manipulating the parameters by user input.

4) Create your own API to run the query, with parameters, from a third party tool. I do this a lot too leveraging our own Wolfram Language to call APIs and manipulate and display data in notebooks that I give to the user or post on our internal Enterprise Cloud for people to use  (www.wolfram.com) 

I'm not sure any of this is helpful to you, but perhaps it explains the limitations for Sugar Reports when it comes to reporting on emails to someone less familiar with the database structure of emails.

FrancescaS