CiviCRM 5.29.0 - import relationships

Question

(2020 12 02; 13:27 US/PST: I've edited this question to more accurately reflect the data available for import.)

Can anyone point me at an example of two of importing a set of relationship data (e.g., is head or member of household) between an individual contact record and a (somehow) related household contact record? I have read through the available documentation several times, and have tried the import process in several ways, all to no useful results.

I am working with a small number of records (~27) of individuals to relate to (~17) households. Each individual and household record as a unique (but related) external identifier, and all related records have a record # in common among them, as well as a unique record number assigned by CiviCRM at the time of import. This number is not available prior to the import process and is not part of the import data.

I am working with WordPress 5.5.3 / CIviCRM 5.29.0.

Here are three example records (most fields omitted):

ID  ID1     UniqueID          Type  Relationship    Name
15  101015  1010150           h                     Hh Name
15  101015  1010151           i     HoH             HoH name
15  101015  1010152           i     MoH             MoH name

Household records have UniqueID ending in '0', Head of household individual records have UniqueID ending in '1', Member of household records have UniqueID ending in 2. This field is imported as CiviCRM's ExternalID. ID and ID1 look related in this example, they are not necessarily related. The UniqueID is created from ID1, which is an increasing series.

My approach has been to import individual records first, and then household records, with attempted links to individuals. This creates new individual records rather than updating existing individual records. I've read on SE that it may work better to create Hh records first and then import individuals. I will try that and update this question with results.

Once this small subset is functioning as expected, I then have about 2,000 records to import and establish relationships among individuals, households, and organizations. TIA, DVH

Answer 1

So your contacts (both individuals and households) already exist in CiviCRM and you have the contact ids for all of them - all you need to do is create the relationships.

I think part of your difficulty comes from the way your data is currently structured.

Create a file of your HoH's with just the contact_id of the individual and the household:

contact_id,household_id
10596,10594

Then at Contacts > Import Contacts:

• select your file
• tick 'First row contains column headers'
• select Update for Duplicate Contacts
• Continue

On the next page:

• map contact_id to Contact ID
• map household_id to Head of Household for Contact ID

Note: if Contact ID is not listed, go back to the previous page and make sure you select Update.

Run the import and you should find the relationships are created. Then repeat for MoH

However ... creating a file like that from your current one takes a little work.

Depending on your preference you could create that with Excel & Vlookup, or php/perl/etc

Alternatively since you have created external identifiers with a specific relationship between ID1 and UniqueID, you could use those:

• extract the contact_id and ID1 for the HoH lines
• append '0' to the ID1 field (to create the external identifier for the household)
• import as above but map to Head of Household for External ID

Update (following update of the question):

If you don't have the CiviCRM contact ids available, just use the external id's for both parties:

• extract the UniqueID and ID1 for the HoH lines
• append '0' to the ID1 field (to create the external identifier for the household)
• map UniqueID to External ID
• map modified ID1 to Head of Household for External ID

This was based on you already having imported the contacts and just needing to import the relationships. If you are starting from scratch you can import household and HoH in one go, then import MoH.

Answer 2

After reading other posts in SE concerning the import process and some experimentation, I have resolved my problem by revising the import process and modifying the imported data:

1. I changed the import order to:

   • Household data
   • Person 1 data
   • Person 2 data

2. I added a column to each of the files, "HouseholdName" and used it to provide the relationship import linkage. The revised table is, thus:

ID  ID1     UniqueID    Type  Relationship    Name       HouseholdName
15  101015  1010150     h                                Hhname
15  101015  1010151     i     HoH             HoH name   Hhname
15  101015  1010152     i     MoH             MoH name   Hhhame

With these changes, I am able to import my data, with the appropriate relationships established as part of the import process.