Author Topic: Contact Name is changed if someone else joins with same email address (Read 1531 times)

pennyhaynes · January 04, 2012, 07:35:07 am

I use multiple customized tables to acquire additional information from patients using profiles. Patients submit their information online for 1) demographics and 2) medical history. However, if different people, such as multiple members of a family, use the same email, it is changing the name of the first person who submitted their information to the next person who submits it and attaches the newest information to that contact ID #. This leaves me with contacts attached to the wrong demographics and medical history, and some contacts missing altogether. NOT GOOD!

How can I change the parameters that determine how custom profile data is attached to Contacts? I ASSumed it would be by first and last name, but obviously it is by email.

Coleman Watts · January 04, 2012, 09:09:40 am

You need to modify your default strict dedupe rule to use more than just email.
email+first_name works pretty well (although I find the whole dedupe interface too limiting so I've coded my own instead using hook_civicrm_dupeQuery)

Of course, if the user is logged in, it will always overwrite the fields because it always assumes the current user is filling out the form on behalf of him/herself.

Have you looked into the webform_civicrm module? It can do a lot more than profiles can.

pennyhaynes · January 04, 2012, 12:44:48 pm

Thank you so much. Unfortunately, I'm using Joomla, since I could never get Drupal to install completely, so that module doesn't work for me. Do you have a version for Joomla?

I didn't know it was defaulting to the strict rule. Thank you so much. I have changed it and will test it.

Aahar · January 04, 2012, 05:31:04 pm

Colemanw, you mentioned that "although I find the whole dedupe interface too limiting so I've coded my own instead using hook_civicrm_dupeQuery" .
Could you please elaborate that. What did you intend to do .. Could I please share ...

Coleman Watts · January 05, 2012, 01:31:16 pm

Sorry, all the good stuff for CiviCRM (Views, Webforms, user-roles, CCK, etc.) are Drupal-only. If you haven't put too much work into your J! site, I think making the switch is really worth it.

My deduping code matches on Last Name AND (First or Nick name) AND (Phone or Email or Address). It really makes a big difference in cutting down on false-positives and false-negatives.

If you'd like to use it I'm happy to share.

See http://wiki.civicrm.org/confluence/display/CRMDOC40/CiviCRM+hook+specification#CiviCRMhookspecification-Proceduresforimplementinghooks%28forJoomla%29 for how to implement.

Cat Paquin · January 11, 2012, 05:14:10 am

Quote from: colemanw on January 04, 2012, 09:09:40 am

You need to modify your default strict dedupe rule to use more than just email.
email+first_name works pretty well

I was having the same problem for contributions. If two family members used the same email address, it overwrites the contact information of the first person with the contact information of the second person.

I modified the Individual-Strict rule, but I want to make sure I did this correctly. Under Fields, I have Email, First Name, Last Name. All of the weights are 10. Then I changed the Weight Threshold to be 30. I believe this would mean that all of the fields have to match to declare a duplicate contact. Is that correct?

Thank you,
Cat

Coleman Watts · January 11, 2012, 10:55:36 am

That's right - in that scenario it will only match if all three are an exact match. Not very intelligent, and you'll get loads of duplicates in your db, but there you have it.

Coleman Watts · January 11, 2012, 01:34:59 pm

Here's the code I've written for woolman.org which implements "smart" deduping, both for front-end (strict rule) and back-end (fuzzy) applications.

Code: [Select]

<?php
/**
 * Implementation of hook_civicrm_dupeQuery().
 */
function woolman_website_civicrm_dupeQuery($obj, $type, &$query) {
  if ($type == 'table' && empty($obj->noRules)) {
    woolman_dupe_query($obj, $type, $query);
  }
}

function woolman_dupe_query($obj, $type, &$query) {
  $rules = array(
    'Individual Strict' => array(
      'internal' => 'individual_strict_internal',
      'record' => 'individual_strict_record'
    ),
    'Individual Fuzzy' => array(
      'internal' => 'individual_fuzzy_internal',
      'record' => ''
    ),
  );
  $rule_type = empty($obj->params) ? 'internal' : 'record';

  // If its a rule that we've overridden
  if (array_key_exists($obj->name, $rules) && $rule = $rules[$obj->name][$rule_type]) {

    $obj->threshold = 5;
    if (!($q = call_user_func('woolman_dedupe_' . $rule, $obj))) {
      $query = array();
      return;
    }

    // For internal queries: verify t1 and t2 are different and prevent mirrored results
    if ($rule_type == 'internal') {
      $q .= " AND t1.id < t2.id AND t1.is_deleted = 0 AND t2.is_deleted = 0";
      // Make sure to respect the contactIds constraints
      if (!empty($obj->contactIds)) {
        $cids = implode(',', $obj->contactIds);
        $q .= " AND (t1.id IN($cids) OR t2.id IN($cids))";
      }
    }
    $query = array('civicrm_contact.display_name.5' => $q);
  }
}


function woolman_dedupe_individual_strict_internal($obj) {
  return "
    SELECT t1.id id1, t2.id id2, 5 weight
    FROM civicrm_contact t1
    JOIN civicrm_contact t2 ON (t1.first_name = t2.first_name OR t1.nick_name = t2.first_name OR t1.first_name = t2.nick_name)
    LEFT JOIN civicrm_email e1 ON e1.contact_id = t1.id
    LEFT JOIN civicrm_email e2 ON e2.contact_id = t2.id
    WHERE t1.contact_type = 'Individual'
      AND t2.contact_type = 'Individual'
      AND (t1.last_name = t2.last_name OR e1.email = e2.email)
  ";
}


function woolman_dedupe_individual_fuzzy_internal($obj) {
  return "
    SELECT t1.id id1, t2.id id2, 5 weight
    FROM civicrm_contact t1
    JOIN civicrm_contact t2 ON t2.contact_type = 'Individual'
      AND (SUBSTRING_INDEX(t1.first_name, ' ', 1) = SUBSTRING_INDEX(t2.first_name, ' ', 1)
        OR SUBSTRING_INDEX(t1.nick_name, ' ', 1) = SUBSTRING_INDEX(t2.first_name, ' ', 1)
        OR SUBSTRING_INDEX(t1.first_name, ' ', 1) = SUBSTRING_INDEX(t2.nick_name, ' ', 1)
        OR SUBSTRING_INDEX(t1.first_name, ' ', -1) = SUBSTRING_INDEX(t2.first_name, ' ', -1)
        OR SUBSTRING_INDEX(t1.nick_name, ' ', -1) = SUBSTRING_INDEX(t2.first_name, ' ', -1)
        OR SUBSTRING_INDEX(t1.first_name, ' ', -1) = SUBSTRING_INDEX(t2.nick_name, ' ', -1)
        OR t1.nick_name = t2.middle_name
        OR t1.middle_name = t2.nick_name
      )
    LEFT JOIN civicrm_email e1 ON e1.contact_id = t1.id
    LEFT JOIN civicrm_email e2 ON e2.contact_id = t2.id
    WHERE t1.contact_type = 'Individual'
      AND (
        (SUBSTRING_INDEX(t1.last_name, ' ', 1) = SUBSTRING_INDEX(t2.last_name, ' ', 1)
          OR SUBSTRING_INDEX(t1.last_name, ' ', -1) = SUBSTRING_INDEX(t2.last_name, ' ', -1)
          OR SUBSTRING_INDEX(t1.last_name, '-', 1) = SUBSTRING_INDEX(t2.last_name, '-', 1)
          OR SUBSTRING_INDEX(t1.last_name, '-', -1) = SUBSTRING_INDEX(t2.last_name, '-', -1))
        OR e1.email = e2.email
        OR (SUBSTRING_INDEX(e1.email, '@', 1) = SUBSTRING_INDEX(e2.email, '@', 1) AND t1.last_name = t2.last_name))
  ";
}


function woolman_dedupe_individual_strict_record($obj) {
  $join = '';
  $where = "WHERE t1.is_deleted = 0 AND t1.contact_type = 'Individual'";
  $loc_query = array();
  $criteria_met = 0;
  $p = $obj->params;
  foreach (array('first', 'last', 'nick') as $n) {
    ${$n . '_name'} = CRM_Core_DAO::escapeString(trim(CRM_Utils_Array::value($n . '_name', $p['civicrm_contact'], '')));
  }
  if ($first_name || $nick_name) {
    ++$criteria_met;
    $where .= "\nAND (";
    if ($first_name) {
      $where .= "t1.first_name = '$first_name' OR t1.nick_name = '$first_name'";
    }
    if ($nick_name && $nick_name != $first_name) {
      $where .= ($first_name ? ' OR ' : '') ."t1.first_name = '$nick_name' OR t1.nick_name = '$nick_name'";
    }
    $where .= ')';
  }
  if ($last_name) {
    ++$criteria_met;
    $where .= "\nAND t1.last_name = '$last_name'";
  }
  if ($birth_date = CRM_Utils_Array::value('birth_date', $p['civicrm_contact'], '')) {
    $loc_query[] = "t1.birth_date = '" . date('Y-m-d', strtotime($p['civicrm_contact']['birth_date'])) . "'";
  }

  foreach (array('email', 'address', 'phone') as $loc) {
    $field_name = str_replace('address', 'street_address', $loc);
    if ($field = CRM_Core_DAO::escapeString(trim(CRM_Utils_Array::value($field_name, $p['civicrm_' . $loc], '')))) {
      $join .= "\nLEFT JOIN civicrm_$loc $loc ON $loc.contact_id = t1.id";
      if ($loc == 'phone') {
        $unwanted_phone_chars = array(' ', '-', ')', '(', '.', '/');
        $field = substr(str_replace($unwanted_phone_chars, '', $field), 0, 10);
        $query = 'phone.phone';
        foreach ($unwanted_phone_chars as $char) {
          $query = "REPLACE($query, '$char', '')";
        }
        $query = "LEFT($query, 10) = '$field'";
      }
      else {
        $query = "$loc . $field_name = '$field'";
      }
      $loc_query[] = $query;
    }
  }
  if (!empty($loc_query)) {
    ++$criteria_met;
    $where .= "\nAND (". implode(' OR ', $loc_query) .')';
  }
  // Did we meet enough criteria for search results to be valid?
  if ($criteria_met >= 3) {
    return "SELECT t1.id id1, 5 weight\nFROM civicrm_contact t1 $join \n$where";
  }
}
?>

Cat Paquin · January 11, 2012, 02:24:32 pm

Unfortunately, I am not yet familiar enough with CiviCRM to apply that code. Is there a "getting started with hooks" topic that you would recommend?

Coleman Watts · January 11, 2012, 05:05:50 pm

http://wiki.civicrm.org/confluence/display/CRMDOC40/CiviCRM+hook+specification

basically if you create a simple module called my_code then you'd change the text woolman_website to my_code and just drop it all in place.

CiviCRM Community Forums (archive)

News:

Author Topic: Contact Name is changed if someone else joins with same email address (Read 1531 times)

pennyhaynes

Contact Name is changed if someone else joins with same email address

Coleman Watts

Re: Contact Name is changed if someone else joins with same email address

pennyhaynes

Re: Contact Name is changed if someone else joins with same email address

Aahar

Re: Contact Name is changed if someone else joins with same email address

Coleman Watts

Re: Contact Name is changed if someone else joins with same email address

Cat Paquin

Re: Contact Name is changed if someone else joins with same email address

Coleman Watts

Re: Contact Name is changed if someone else joins with same email address

Coleman Watts

Re: Contact Name is changed if someone else joins with same email address

Cat Paquin

Re: Contact Name is changed if someone else joins with same email address

Coleman Watts

Re: Contact Name is changed if someone else joins with same email address