CiviCRM Community Forums (archive)

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?

Thank you Pete!

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

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

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!

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

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

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!