Advanced Duplication Management for Sugar

By Alexandru Calarasu • November 21st, 2016
Audiences: Developers

In a previous blog post, we discussed duplicate checking on save in Sugar and how the logic behind this remains hidden. The simplest way to customize duplicate checking is by adding a custom vardef extension, as seen in Part 2 of the Duplicate Checking on Save blog post.

There are however scenarios where we need to tackle the issue in a more programmatic manner.

Our hypothetic client, ACME Inc. requires a custom cross modules duplicate check. When a new Lead record is created upon the initial Save, use the following conditions (business logic) to determine if a Lead has duplicates:

  1. New Lead’s e-mail address value matches an existing Lead's or Contact's e-mail address value.
  2. New Lead’s mobile phone value matches an existing Lead's or Contact's mobile value.
  3. New Lead’s First and Last Name values match an existing Lead's or Contact's First and Last Name values, and New Lead’s street address value matches an existing Lead's or Contact's street address value.
  4. New Lead’s First and Last Name values match an existing Lead or Contact First and Last Name values, and New Lead’s Company Name value matches an existing Lead or Company Name value.

The duplicate check needs to be done in different modules so that rules out our metadata-driven approach. The only way left is to customize SugarCRM's duplicateCheck API endpoint using Sugar's customization framework.

Create a custom file: custom/clients/base/api/customLeadsDuplicateCheck.php. This is where we will create our custom API which extends Sugar's base DuplicateCheckApi.

class LeadsCustomDuplicateCheckApi extends DuplicateCheckApi
{
	public function registerApiRest()
	{
		return array(
			'duplicateCheck' => array(
				'reqType' => 'POST',
				'path' => array('Leads','duplicateCheck'),
				'pathVars' => array('module',''),
				'method' => 'checkForDuplicates',
				'shortHelp' => 'Custom duplicate check for the Leads module',
				'longHelp' => '',
			),
		);
	}
}

The method which handles the duplicate checking is checkForDuplicates. By overriding this method, we can add our logic to the duplicate check. We write our custom query and return the results:

function checkForDuplicates(ServiceBase $api, array $args)
  {
  	global $db;

    $bean = BeanFactory::newBean($args['module']);

    $this->handleEmptyBean($bean);

    $args = $this->trimArgs($args);

    if (!$bean->ACLAccess('read')) {
      throw new SugarApiExceptionNotAuthorized('No access to read records for module: '.$args['module']);
    }

    $options = array('acl' => 'read');

    $errors = $this->populateFromApi($api, $bean, $args, $options);

    if ($errors !== true) {
      $displayErrors = print_r($errors, true);
      throw new SugarApiExceptionInvalidParameter("Unable to run duplicate check. There were validation errors on the submitted data: $displayErrors");
    }

    $leads_contacts_query = <<<EQQ
SELECT l.id, 'Leads' AS _module, l.first_name, l.last_name, l.primary_address_street, l.account_name, ea.email_address,
		l.phone_mobile, l_c.stage_c stage, l.status, us.user_name AS assigned_user_name
FROM leads l
INNER JOIN leads_cstm l_c
	ON l.id = l_c.id_c
LEFT JOIN email_addr_bean_rel eabr
	ON eabr.bean_id = l.id
	AND eabr.deleted = 0
LEFT JOIN email_addresses ea
	ON eabr.email_address_id = ea.id
	AND ea.deleted = 0
LEFT JOIN users us
	ON l.assigned_user_id = us.id
	AND us.deleted = 0
WHERE l.deleted = 0
AND (ea.email_address = '{$bean->email1}' OR
	 l.phone_mobile = '{$bean->phone_mobile}' OR
	 (l.first_name = '{$bean->first_name}' AND l.last_name = '{$bean->last_name}' AND l.primary_address_street = '{$bean->primary_address_street}') OR
	 (l.first_name = '{$bean->first_name}' AND l.last_name = '{$bean->last_name}' AND l.account_name = '{$bean->account_name}'))

UNION

SELECT c.id, 'Contacts' AS _module, c.first_name, c.last_name, c.primary_address_street, acnt.name as account_name,
	ea.email_address, c.phone_mobile, a_c.stage_c AS stage, a_c.status_c AS status, us.user_name AS assigned_user_name
FROM contacts c
INNER JOIN contacts_cstm c_c
	ON c.id = c_c.id_c
LEFT JOIN email_addr_bean_rel eabr
	ON eabr.bean_id = c.id
	AND eabr.deleted = 0
LEFT JOIN email_addresses ea
	ON eabr.email_address_id = ea.id
	AND ea.deleted = 0
LEFT JOIN accounts_contacts ac
	ON ac.contact_id = c.id
	AND ac.deleted = 0
LEFT JOIN accounts acnt
	ON acnt.id = ac.account_id
	AND acnt.deleted = 0
LEFT JOIN users us
	ON c.assigned_user_id = us.id
	AND us.deleted = 0
LEFT JOIN accounts_cstm a_c
	ON acnt.id = a_c.id_c
WHERE c.deleted = 0
AND (ea.email_address = '{$bean->email1}' OR
	 c.phone_mobile = '{$bean->phone_mobile}' OR
	 (c.first_name = '{$bean->first_name}' AND c.last_name = '{$bean->last_name}' AND c.primary_address_street = '{$bean->primary_address_street}') OR
	 (c.first_name = '{$bean->first_name}' AND c.last_name = '{$bean->last_name}' AND acnt.name = '{$bean->account_name}'))
EQQ;

		$result = $db->query($leads_contacts_query);

		$duplicate_records = array();

		while ($row = $db->fetchByAssoc($result)) {
			$duplicate_records[] = $row;
		}

		return array(
			'records' => $duplicate_records,
			'next_offset' => -1
		);
  }

Sugar's API customizations capabilities allowed us to modify a core feature of their application, to create a more advanced way of checking for duplicate records. If you'd like more information about Duplicate Checking on Save in Sugar, read Part 1 and Part 2 of the article, in our SugarCRM blog

More From This Author
Alexandru Calarasu
Developer at UpCurve Cloud