CiviCRM Community Forums (archive)

We have made the mistake in the past: We have created/imported contacts and didn't assign state/country "information to them". The contacts are related to organizations though that have some information (city, province, country).

So I wrote a query that figures out whether contacts have a relation to an organization (employee relation) and if this organization has an address and the individual itself does not, it copies the address.

Everything seemed to work fine, but for some reason the state/city/province does not appear when we list the contacts and you also can't search by the province for example. When you look at the details of the contact though, the address is shown.

What am I missing? Do I have to modify some other properties/tables?

INSERT INTO civicrm_address
(contact_id, location_type_id, country_id, state_province_id, city)
SELECT c.id, location_type_id, country_id, state_province_id, city
FROM civicrm_contact c
  JOIN civicrm_relationship r ON
    r.relationship_type_id = 4
    AND r.contact_id_a = c.id
  JOIN civicrm_address addr ON
    addr.contact_id = r.contact_id_b
    AND addr.location_type_id = 2
WHERE contact_type = 'Individual'
AND NOT EXISTS (
  SELECT 1
  FROM civicrm_address a
  WHERE a.contact_id = c.id
  AND a.location_type_id = addr.location_type_id
);

thanks, that worked.

I initially just tested this with one contact before I posted the question, and it didn't seem to work. But now it works and I can't reproduce why it wouldn't have worked before.