2

I have run into a problem where someone created a Webform using Webform_CiviCRM and for some reason added two address field sets to the contact, with the street address in one of them and the city/state/zip in the other. Now the contacts in CiviCRM have two incomplete addresses, both of the type 'Home'. When I try to export them, I'm just getting one of the incomplete addresses.

This old forum thread seems to be related. It looks like the UI on the main contact page prevents creation of multiple addresses of the same type, but the API must still not enforce?

UI Error message RE: addresses

Anyone have a suggestion for how to tackle this and get the addresses merged/squashed into a single address?

2

I would fix this either partially or wholly with SQL. The general approach I'd use is:

  • Change the location_type_id on one of the two addresses for each contact to an unused location type (creating a new location type if need be).
  • Export the contacts with both the "Home" and the unused location type.
  • Reimport the data with all address fields designated as "Home" addresses.
  • Delete the extra addresses with a SQL statement.

The basic query you want is:

SELECT MAX(id), contact_id, COUNT(id) FROM civicrm_address WHERE location_type_id = 1 GROUP BY contact_id HAVING COUNT(contact_id) > 1;

MAX(id) is the id of the second address created; MIN(id) would return the first address id.

Due to this constraint of MySQL, you can't just use this SQL query that would meet the requirements of the first bullet point:

UPDATE civicrm_address SET location_type_id=4 WHERE id IN (SELECT MAX(id), contact_id, COUNT(id) FROM civicrm_address WHERE location_type_id = 1 GROUP BY contact_id HAVING COUNT(contact_id) > 1);

You can use of the answers on that SE question to work around that (e.g. with a temp table).

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.