4

I'm using the latest CiviCRM Wordpress version and have imported thousands of contacts and their memberships. The problem is when I attempt to import the associated contributions they seems to be no way to linked the contributions to their memberships. Jon G has answered a similar question (below) but I'm not sure I can use his advise as I have already imported the memberships. Any an all advise welcome.

Bulk Import of Membership Dues - Jon G's Answer

It sounds like your process of importing memberships via CSV and contributions via CSV works - except that your contributions and memberships aren't linked.

If that's correct, I've resolved this when doing migrations as follows:

Create a custom field for contributions called "external serial" or similar. Create a custom field for memberships also called "external serial". Add a serial number column to your CSV (if one doesn't already exist) before importing it as contributions/memberships. The only important part is that each row have a unique identifier. After import, you can use the shared identifier to create a new CSV with just two columns - membership_id and contribution_id (as CiviCRM assigned them). I typically would do this via SQL. Something like this: SELECT cc.id AS contribution_id, cm.id AS membership_id FROM civicrm_contribution cc JOIN civicrm_value_importfieldcc_1 cvi1 ON cc.id = cvi1.entity_id JOIN civicrm_value_importfieldcm_2 cvi2 ON cvi1.external_serial_1 = cvi2.external_serial_2 JOIN civicrm_membership cm ON cvi2.entity_id = cm.id Create membership payments using this new CSV to link the memberships/contributions. You can do that with the API CSV Import GUI extension or the command-line tool that ships with CiviCRM.

Improve this question
7
  • Hi Jon - I think you have just the right approach. I am working with Mike here and both of us are quite newbies to CiviCRM and even to putting together precise SQL queries on our own. I would like to set up a shell script to use with cli.php to do what you want. I have two questions and sorry if they seem trivial.. we are just beginning to learn.
    – Karl Rudnick
    Commented Jan 17, 2017 at 0:18
  • 1. How exactly do we add "external_serial" to both membership and contribution tables civicrm_membership and civicrm_contribution? Is that part of the CiviCRM backend or do we need SQL?
    – Karl Rudnick
    Commented Jan 17, 2017 at 0:22
  • 2. Assuming we use "external_serial" in both tables to do the link, can you tell us the correct command to use with cli.php? Any help is much appreciated, even any documentation links just to make sure we get it right. We may be doing this on occasion, especially when we may be gathering contacts, membership, donations at live events via a tablet or mobile device, etc. THANKS for your expert help!
    – Karl Rudnick
    Commented Jan 17, 2017 at 0:24
  • Sorry. I just realized that I can add "external_serial" to both contributions and memberships via CiviCRM > Administer > Custom Data and Screens > Custom Fields. Any help on the second question is still appreciated.
    – Karl Rudnick
    Commented Jan 17, 2017 at 1:18
  • [EDIT to last comment: but this just creates custom field sets, extra rows in our DB. Do we want to add fields directly to civicrm_membership and civicrm_contribution? That seems like a bad idea as CiviCRM versions evolve]
    – Karl Rudnick
    Commented Jan 17, 2017 at 1:42

1 Answer 1

Reset to default
3

The key to my instructions are that there is some unique identifier in both your memberships and contributions. If you don't have one, then you're correct, there's no way to link the two.

That said - you can use some trickery to create a unique identifier after the fact. For instance, if your import data has a contact ID and a transaction date, and you know that one contact never signs up for two memberships in a day, you can create a unique ID based on the data you've already imported to the memberships.

Improve this answer
1
  • Thanks Jon, Yes, I have external IDs for all so maybe I can use that data along with the contribution date to make the link? Otherwise I'm hosed. Another thought I had was; "What does it matter if the membership doesn't show complete?" All we really want is the expiration date of the imported membership to trigger email reminders to renew. I'm hoping that will at least work.
    – Mike S
    Commented Jan 9, 2017 at 22:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.