I'd like to ensure that phone numbers of a certain type (let's say Mobile) are unique across our Civi instance. A given contact should be able to have no more than one Mobile number, and no two contacts should have the same Mobile number. We'd still like to take advantage of Civi's existing infrastructure around phone numbers -- in other words, we don't want to use a custom field instead of the phone number entity.

Ideally, the uniqueness rules would be enforced when data comes into Civi. But there are many ways that data comes in: backend forms, contribution pages, event registration forms, import, api, etc. If we wanted to alert users about duplicates during the various GUI-driven workflows, and also handle situations where a user isn't directly controlling the process (e.g. automatic syncs using the api), we'd need to create several different mechanisms.

Has anyone tried to do this, particularly with something like phone numbers which have a many-to-one relationship with contacts? (A question from 2015, Enforce uniqueness of custom field, is related, but I'm asking a broader question. Civi and its ecosystem of extensions have also evolved in the last 5+ years.) Are there best practices for enforcing uniqueness of a field/entity in CiviCRM, in a way that gives users a chance to correct duplicates during data input whenever possible?

  • just to add to the issue, you will need to be either forcing the format on data entry, or ensuring that all spaces brackets and dashes are dropped before doing the comparison with 'phone_numeric' field (which i only discovered when we went in to the rabbit hole of enforcing country prefix if contact was not in the home country. tricky shit it seems. – petednz - fuzion Feb 9 at 20:58
  • Interesting question ... at the db level, you could add a unique index on civicrm_phone.phone_numeric but that would not just be on mobiles and provides no flexibility for exceptions - and failures are unlikely to be presented meaningfully to the user. – Aidan Feb 12 at 10:13

I think hook_civicrm_check with the following SQL would handle notifications of duplicates:

SELECT phone FROM civicrm_phone GROUP BY phone_numeric HAVING count(*) > 1;

For back-end forms, you could modify the Supervised dedupe rules, though this wouldn't catch non-unique numbers belonging to different contact types.

  • Using the system status check is an interesting approach. We'd have to tweak that SQL for the exact rules I described, but I get the idea. It's not as immediate as I'd like, though. Ideally I'd like to catch duplicates during import right away. – Noah Miller Feb 11 at 17:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.