Kontakte mit zusätzlichen Feldern – wie Taget List exportieren?

[vox789]

Wir haben mit Studio zusätzliche Felder zu den Kontakten hinzugefügt.

Nun müssen wir Target Lists exportieren, die Kontakte enthalten. Leider wird die CSV Liste ohne die zusätzlichen Felder erstellt.
Könnte mir jemand einen Tipp geben, wie man einen vollständigen Export der Kontakt-Daten einer Target Liste erhalten kann?

[sts]

Hier gibt es einen Thread zu diesem Thema:
http://www.sugarcrm.com/forums/f22/h...sv-file-65790/

[kuske]

Für den Export der Elemente einer Targetliste wird die Funktion create_export_query im Modul modules/ProspectList/ProspectList.php verwendet. Hier muss man dann die zusätzlich benötigten Felder mit einbauen.
Dabei unbedingt darauf achten, dass die Query einen Join über 5 Tabellen (Leads, Accounts, Contacts, Prospects und Users) macht.

Beispiel:

  function create_export_members_query($record_id)
 {
//PATCH
// im Folgenden wurden 5 salutation-Zeilen eingefügt
// im Folgenden wurden 5 title-Zeilen eingefügt
// im Folgenden wurden 5 department-Zeilen eingefügt
// im Folgenden wurden 5 alt-address-Zeilen eingefügt
// im Folgenden wurden 5 position-Zeilen eingefügt
// im Folgenden wurden 5 owner-Zeilen eingefügt
// im Folgenden wurden 5 company1-Zeilen eingefügt
// im Folgenden wurden 5 company2-Zeilen eingefügt
// im Folgenden wurden 5 sprache-Zeilen eingefügt
//PATCH
  $leads_query = "SELECT l.id AS id, 'Leads' AS related_type, '' AS \"name\", l.first_name AS first_name, l.last_name AS last_name,
    l.primary_address_street AS primary_address_street,l.primary_address_city AS primary_address_city, l.primary_address_state AS primary_address_state, l.primary_address_postalcode AS primary_address_postalcode, l.primary_address_country AS primary_address_country,
    l.alt_address_street AS alt_address_street,l.alt_address_city AS alt_address_city, l.alt_address_state AS alt_address_state, l.alt_address_postalcode AS alt_address_postalcode, l.alt_address_country AS alt_address_country,
    l.account_name AS account_name,
    l.account_name AS company_name_1,
    '' AS company_name_2,
    l.salutation AS salutation,
    l.title AS title,
    l.department AS department,
                (select cs.position_c from leads_cstm cs where cs.id_c = l.id) as position,
                (select cs.sprache_c from leads_cstm cs where cs.id_c = l.id) as sprache,
                (select concat(u.last_name,' ',u.first_name) from users u where u.id = l.assigned_user_id) as owner,
    ea.email_address AS primary_email_address, ea.invalid_email AS invalid_email, ea.opt_out AS opt_out, ea.deleted AS ea_deleted, ear.deleted AS ear_deleted, ear.primary_address AS primary_address,
    l.do_not_call AS do_not_call, l.phone_fax AS phone_fax, l.phone_other AS phone_other, l.phone_home AS phone_home, l.phone_mobile AS phone_mobile, l.phone_work AS phone_work
    FROM prospect_lists_prospects plp
    INNER JOIN leads l ON plp.related_id=l.id
    LEFT JOIN email_addr_bean_rel ear ON  ear.bean_id=l.id
    LEFT JOIN email_addresses ea ON ear.email_address_id=ea.id
    WHERE plp.prospect_list_id = $record_id AND plp.deleted=0
    AND l.deleted=0";
  $users_query = "SELECT u.id AS id, 'Users' AS related_type, '' AS \"name\", u.first_name AS first_name, u.last_name AS last_name,
    u.address_street AS primary_address_street,u.address_city AS primary_address_city, u.address_state AS primary_address_state,  u.address_postalcode AS primary_address_postalcode, u.address_country AS primary_address_country,
    '' AS primary_address_street,'' AS primary_address_city, '' AS primary_address_state,  '' AS primary_address_postalcode, '' AS primary_address_country,
    '' AS account_name,
    '' AS company_name_1,
    '' AS company_name_2,
    '' AS salutation,
    u.title AS title,
    u.department AS department,
                '' as position,
                '' as sprache,
                concat(u.last_name,' ',u.first_name) as owner,
    ea.email_address AS email_address, ea.invalid_email AS invalid_email, ea.opt_out AS opt_out, ea.deleted AS ea_deleted, ear.deleted AS ear_deleted, ear.primary_address AS primary_address,
    0 AS do_not_call, u.phone_fax AS phone_fax, u.phone_other AS phone_other, u.phone_home AS phone_home, u.phone_mobile AS phone_mobile, u.phone_work AS phone_work
    FROM prospect_lists_prospects plp
    INNER JOIN users u ON plp.related_id=u.id
    LEFT JOIN email_addr_bean_rel ear ON  ear.bean_id=u.id
    LEFT JOIN email_addresses ea ON ear.email_address_id=ea.id
    WHERE plp.prospect_list_id = $record_id AND plp.deleted=0
    AND u.deleted=0";
  $contacts_query = "SELECT c.id AS id, 'Contacts' AS related_type, '' AS \"name\", c.first_name AS first_name, c.last_name AS last_name,
    c.primary_address_street AS primary_address_street,c.primary_address_city AS primary_address_city, c.primary_address_state AS primary_address_state,  c.primary_address_postalcode AS primary_address_postalcode, c.primary_address_country AS primary_address_country,
    c.alt_address_street AS alt_address_street,c.alt_address_city AS alt_address_city, c.alt_address_state AS alt_address_state, c.alt_address_postalcode AS alt_address_postalcode, c.alt_address_country AS alt_address_country,
    a.name AS account_name,
    (select csa.company_name_1_c from accounts_cstm csa where csa.id_c = a.id) AS company_name_1,
    (select csa.company_name_2_c from accounts_cstm csa where csa.id_c = a.id) AS company_name_2,
    c.salutation AS salutation,
    c.title AS title,
    c.department AS department,
                (select cs.position_c from contacts_cstm cs where cs.id_c = c.id) as position,
                (select cs.sprache_c from contacts_cstm cs where cs.id_c = c.id) as sprache,
                (select concat(u.last_name,' ',u.first_name) from users u where u.id = c.assigned_user_id) as owner,
    ea.email_address AS email_address, ea.invalid_email AS invalid_email, ea.opt_out AS opt_out, ea.deleted AS ea_deleted, ear.deleted AS ear_deleted, ear.primary_address AS primary_address,
    c.do_not_call AS do_not_call, c.phone_fax AS phone_fax, c.phone_other AS phone_other, c.phone_home AS phone_home, c.phone_mobile AS phone_mobile, c.phone_work AS phone_work
    FROM  accounts a,accounts_contacts ac, prospect_lists_prospects plp
    INNER JOIN contacts c ON plp.related_id=c.id
    LEFT JOIN email_addr_bean_rel ear ON  ear.bean_id=c.id
    LEFT JOIN email_addresses ea ON ear.email_address_id=ea.id
    WHERE plp.prospect_list_id = $record_id  AND plp.deleted=0
    AND c.deleted=0
    AND ac.contact_id=c.id
    AND ac.account_id=a.id";
  $prospects_query = "SELECT p.id AS id, 'Prospects' AS related_type, '' AS \"name\", p.first_name AS first_name, p.last_name AS last_name,
    p.primary_address_street AS primary_address_street,p.primary_address_city AS primary_address_city, '' AS primary_address_state,  p.primary_address_postalcode AS primary_address_postalcode, p.primary_address_country AS primary_address_country,
    p.alt_address_street AS alt_address_street,p.alt_address_city AS alt_address_city, p.alt_address_state AS alt_address_state, p.alt_address_postalcode AS alt_address_postalcode, p.alt_address_country AS alt_address_country,
    p.account_name AS account_name,
    p.account_name AS company_name_1,
    '' AS company_name_2,
    p.salutation AS salutation,
    p.title AS title,
    p.department AS department,
                '' as position,
                '' as sprache,
                (select concat(u.last_name,' ',u.first_name) from users u where u.id = p.assigned_user_id) as owner,
    ea.email_address AS email_address, ea.invalid_email AS invalid_email, ea.opt_out AS opt_out, ea.deleted AS ea_deleted, ear.deleted AS ear_deleted, ear.primary_address AS primary_address,
    p.do_not_call AS do_not_call, p.phone_fax AS phone_fax, p.phone_other AS phone_other, p.phone_home AS phone_home, p.phone_mobile AS phone_mobile, p.phone_work AS phone_work
    FROM prospect_lists_prospects plp
    INNER JOIN prospects p ON plp.related_id=p.id
    LEFT JOIN email_addr_bean_rel ear ON  ear.bean_id=p.id
    LEFT JOIN email_addresses ea ON ear.email_address_id=ea.id
    WHERE plp.prospect_list_id = $record_id  AND plp.deleted=0
    AND p.deleted=0";
  $accounts_query = "SELECT a.id AS id, 'Accounts' AS related_type, a.name AS \"name\", '' AS first_name, '' AS last_name,
    a.billing_address_street AS primary_address_street,a.billing_address_city AS primary_address_city, a.billing_address_state AS primary_address_state, a.billing_address_postalcode AS primary_address_postalcode, a.billing_address_country AS primary_address_country,
    a.shipping_address_street AS alt_address_street,a.shipping_address_city AS alt_address_city, a.shipping_address_state AS alt_address_state, a.shipping_address_postalcode AS alt_address_postalcode, a.shipping_address_country AS alt_address_country,
    '' AS account_name,
    (select csa.company_name_1_c from accounts_cstm csa where csa.id_c = a.id) AS company_name_1,
    (select csa.company_name_2_c from accounts_cstm csa where csa.id_c = a.id) AS company_name_2,
    '' AS salutation,
    '' AS title,
    '' AS department,
                '' as position,
                '' as sprache,
                (select concat(u.last_name,' ',u.first_name) from users u where u.id = a.assigned_user_id) as owner,
    ea.email_address AS email_address, ea.invalid_email AS invalid_email, ea.opt_out AS opt_out, ea.deleted AS ea_deleted, ear.deleted AS ear_deleted, ear.primary_address AS primary_address,
    0 AS do_not_call, a.phone_fax as phone_fax, a.phone_alternate AS phone_other, '' AS phone_home, '' AS phone_mobile, a.phone_office AS phone_office
    FROM prospect_lists_prospects plp
    INNER JOIN accounts a ON plp.related_id=a.id
    LEFT JOIN email_addr_bean_rel ear ON  ear.bean_id=a.id
    LEFT JOIN email_addresses ea ON ear.email_address_id=ea.id
    WHERE plp.prospect_list_id = $record_id  AND plp.deleted=0
    AND a.deleted=0";
  $order_by = "ORDER BY related_type, id, primary_address DESC";
  $query = "$leads_query UNION ALL $users_query UNION ALL $contacts_query UNION ALL $prospects_query UNION ALL $accounts_query $order_by";
  return $query;
 } 
  

[vox789]

Allerbesten Dank für die ausführliche Information.