Details about this thread
  • State Not Answered
  • Replies 2 replies
  • Subscribers 314 subscribers
  • Views 813 views
  • Users 0 members are here
Related content from around SugarClub

Displaying Fields from Related Modules in "Email Address" Report

Sam Horvath

I am trying to build a report that begins with the Email Addresses module, and returns field values for several related modules. 

However, when I choose Report Type = Rows and Columns, and Module = Email Address, no related modules are displayed. 

Is there a way to fix this? I know that the Email Address module is related to all of the modules that I want to output data for (Leads, Contacts, Opportunities, and Meetings).

Thank you in advance.

  • 0

    I think the reason is that the "relationship" between Email Addresses and other modules is not modeled like other relationships.

    You might be able to do something with a SQL based report, I have not tried as I do these kinds of reports querying the database directly in my on-site instance.

    The key to linking an email address to a Contact or Lead or other module is in the email_addr_bean_rel table which has a bean_module and bean_id which will tell you which module and which record the email_address_id is related to.

    At its most basic the query will look something like:

    select email_addresses.email_address, email_addr_bean_rel.bean_module, email_addr_bean_rel.bean_id
from email_addresses
join email_addr_bean_rel on email_addr_bean_rel.email_address_id = email_addresses.id

    I have not worked with SQL based reports before so I don't know how far you can take this nor do I know if it will help get you where you need to go...

    Off the top of my head, the way I would do it is to create a custom API that will generate the report by compiling data from various queries and beans into one.

    Hope this helps,
    FrancescaS

  • 0 in reply to Francesca Shiekh

    Thank you for weighing in. I'm not a developer, so this is a bit beyond me. I will share this with my dev and see if he agrees that this would be a way forward.

    Thanks