Importing Household Address if Already Exists

Question

I've seen several requests for this, but some seems outdated. Goal: Import contacts and households and update existing records. I'm using http://forum.civicrm.org/index.php?topic=29506.0 as a reference. Should it work for contact updates?

1. Using 4.6.3 and import GUI.
2. Import household contact with primary home address (complete)
3. Associate relationship for contacts as Member of household (complete)
4. Associate relationship for contacts of Spouse (TBD but suggestions welcome)
5. Associate contacts as having Master Address belonging to household

This seems like a very common use case and I'm almost there...now attempting steps 4 and 5.

Status:

• Created csv with household info and imported successfully.
• Used mysql to collect master address record id.

When importing individuals:

• Selected Update method
• I selected "Master Address Belongs To..." and included the Address ID from mysql - Import successful but Address is not updated for existing contact addresses and does not belong to household

Could this be related to: CRM-16588?

So now I have some related addresses (although I have to manually save to show them properly in view screen) but others belonging to individuals. I suppose I could delete all the individual addresses and reimport but don't like that idea ....

BTW perhaps this could drive a rather large feature request/refactor: Rather than import master address belongs to ... use the same relationship method but enable an option like "Let Household fields override Household Member" Perhaps this could work like relationship permissions. This could mean any updates to a contact address would update the linked field (consistent with how event pricesets/address work...)

Answer 1

If the households and individuals exist in the database but individuals do not have a shared address with the household, you can run the following sql to create a csv that you can import.

On the import use update and link the first 7 columns to the appropriate columns. (If you don't link the street address, city, state, etc., the individual's shown address will be whatever it was before). The last two columns are just for sanity so you can double check the csv is going to update the right things.

select r.contact_id_a internal_id,
       ah.street_address,
       ah.city,
       p.name state,
       ah.postal_code,
       co.name country,
       ah.id master,
       ci.sort_name Ind_name,
       ch.sort_name hh_name
from   civicrm_relationship r,
       civicrm_contact ci,
       civicrm_contact ch,
       civicrm_address ah,
       civicrm_country co,
       civicrm_state_province p
where  r.contact_id_b = ah.contact_id
and    r.contact_id_a = ci.id
and    r.contact_id_b = ch.id
and    r.relationship_type_id = 7  -- hh
and    ah.master_id is null  -- make sure hh isn't mastered off ind
and    ah.is_primary -- use the hh primary address
and    ah.country_id = co.id
and    ah.country_id = p.country_id
and    p.id = ah.state_province_id
and    not ci.is_deleted
and    not ch.is_deleted
and    not exists (select 'does individual have master address id'
            from   civicrm_address ci
            where  ci.contact_id = r.contact_id_a
            and    ci.master_id is not null)

If you did an import update and only linked the master address you fix the displayed address of the individuals with sql.

Here are two queries. One lists the people with a master address but missing the details. The other fixes them.

select ca.street_address,
       ca.city,
       ca.state_province_id,
       ca.postal_code,
       ca.postal_code_suffix,
       ca.country_id,
       pa.street_address,
       pa.city,
       pa.state_province_id,
       pa.postal_code,
       pa.postal_code_suffix,
       ca.country_id
from   civicrm_address ca,
       civicrm_address pa
where  ca.master_id = pa.id
and    ca.master_id is not null
and    ca.street_address is null

Fix missing

update civicrm_address ca,
       civicrm_address pa
set    ca.street_address = pa.street_address,
       ca.city = pa.city,
       ca.state_province_id = pa.state_province_id,
       ca.postal_code = pa.postal_code,
       ca.postal_code_suffix = pa.postal_code_suffix,
       ca.country_id = pa.country_id
where  ca.master_id = pa.id
and    ca.master_id is not null
and    ca.street_address is null

Answer 2

A workaround is to import the address along with the Master Address ID. So import like you are importing a new address but also include the Master Address ID, mapped to the "Master Address Belongs To" field. It links them and populates the address.

Answer 3

This is in answer to the how to create spouses from household members.

Here is some sql. You can use it to create a csv to do an import update individual or paste into the Specify SQL Query box after selecting Specify SQL Query for data source.

You'll have to manually link the second column to Spouse Of Internal Contact ID. The others should default correct. The last three do-not-import columns are just for sanity sake to make sure things look correct.

Notes about sql: If the spouse relationship already exists it will skip that one. If the household is made up of more than two individuals e.g. husband, wife, kid1, it will make two of them spouse and ignore the third. Not sure which two will be used.

SELECT rh.contact_id_a internal_id,
       ri.contact_id_a 'Spouse of',  -- on import link to spouse of internal id
       ch.sort_name '-- Household',  -- do not import
       cs1.sort_name '-- Spouse1',   -- do not import
       cs2.sort_name '-- Spouse2'    -- do not import
FROM   civicrm_contact ch,  -- household
       civicrm_contact cs1, -- first spouse
       civicrm_contact cs2, -- second spouse
       civicrm_relationship rh,  
       civicrm_relationship ri
WHERE  ri.contact_id_b = rh.contact_id_b
and    ri.relationship_type_id = 7
and    rh.relationship_type_id = 7
and    ri.contact_id_a <> rh.contact_id_a
and    rh.contact_id_b = ch.id
and    rh.contact_id_a = cs1.id
and    ri.contact_id_a = cs2.id
and not exists (select 'is there already a spouse relationship'
                from   civicrm_relationship re
                where  (rh.contact_id_a = re.contact_id_a
                   or   rh.contact_id_a = re.contact_id_b)
                and     re.relationship_type_id = 2) -- spouse
group by rh.contact_id_b  -- sneaky way to get only one row for each pair otherwise get two rows