Report on Relate Field Values

Question

Hey There! 
 I'm stumped on this one. I want to run a Rows and Columns report on the values of some relate fields but can't find a way to pull information on the records linked in the relate field. 
 Here are the specifics: I have company contact persons listed under Account records using a relate field. I now wish to pull the email addresses of all Contact records linked in those relate fields for producing a mailing list. I can't find a way to pull any data from those relate fields other than the names of the Contacts that are listed in those relate fields. 
 Any ideas? 
 Thanks so much! 
 John

John Smith · Accepted Answer

Hi John Smith , 
 Can you clarify how the relate field was created? Was it created as a 'Relate' custom field in the Accounts module or did you create a new relationship between the Accounts and Contacts modules? If you created this as a 'Relate' custom field, you will not be able to retrieve this data via standard reports as 'Relate' fields are only one-way (i.e. you can only see the linked data on the source record and the linked record does not show that reference at all). The standard reports only allow you to traverse to related data when the records are linked by a formal relationship (i.e. one-to-one, one-to-many, many-to-many). 
 If this is a 'Relate' custom field, you can retrieve this data via Advanced Reports. Using Advanced Reports requires a knowledge of writing SQL and these are the tables you would need to join at a minimum in your query: 
 
 accounts to accounts_cstm 
 accounts_cstm to contacts 
 contacts to email_addr_bean_rel 
 email_addr_bean_rel to email_addresses

Chris

John Smith · Answer

Hi John Smith 
 Can I ask the reason not to use the Contact module with a flag to identify the 'main contact' or other contact types to help segment for your marketing etc.?