CiviCRM Community Forums (archive)

Household relationship imports have not been going the way I expected -- hopefully someone here can suggest a path to take...

Here is what I have done. Importing from SQL, I have tables in MySQL with the data to be imported into CiviCRM. I have Households already in CiviCRM and next need to import members of the Households as Individual contacts.

Both data types have their own external IDs but they conflict. Household has household_id and member has member_id, but there can be household_id=30 and member_id=30 for instance. So I could not use the External ID field in CiviCRM. Instead I created custom fields, Household_ID::Household Data and Member_ID::Individual Data. This works great for the de-duping, I added two new rules, one for each ID and contact type and it works to allow updating.

The problem comes when I try to create the relationship. I.e. household Jones and person John Jones. John Jones should be a Household Member Of the Jones household. At first, I set up the field mapping as follows (just the relevant fields).

member_id >> Member_ID::Individual Data
household_id >> Household_ID::Household Data
...
household_name >> Household Member Of, Household Name (match to contact)*

This worked when I tested with one record. But then I tested with all records where last name=Smith (there are multiple Smith households). Now all of the Smith individual contacts are members of just one of the Smith households. It looks like the first match was the one used.

OK, I changed the above to remove the household_name (since it was not unique) and changed household_id:

household_id >> Household Member Of, Household_ID::Household Data

I figured this should work because it is unique. What I get is, no relationship from the households to the individuals. And the individuals do have a relationship Household Member Of, but the household is empty.

It seems I can only get it to create a relationship to the household if I use the household name, but again that is not a unique field and I have problems for households whose name is the same as others.

Thanks for any ideas/suggestions...

Thanks, that worked. To test, I chose a Household with a name that is matched by one other one. I put the household_id (from the external system) into the External ID field in CiviCRM for each of the two Households. Then I imported three Individuals, two belong to one Household and one to another. I changed the mapping to have:

household_id >> Household Member Of, External ID

This time the three contacts ended up properly related to the two Households they belonged to.

I have another issue now however, I had not mentioned it for simplicity but there is another relationship I need to import as well for the individuals, to an Organization. Some of the Orgs have an external ID that would be identical to some of the Households. I may be able to use the Org Name however, I did successfully use that earlier for some Households that were also related to the Orgs. Fortunately the Org Names are unique.

It sounds like you have a lot of conflicting ids in your external systems. Perhaps one fairly easy solution would be to give each type of id a unique prefix: i.e.
Household id 123 becomes hh123
Organization id 123 becomes og123
and so on.
So when you export from your ext systems you add the prefix in your spreadsheet and then civi can tell the difference.

I used a system similar to this when importing a whole series of organizations, spouses, families, etc.

I had a whole scheme where our original member ID number of, say 1223 would become

 1223 - for the primary member
 1223G - for the related group or organization
 1223S - for the spouse related to the primary member

etc.

I imported this into the foreign key (because that seems to work best for a lot of import-related things) and also to a custom field which I called "Previous System Membership ID" or something of that sort.