Advanced Reporting on Email Field in Accounts Module

My apologies if I'm going into obvious detail, I don't know your level of familiarity with Sugar's Studio and database table structure.

Here is a starting point:

All relationships between modules and email_addresses are via the  email_addr_bean_rel table where there are three essential columns:

email_address_id (links to email_addresses)

bean_module (the name of the module the address is on, for example "Contacts", "Accounts" etc)

bean_id (the id of the record the email_address is on)

So your query will look something like:

select accounts.name, 
email_addresses.email_address,
accounts.account_type,
accounts_cstm.account_number_c
from accounts
join accounts_cstm on accounts_cstm.id_c = accounts.id
join email_addr_bean_rel on email_addr_bean_rel.bean_id = accounts.id 
	 and email_addr_bean_rel.bean_module = 'Accounts' 
	 and email_addr_bean_rel.deleted = 0
join email_addresses on email_addresses.id = email_addr_bean_rel.email_address_id
     and email_addresses.deleted = 0
where accounts.deleted = 0
and account.account_type = 'CurrentCustomer'

Note that I added accounts_cstm which is the table where your custom fields for Accounts are stored. I am assuming the Account Number is a custom field. I am also assuming that your field name is account_number_c (this is a big assumption).

To see the name of the field from your instance, you can use Studio (Admin->Studio->Accounts->Fields) or open an account record view, right click on that field's label and "Inspect", in the "Elements" tab of your tools you will see something like:

data-name="the_name_of_your_field_here_c"

As for the "Current Customer" value, you will need to check what you are using for the actual value stored in the database: go to

Admin->Studio->Accounts->Fields->account_type 

just below the "Drop down List" is an Edit button, click that and it will show you all the values and labels.

Use the value on the left, in bold, as the value for the filter.

Hope this helps you get what you need.

FrancescaS

My apologies if I'm going into obvious detail, I don't know your level of familiarity with Sugar's Studio and database table structure.

Here is a starting point:

All relationships between modules and email_addresses are via the  email_addr_bean_rel table where there are three essential columns:

email_address_id (links to email_addresses)

bean_module (the name of the module the address is on, for example "Contacts", "Accounts" etc)

bean_id (the id of the record the email_address is on)

So your query will look something like:

select accounts.name, 
email_addresses.email_address,
accounts.account_type,
accounts_cstm.account_number_c
from accounts
join accounts_cstm on accounts_cstm.id_c = accounts.id
join email_addr_bean_rel on email_addr_bean_rel.bean_id = accounts.id 
	 and email_addr_bean_rel.bean_module = 'Accounts' 
	 and email_addr_bean_rel.deleted = 0
join email_addresses on email_addresses.id = email_addr_bean_rel.email_address_id
     and email_addresses.deleted = 0
where accounts.deleted = 0
and account.account_type = 'CurrentCustomer'

Note that I added accounts_cstm which is the table where your custom fields for Accounts are stored. I am assuming the Account Number is a custom field. I am also assuming that your field name is account_number_c (this is a big assumption).

To see the name of the field from your instance, you can use Studio (Admin->Studio->Accounts->Fields) or open an account record view, right click on that field's label and "Inspect", in the "Elements" tab of your tools you will see something like:

data-name="the_name_of_your_field_here_c"

As for the "Current Customer" value, you will need to check what you are using for the actual value stored in the database: go to

Admin->Studio->Accounts->Fields->account_type 

just below the "Drop down List" is an Edit button, click that and it will show you all the values and labels.

Use the value on the left, in bold, as the value for the filter.

Hope this helps you get what you need.

FrancescaS