Export Contacts - Account ID

[heritagehc]

When exporting contacts it does not bring out the associated account. Is there a way to pull an export of contacts and get the account id?

Thanks!

[kuske]

It's very simple.
Each bean provides a function create_export_query which generates the query for all fields to be exported.
In /modules/contacts/Contact.php you can add a line in function create_export_query to this query statement like that:

                          $query = "SELECT
                                contacts.*,email_addresses.email_address email1,
                                accounts.name as account_name,
                                accounts.id as account_id,
                                users.user_name as assigned_user_name "; 
  

[heritagehc]

I tried your suggestion and ended up with an extra column in my export file, but it was empty and had no entries. I know that the relationships exist between accounts and contacts, because I can see the appropriate contacts tied to each account inside sugar (and links to the account inside the contact views, etc.).

Below is the function from my contact.php file (including your suggested edit):

function create_export_query(&$order_by, &$where, $relate_link_join='')
{
$custom_join = $this->custom_fields->getJOIN(true, true,$where);
if($custom_join)
$custom_join['join'] .= $relate_link_join;
$query = "SELECT
contacts.*,email_addresses.email_address email1,
accounts.name as account_name,
accounts.id as account_id,
users.user_name as assigned_user_name ";



if($custom_join){
$query .= $custom_join['select'];
}
$query .= " FROM contacts ";




$query .= "LEFT JOIN users
ON contacts.assigned_user_id=users.id ";



$query .= "LEFT JOIN accounts_contacts
ON ( contacts.id=accounts_contacts.contact_id and (accounts_contacts.deleted is null or accounts_contacts.deleted = 0))
LEFT JOIN accounts
ON accounts_contacts.account_id=accounts.id ";

//join email address table too.
$query .= ' LEFT JOIN email_addr_bean_rel on contacts.id = email_addr_bean_rel.bean_id and email_addr_bean_rel.bean_module=\'Contacts\' and email_addr_bean_rel.deleted=0 and email_addr_bean_rel.primary_address=1 ';
$query .= ' LEFT JOIN email_addresses on email_addresses.id = email_addr_bean_rel.email_address_id ' ;

if($custom_join){
$query .= $custom_join['join'];
}

$where_auto = "( accounts.deleted IS NULL OR accounts.deleted=0 )
AND contacts.deleted=0 ";

if($where != "")
$query .= "where ($where) AND ".$where_auto;
else
$query .= "where ".$where_auto;

if(!empty($order_by))
$query .= " ORDER BY ". $this->process_order_by($order_by, null);

return $query;
}

[maen]

Hello, I have a similar task for export. I joined the contacts in export which is great. But I need a multiple join to get a colum from contacts_cstm. Unfortunately Im not able for this join.

Example:

In line 365 I modified the code like this:

             $custom_join = $this->custom_fields->getJOIN(true, true,$where);
            if($custom_join)
                $custom_join['join'] .= $relate_link_join;
                         $query = "SELECT
                                accounts.name as firma,
                                accounts.billing_address_street as Rechnungsanschrift_Strasse,
                                accounts.billing_address_postalcode as rechnungsanschrift_PLZ,
                                accounts.billing_address_city as rechnungsanschrift_stadt,
                                accounts.billing_address_country as rechnungsanschrift_land, 
                                accounts.description as beschreibung,
                            
                                contacts.salutation as Anrede,
                                contacts.first_name as Vorname,
                                contacts.last_name as Nachname,
                                
                                contacts.do_not_call as darf_angerufen_werden_ist_status_0,
                                email_addresses.email_address FirmenEmail"; 
  

And I put a join from line 395:

                         //join contacts from relation contacts and accounts_contacts
                        $query .= "LEFT JOIN accounts_contacts
                                    ON ( accounts.id=accounts_contacts.account_id and (accounts_contacts.deleted is null or accounts_contacts.deleted = 0))
                                    LEFT JOIN contacts
                                    ON accounts_contacts.contact_id=contacts.id "; 
  

Works great! The issue: I need a contacts_cstm column for filter options in excel. Does anybody knows how to put this multiple join?

Thnx in advance,

Marc