Membership and Contributions Import dots not connected

[mkpinformation]

So, hi.

Here's where I'm at.  I've got 2010 memberships and their accompanying contribution/dues that I've been importing.   It's the last, probably most complicated part of the importing I've been doing to get set up and ready to roll forward.

I set up member types and contribution types.  My member types use the contribution type "membership".

When I imported memberships I wasn't given the option of including AMOUNT in the same way it seems the system is set up to allow me to create a membership manually and process or record a payment at the time.

So I divided the process importing the $ as contributions (contribution type membership) and the memberships without $.

So, of course my membership report has no money in it.  I guess I shouldn't be surprised but I'm not sure what else I should have done to get it that way. 

I can Find Contributions by type and isolate the $ raised in that way... but it's clunky and separate from the membership data itself.  I suppose when I have a cart up and people 'join and pay' at the same time, and/or 'renew and pay' at the same time, it will link the $ with the membership data moving forward.

But I'd really like to get this 2010 information all joined up.

Help?

[petednz]

Hi - yes I think this is a known gap - Eileen worked around it in a couple of ways so she may be able to give you some pointers - one was via using Migrate module to do the import - see http://civicrm.org/blogs/eileen/civimigrate-–-how-i-got-on…-incl-pledge-imports and http://civicrm.org/blogs/eileen/civi-migrate-proof-concept

Otherwise I think it requires some sql trickery to either make the connection or at least make the membership have a setting for a Contribution page, so that these members will get a Renew Now on their Dashboard.

Hopefully you will get more specific help from others.

[mkpinformation]

Thank you Pete!

[sjthespian]

I just went through this same thing, due to my organization giving free memberships to all attendees of a conference we support. You can see my full description of what I was trying to do an my solution here:
  http://forum.civicrm.org/index.php/topic,18825.msg78204.html

The short answer: I had to import my membership and contributions separately then write a script that would walk the database and tie the contributions to the memberships. It's ugly, makes some possibly incorrect assumptions (like always tying the contribution to the most recent membership) and is very custom for our site, but I'm willing to share the code if you want to take a look at it.

[Donald Lobo]

hey sjthespian:

Seems like you are doing some interesting stuff with regard to getting civicrm work for your needs. Would be great if you can combine your forum posts and code snippets into a blog post with a more detailed explanation and background. This might help other people in similar situations / give folks some things to ponder about with regard to making

Let me know if interested, and i can give your drupal account blogging privileges.

thanx

lobo

[collinsm22]

Here is some PHP code I worked up to solve this specific problem.  I'm just getting started with CiviCRM, so I don't have the slightest clue how this be incorporated into a patch or a hook, or whatever, but it might save someone else some time.  I made some assumptions with this code.

• The contributions and memberships were imported so the contribution receive date matches the membership start date or join date (it looks for both)
• the contact ID matches in both contribution and membership (duh)

The script is intended to be run multiple times.  Each time it is run, more and more information can be populated, or more information is given to troubleshoot missing or incorrect data (an incorrect contribution receive date, for example).  It won't overwrite or update existing links between memberships and contributions.  It simply add links where they were missing, and unique matches were found.

Michael

The forum won't let me paste the code in here for some reason.  You can find it at chicago-orienteering . org / civicrm_code . htm

[Donald Lobo]

hey collinsm2:

thanx for the code contributions. a few thoughts and comments:

1. Might be worth deploying your code on github. You get version control / issue tracker that way.

2. Might want to convert your code to a civicrm extension, check:

http://wiki.civicrm.org/confluence/display/CRMDOC/Extensions
http://wiki.civicrm.org/confluence/display/CRMDOC/Create+an+Extension

3. The developer book will also be a useful guide:

http://book.civicrm.org/developer/

feel free to ask for help / assistance on irc

lobo

[Michael McAndrew]

Hey there,

Definitely a need for this.  Just a thought - there is a membership payment api that might be useful here, e.g.

http://drupal.demo.civicrm.org/civicrm/api/explorer#/civicrm/ajax/rest?json=1&sequential=1&debug=1&&entity=MembershipPayment&action=get

[flug]

Here is some PHP code I worked up to solve this specific problem.  I'm just getting started with CiviCRM, so I don't have the slightest clue how this be incorporated into a patch or a hook, or whatever, but it might save someone else some time.  I made some assumptions with this code. . . .

chicago-orienteering . org / civicrm_code . htm

This worked for me--thanks!

[The Doctor]

I recently converted our system to CiviCRM and I have the same issue. I am not a coder, so I don't know how to make this happen. Could someone please post instructions for how to install and run this script? Thanks in advance.

[collinsm22]

Do you still need help with the script?  I was away for a while, but I'm back again.

[The Doctor]

Michael,

Yes. Please. I haven't got a clue how to begin. Thanks.

[collinsm22]

