Problem with multiple 'home' email addresses, phone numbers, etc.

[flug]

I've noticed a bit of a systematic problem in CiviCRM regarding email addresses & phone numbers that can be selected with different locations such as home, work, billing, etc.

The problem happens when you have, for example, two phone numbers or two email addresses that are both identified as 'home'.

Just for example, in one situation we had 100 people registered for an event.  But on downloading the list (search/export participants) we discover 107 people downloaded.  Careful examination shows that 7 people are double-listed.  Even more careful examination shows that one of the export fields was "home phone" and each person with two home phones listed in their contact page was listed twice.

The same thing happens if they have two home email addresses (or two work email addresses, etc).

Just now I had strange situation where a contact's address was listed on his civicrm contact page but  on his Drupal account page, where is contact info is viewable and editable via a civicrm profile, the primary address/city/state was all showing as blank.  Editing the blank fields and saving resulted in creation of a second address for the contact.

The problem turned out to be that the contact had two email addresses both labelled as 'home'.

The problem seems to be in the SQL searches for this info, where if you are not careful to specify, a left join on the email table with contactid=emailid and location_type_id=1 brings in two rows if the contact happens to have two email addresses with location_type_id=1.

In a situation like the profile, only one of the two can be displayed, so you basically have a 50% chance of it working just fine or malfunctioning.

I'm bringing this up here because it seems to be a bit of a systematic thing where the SQL wherever joins with email & phone tables are used need to be refined a bit.  Perhaps there is a systematic way to do this or perhaps you all have ways to deal with this situation?

It does seem to be a problem in that the interface cheerfully allows you to enter multiple email & phone numbers under 'home' or 'work' or whatever (which IMHO is reasonable) but then this leads to unusual and hard-to-debug behavior down the line.

Perhaps the solution is to enforce just one email/phone # per label, and if you want to save a bunch of them them create a bunch of different labels. 

Any thoughts?

[JonGold]

I just tested trying to add an address with the same location type as an existing address, and received a warning that I couldn't.  So it's my understanding that this shouldn't be possible, and that's how this problem gets solved.  However, I also agree that a) this hasn't always been the case, and b) there are plenty of instances in which Civi will cheerfully allow you to add a second address (etc.) with a location type matching an existing address. 

I broadly agree that allowing only one address per location type is a good idea because it means that there's little question about whether a new address added via a public-facing interface (e.g. a contribution page) should overwrite or coexist with an existing address.  However, there's some work to be done to make it harder to actually enter multiple addresses for a single location type.  I also don't think there's a good way to handle this in 100% of situations - for instance, I've written extensions that change location types.  Still - I think that given the direction Civi's gone in, the correct solution to this is to identify situations that allow multiple addresses per location type and report them as a bug.

I'm also hopeful that 4.5 will take some of the pain out of this for end users, since you should be able to edit location types more easily from the edit screen, in case more location types are needed.

[Eileen]

I suspect the api doesn't enforce the 1 location per type which is the cause of some of the duplicates (code using the api). It seems reasonable that the api / BAO would enforce this rule

[flug]

I'm still on 4.2 so that might be part of my problem.  It doesn't seem to enforce anything in regards to multiples of any particular type of addresses, phone numbers, email addresses, whether 'home', 'work', or whatever.  Also I think we tend to get multiple locations for address, phone, and email when merging duplicate records.  Must often my objective there is to not lose any information, but I do want to mark which is (most likely) the best or preferred address, while also keeping the alternative addresses, phone #s, emails, in case the one I think best turns out to be incorrect or outdated.

And . . . it does seem a bit problematic to enforce this, in that a single person can actually have multiple home email addresses, multiple work phone numbers, etc.  I guess we can just work around this by creating multiple location types.