How do I add additional columns to Convert Lead page that shows existing Contacts that might match?

Amy Cox,

Great question. This took me a while to track down! I got it to put the Primary Address City instead of the title, but you will have to play with some joins to get anything interesting to appear --- like email or account name or anything like that -- but I didn't play with it long enough to get the label in the header of the table. 

In the file modules/Contacts/ContactFormBase.php I made the changes to the query string in line 65 [I highlighted my changes for you]

/**

* getDuplicateQuery

*

* This function returns the SQL String used for initial duplicate Contacts check

*

* @see checkForDuplicates (method), ContactFormBase.php, LeadFormBase.php, ProspectFormBase.php

* @param $focus sugarbean

* @param $prefix String value of prefix that may be present in $_POST variables

* @return SQL String of the query that should be used for the initial duplicate lookup check

*/

public function getDuplicateQuery($focus, $prefix='')

{

  $query = 'SELECT contacts.id, contacts.first_name, contacts.last_name, contacts.primary_address_city FROM contacts ';

    // Bug #46427 : Records from other Teams shown on Potential Duplicate Contacts screen during Lead Conversion

    // add team security

    $query .= ' where contacts.deleted = 0 AND ';

  if(isset($_POST[$prefix.'first_name']) && strlen($_POST[$prefix.'first_name']) != 0 && isset($_POST[$prefix.'last_name']) && strlen($_POST[$prefix.'last_name']) != 0){

  $query .= " contacts.first_name LIKE '". $_POST[$prefix.'first_name'] . "%' AND contacts.last_name = '". $_POST[$prefix.'last_name'] ."'";

  } else {

  $query .= " contacts.last_name = '". $_POST[$prefix.'last_name'] ."'";

  }

  if(!empty($_POST[$prefix.'record'])) {

  $query .= " AND  contacts.id != '". $_POST[$prefix.'record'] ."'";

  }

    return $query;

}

Hope this helps

Actually, try replacing the query with this:

Thank you for your response. 

I really need to do this in a custom folder though, so that it will be upgrade safe.

I am starting to wonder if there is a way to do that in the custom folder.

I have it . . . Just need to finish testing and will respond with the answer soon.

OK, Amy Cox, I have something for you -- whoever designed this didn't make upgrade-proof customization easy, but here it is.

In the Convert Leads process, the getDuplicateQuery() function is only called once. This is good news because it only requires one file copy and one new file creation.  First, copy the file modules/Leads/views/convertleads.php into a new folder called custom/modules/Leads/views/convertleads.php. Then find the require_once call for ContactFormBase.php [in CE version 6.5.22 it is line 406 and 407 of that file] and change this

            require_once('modules/Contacts/ContactFormBase.php');

      $contactForm = new ContactFormBase();

to this

            require_once( 'custom/modules/Contacts/CustomContactFormBase.php' );

      $contactForm = new CustomContactFormBase();

Then create a few class module called custom/modules/Leads/CustomContactFormBase.php that contains this code:

<?php

require_once('modules/Contacts/ContactFormBase.php');

class CustomContactFormBase extends ContactFormBase {

  public function getDuplicateQuery($focus, $prefix='')

  {

  $query = 'SELECT contacts.id '

  . ' , contacts.first_name'

  . ' , contacts.last_name'

  . ' , contacts.primary_address_city'

  . ' ,email_addresses.email_address'

  . ',accounts.name as "Account Name"'

  . 'FROM contacts '

  . ' 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.primary_address = 1'

  . ' LEFT JOIN email_addresses ON email_addresses.id = email_addr_bean_rel.email_address_id'

  . ' LEFT JOIN accounts_contacts ON accounts_contacts.contact_id = contacts.id'

  . ' LEFT JOIN accounts ON accounts.id = accounts_contacts.account_id';

// or whatever you want to capture

  $query .= ' where contacts.deleted = 0 AND ';

  if(isset($_POST[$prefix.'first_name'])

  && strlen($_POST[$prefix.'first_name']) != 0

  && isset($_POST[$prefix.'last_name'])

  && strlen($_POST[$prefix.'last_name']) != 0)

  {

  $query .= " contacts.first_name LIKE '". $_POST[$prefix.'first_name'] . "%' AND contacts.last_name = '". $_POST[$prefix.'last_name'] ."'";

  } else {

  $query .= " contacts.last_name = '". $_POST[$prefix.'last_name'] ."'";

  }

  if(!empty($_POST[$prefix.'record']))

  {

  $query .= " AND  contacts.id != '". $_POST[$prefix.'record'] ."'";

  }

  return $query;

  }

}

Don't forget to run a Quick Repair.

Hope this is what you were looking for!

It works!! The only thing left is to give the new columns names.

After doing the research I can put with the following:

In file ./custom/modules/Leads/language/en_us.lang.php there is an array of $mod_strings.  The following needs to be added for each column.

Database field name(or alias given in query) = {field name}, example: primary_address_city.

'db_{field name}' => 'LBL_LIST_{FIELD NAME}',

'LBL_LIST_{FIELD NAME' => '{label for field name in english',

Example of Primary City:

'db_primary_address_city' => 'LBL_LIST_PRIMARY_ADDRESS_CITY',

'LBL_LIST_PRIMARY_ADDRESS_CITY' => 'City',