5

Hi this is our first season with CiviCRM. Our data is uploaded and getting to grips with updating processes.

Our process is annual membership collected in cash and banked in tranches.

The user front end is not really appropriate for the bulk process, manually looking up a member, renewing the membership with a contribution - which is slow enough - but then we need to go to the contribution to record a custom field ( banking id ) for later reconciliation.

A lot faster process would be to work off a downloaded spreadsheet, removing non paid members, and then uploading the contributions & membership.

But, as far as I can see, simply uploading the contributions through import process does not renew that membership - and bulk uploading memberships do not link to contributions.

I guess many orgs bulk upload contribution / memberships - how is this best achieved?

5
+50

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.

1

It seems you could do this by a batch entry of membership payments, the instructions for which are in the documentation at https://docs.civicrm.org/user/en/stable/contributions/manual-entry-of-contributions/

There is quite a bit to read in there but scroll down to "Configuring Profiles for Batch Entry of Contribution, Membership and Pledge Payments" where you should find all the information you may need.

The process uses a reserved profile "Membership Bulk Entry" in Administer>Customise Data>Profiles>Reseved Profiles

Hope this helps.

  • I saw the batch entry - but it is missing the all important part uploading the batch from a csv. As it stand you have to manually select each 'member' by typing their name. If you have a list of member ids, in a spreadsheet (downloaded from a report showing the member ID) then dealing with the data 'offline' has to be the fastest way of dealing with thousands on renewal. – Alan Fuller Nov 7 '16 at 18:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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