Author Topic: Important Member Families (What ID do I use?) (Read 926 times)

mm2000 · June 25, 2012, 11:56:54 am

Hey all!

Thanks for taking the time to read/help me with this. I'm brand new to CIVICRM and have been playing around with it. We currently use a program built from the ground up for Membership, and are thinking of switching to CIVCRM.

My questions is this. We have our database setup by Member Family. What I mean is that we have essentually two members for each family (Member 1 and Spouse). There is a single piece of infromation we use to identify the Member Family as a whole (what we call Member ID). This Member ID is used to keep track of who is each family (the Member, Spouse, and Children).

I want to know what the best way to import these people are. I tried using the External ID, Internal ID, and Household Name. The only one that seemed to group them into a family was Household Name. But when I tried to import via Household Name, I can't seem to match it up to the children.

What is the best way to import this information?

Thanks in advance!

JonGold · June 25, 2012, 12:21:11 pm

Hello mm2000,

You'll most likely want to handle this by creating relationships. Do you have a way to differentiate the "member 1" folks from everyone else?

The best way to do this is to create a list of "member 1" folks, and a list of everyone else. Then, in the "everyone else" list, create a new column with the external ID of the corresponding member 1.

So a quick example:
ID MemberID Name Member 1
66 2222 Jane Smith yes
67 2222 Joe Smith no
68 2222 Kim Smith no

Break this into two lists - one with Jane (and all the member 1s) and one list with everyone else. Then, add a new "member 1 ID" column to the second list, and use a VLOOKUP (assuming you're using Excel or LibreOffice Calc) to populate that value with the id of the corresponding member 1. So your two lists look like this now:
ID MemberID Name
66 2222 Jane Smith

ID MemberID Name Member1ID
67 2222 Joe Smith 66
68 2222 Kim Smith 66

THEN, when importing the second list, import the Member1ID field as a related field, and it will automatically create a relationship.

This explanation assumes a lot about how your data is modeled, but the general approach should work for you!

mm2000 · June 25, 2012, 01:30:33 pm

So I'm essentially creating a second ID.

What do I map the ID fields as (on the import)?

JonGold · June 25, 2012, 01:35:59 pm

You map the ID fields to "external identifier", assuming your ID fields are coming out of your legacy system.

You're not actually creating a second ID in CiviCRM - the "member1ID" isn't actually imported into CiviCRM. You're just saying, "Link this member I'm importing to an existing record that has this ID". Does that make sense? I can try to explain it differently.

mm2000 · June 25, 2012, 01:44:56 pm

You're last sentance made perfect sense and that's what I was trying to figure out. What filed I should use to connect everyone with.

In our Legacy system, we have a specific Member ID, which is the field that I thought we would map to continue to keep the family together.

The next question is, do I import just Member 1 first, then everyone else, linking them together through the Member ID?

Second, when importing the spouse and children, do I import them as individuals, or as a household?

JonGold · June 25, 2012, 01:56:51 pm

It's a good question. That really depends on your data model and what you intend to do with the data. That's a little bit more intensive consulting than I can do on the forums though

In CiviCRM, you don't need to have a field in common to connect the records - that's what relationships are for. Try playing with/reading up on relationships and you'll get a feel for them in about 3-5 minutes - I think that'll answer a number of questions.

"do I import just Member 1 first, then everyone else, linking them together through the Member ID?"
Yes.

You'll definitely import the spouse/children as individuals. Whether you create a "household" entity is up to you. From my earlier example, I'd just modify the "member 1" list so that instead of an individual's name, it was "The Smith Family", and reimport that list. However, these days in CiviCRM I rarely have a use case that requires I use households at all. It depends on your use.

mm2000 · June 25, 2012, 03:22:40 pm

I was trying to find a tutorial on CIVICRM imports but couldn't find one that talks about relationships. Do you have any recommendations?

petednz · June 25, 2012, 06:30:02 pm

I doubt you will find one. Jon has offered you some great pointers.
Doubt this will help anymore than his advice would have done but as he says key thing to get your head around is
Person A <relationship> Person B (or Household B)
Therefore Import looks like
- Person A maps to Person A (of course you have first/last names etc)
- Person B maps as the 'this contact has the following relationship' and 'this is the field I have about them to match against'

Also I concur with Jon that 'households' as a Contact Type are not something we use generally to manage such inter-family relationships. only if there is actual stuff that you want to record against the 'household' as an entity

CiviCRM Community Forums (archive)

News:

Author Topic: Important Member Families (What ID do I use?) (Read 926 times)

mm2000

Important Member Families (What ID do I use?)

JonGold

Re: Important Member Families (What ID do I use?)

mm2000

Re: Important Member Families (What ID do I use?)

JonGold

Re: Important Member Families (What ID do I use?)

mm2000

Re: Important Member Families (What ID do I use?)

JonGold

Re: Important Member Families (What ID do I use?)

mm2000

Re: Important Member Families (What ID do I use?)

petednz

Re: Important Member Families (What ID do I use?)