Okay.  I know you're not a "coder," and I don't know what server software you're using, so I'm going to make rather generic instructions.  There's probably going to be something here you don't understand, but at least we can start narrowing this down.

• Go into your web site's control panel (aka cPanel), and look for "File Manager"
• In the File Manager, look for a directory called public_html.  Create a new file (not a folder) in there and call it civicrm_fix.php.
• Edit this file.  It should be blank to start.  Copy and paste the code from the link in the previous post and save the file (but don't close it yet). (BTW, I just updated the code, so get a fresh copy, if you last looked at it last year.)
• Lines 3, 4, and 5 need to be changed to the appropriate values for your CiviCRM database name, user name, and password.  If you're on a shared server, this is probably something like loginname_civicrm, where loginname is what you typed into cPanel to log in.  The password, of course, will be something else.  If you have no idea what I'm talking about, you might look in the MySQL section of the cPanel, to see if anything looks like it might related to civicrm.  If you still don't have any idea what I'm talking about, then you probably have a different web host management system, and my instructions might not be helping you at all.   
• Once you have updated the values in lines 3, 4, and 5, save the file once again, and this time close it.
• Open up a new web browser window, and open this php file (by an address like yoursitename.org/civicrm_fix.php.

The file will look in your civicrm database, and attempt to match up orphaned membership and contribution files.  Since you're not a coder, you probably haven't made any changes to the structure of your database, so this script should cruise right along.

So, let me explain what you are seeing.

The first column is the contribution ID (unique number for the payment).  The second column is the ID of the person linked to the payment.  The third column is the Contribution Type / Financial Type, which will probably be "2" for most of your records, since you're trying to match up orphaned membership records.  The fourth through eighth columns are just more information about the contribution to help you see what's going on if things look goofy or don't match up.

The ninth column is where the magic happens.  Either you'll see a membership ID (a number 1-10000, probably), which means that a a link already exists between the contribution and a membership record.  Or, you'll see the word "Missing," which means that a match doesn't already exist.

If the link is missing, the script will go see if any memberships with the same Contact ID and either Join Date or Start Date match with the Contribution Receive Date. 

If it finds multiple matches, it displays the word "Multiple," which means you'll have to go in by hand into CiviCRM or your database, figure out which ones belong together, and manually link them.  If you're lucky, there won't be too many cases of this, as most Contact IDs are only going to have a single membership record for a given date.

If it doesn't find any match, you'll get the dreaded word "No Member Found," which means you'll have to hunt down the record yourself, since the dates and IDs don't exactly match up.  This probably happened because the dates were messed up in your source data.  I wouldn't expect Contact IDs to get messed up as often as the dates might, depending on your source data, and how financial records were kept and tied to membership renewals.

Under most circumstances, however, it will find one, and only one membership record with marching Contact ID and date.  The script then goes ahead and creates the link in the civicrm_membership_payment table.  Then, it searches again for the linked records (to make sure the link "took").  If everything went according to plan, you'll see a bold, green Contact ID (i.e. a number in the 1-10000 range).  The rest of the row might be empty, especially the first time you run the script, but that bold green number is the sign that everything is going according to plan.  When you refresh the page, the green number will be replaced with lots more information about the membership record, and the matching contribution/payment.

If something goes wrong, and it can't create the link, or it can't find the link it just created (sometimes stuff happens, you know?), then you'll see a red "Fail."  This lets you do a little more detective work, as seeing "Fail" means the script thought it found a solution, but it didn't work for some reason.  So, for any "Fail," write down the Contribution ID and Contact ID, and go into CiviCRM to take a closer look at that record, to see if the memberships and payments/contributions are there. You might be able to fix it by hand from that screen, or you may have something more troubling going on.  Let me know if you get this for more than 1 or 2 records.  It means you have a situation that the script wasn't built to handle.

If your data is in good shape to start with, and looked at all like mine when I built the script, you should see a whole lot of bold green numbers the first time you run the script, and when you refresh the page (and re-run the script), the green numbers should be replaced by a lot of complete-looking data.  That's all it took for me.  If you get something else, let me know, and I'll ask you for more details of your set-up, etc.  I might also ask to see the output of the script, which might give me some clues as to what's going on.  Don't post that here, through, as there may be some information you don't want posted for the world to see.

If you get a blank white page when you run the script, it probably means you don't have the correct settings to get into your database.

When you're all done.  I suggest deleting the script, or at least disabling by removing the password from line 5.  That way, it won't get stumbled upon by a search engine or someone looking to mess with your system.  I make no promises as to the security of the script.

Let me know if you have any questions.

Michael

[petednz]

wow awesome instructions M.

[The Doctor]

Michael,

Thank you so much for taking the time to write out these detailed instructions. They are all understandable to me. Now I have to get up the courage to implement it. 

Much appreciated!