Determining archived emails with email addresses that are not in the Contacts module

Question

Since the email module isn't in Studio, and since the Reports options for emails doesn't include the email address, I'm looking for a way to identify archived emails with email addresses that are not in the Contacts module. 
 OK, so maybe the issue is basically a management challenge to get the users to assure that the addresses for emails they receive or send are actually in the Contacts module, but that's not likely to happen :-) 
 The problem is somewhat compounded it that we have all inbound email to generic addresses (Sales, Service, etc) automatically directed to the Sugar BCC address we use for outbound email archiving. For now, we've been looking at the inbound emails and when an address isn't "familiar" a search in the Contacts module for an address that "starts with" is done. The bad news is that the address may exist but is a secondary address for a contact. 
 I've been hoping for a process that would flag (or send an alert) when an archived email address isn't in the Contacts module. I don't think that will happen. 
 Any suggestions for how I might be able to identify those email addresses? 
 
 P.S. Yes, I realize this might be done with some custom code, but we do not do that - Only Studio, Drop-Downs, and BPM...

Bud Hartley · Accepted Answer

You could build an SQL based report (I'm still on Professional so I can't give you a step by step but in Enterprise or Sell you should be able to do this), the following query will give you all email addresses that are not marked deleted and are not related to any module's bean. 
 select email_address 
from email_addresses 
where email_addresses.deleted = 0
and email_addresses.id not in(select bean_id from email_addr_bean_rel where deleted =0) 
 if you want to, more specifically, only look at email addresses that are not related to a Contact (but may be related to a Lead, a Target, a Case etc...) 
 select email_address 
from email_addresses 
where email_addresses.deleted = 0
and email_addresses.id not in(select bean_id from email_addr_bean_rel where bean_module = 'Contacts' and deleted =0) 
 You can change your select statement to give you more information about the email address specifically. 
 Any flag and reporting at the time of data entry would require coding, unless you can leverage BPM, I'm not familiar with BPM since we don't have access to it in Professional. 
 FrancescaS

Bud Hartley · Answer

Hey Bud, 
 you don't need Sugar Support for it. Within the Reports module menu the "Advanced reports" are a bit hidden but you can run it on your own! 
 https://support.sugarcrm.com/Documentation/Sugar_Versions/11.0/Serve/Application_Guide/Reports/Advanced_Reports/ 
 I think in Sugar University there is a free learning course how to handle advanced reports. 
 It might be possible that Sugar do not accept the syntax of the aboves sql queries but in that case please don't hesitate to ask me or Francesca - I'm sure she will help ;-) 
 Bests 
 Bj&ouml;rn