Answers.Body: <p>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.</p>
  
  <p>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.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-01-09T22:21:17.760Z
Answers.Text: 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.
Answers.UserDisplayName: Mike S
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-01-09T20:45:03.613Z
Answers.LastActivityDate: 2017-01-09T20:45:03.613Z
Answers.UserDisplayName: Jon G - Megaphone Tech
Body: <p>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. </p>
  
  <p>Bulk Import of Membership Dues  - Jon G's Answer</p>
  
  <p>It sounds like your process of importing memberships via CSV and contributions via CSV works - except that your contributions and memberships aren't linked.</p>
  
  <p>If that's correct, I've resolved this when doing migrations as follows:</p>
  
  <blockquote>
    <p>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.</p>
  </blockquote>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-01-17T00:18:34.380Z
Comments.Text: 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.
Comments.UserDisplayName: Karl Rudnick
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-01-17T00:22:11.280Z
Comments.Text: 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?
Comments.UserDisplayName: Karl Rudnick
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-01-17T00:24:32.020Z
Comments.Text: 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!
Comments.UserDisplayName: Karl Rudnick
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-01-17T01:18:49.313Z
Comments.Text: 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.
Comments.UserDisplayName: Karl Rudnick
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-01-17T01:42:02.523Z
Comments.Text: [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]
Comments.UserDisplayName: Karl Rudnick
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-01-18T02:38:54.623Z
Comments.Text: Now, I was able to add the same externial_serial to both the memberships and contributions from our last bulk import by just importing to the same memberships and contributions by updating instead of importing new. When we did the original import we also tagged all the contacts in that import. Using the tag and the date range of the contributions and Advanced Search > Export, it was fairly easy to create the two column spreadsheet with the CiviCRM-assigned Membership ID and Donation ID. No SQL or cli.php required.
Comments.UserDisplayName: Karl Rudnick
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-01-18T05:51:36.283Z
Comments.Text: Jon G - Thanks also for the API CSV Import GUI tip. Mike and I are learning more and more each day about all the really great stuff within CiviCRM. We got it all to work on a subset of about 120 membership records. Very slick. Also your suggestion for adding the external_serial after the fact seems to work well, but you really don't need it as long as you can align the membership with a contribution - contact ID and dates get you there about 99% of the time in our example.
Comments.UserDisplayName: Karl Rudnick
Tags: <civimember><import><contributions>
Title: Importing Memberships and Contributions but can't seem to link the two together
ThreadText: # Importing Memberships and Contributions but can't seem to link the two together
  
  (Asked by: username_0 on 2017-01-09)
  
  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.
  
  **Tags:** civimember, import, contributions
  
  > **Comments:**
  > **username_1**: Hi Jon - I think you have just the right approach. I am working with username_0 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.  
  > **username_1**: 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?  
  > **username_1**: 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!  
  > **username_1**: 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.  
  > **username_1**: [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]  
  > **username_1**: Now, I was able to add the same externial_serial to both the memberships and contributions from our last bulk import by just importing to the same memberships and contributions by updating instead of importing new. When we did the original import we also tagged all the contacts in that import. Using the tag and the date range of the contributions and Advanced Search > Export, it was fairly easy to create the two column spreadsheet with the CiviCRM-assigned Membership ID and Donation ID. No SQL or cli.php required.  
  > **username_1**: Jon G - Thanks also for the API CSV Import GUI tip. username_0 and I are learning more and more each day about all the really great stuff within CiviCRM. We got it all to work on a subset of about 120 membership records. Very slick. Also your suggestion for adding the external_serial after the fact seems to work well, but you really don't need it as long as you can align the membership with a contribution - contact ID and dates get you there about 99% of the time in our example.  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_2 on 2017-01-09. Score: 3 | Accepted answer
  
  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.
  
  > **Comments:**
  > **username_0**: 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.  
  
  ---
  
  