We have a site that has some legacy membership records with associated contributions that were imported manually (before our time, with a previous provider). We are experiencing problems where these legacy memberships do not renew as expected (new memberships that were set up since that time are working fine).
We went through a round of clean up fixes about two years ago and memberships started to renew as expected but they recently stopped working again after an upgrade.
Our assumption is that CiviCRM has changed the way that it does membership processing again and that it is now relying on a characteristic of the membership and its associated records that we did not address in our initial round of fixes.
We have come across different flavours of this problem on different sites in the past and I am sure lots of you have too. I am aware that there have been different ways of doing the processing and that it might change again in the future. We are keen to do a comprehensive clean up this time and I thought it would be worth getting some thoughts on how to proceed.
Our strategy is to compare DB records for succesful renewals with unsuccessful ones and to all identify differences, and then write scripts to bring them into line as appropriate.
So my first question is What tables should we be looking in?
civicrm_membership
civicrm_membership_payment
civicrm_contribution
civicrm_contribution_recur
Seem like obvious candidates. So does civicrm_line_item
though I am less clear about what we should be looking at in that table and how it connects to memberships and contributions. Are there relevant columns in civicrm_line_item
?
Are there other tables I should be looking in?
The following tables seem unlikely to be relevant but I add them here just in case and am happy to be corrected.
civicrm_financial_item
civicrm_financial_trxn
civicrm_entity_financial_trxn
Once we know all of the tables,
Are there specific ways I should be joining tables when doing the comparison? e.g. the first or last in a set of contributions related to a contribution recur?
What about template contributions? I am not sure if they are relevant in this case as there are no contributions with is_template = 1
but feel free to address this point in your answer :)
Any thoughts on the approach we are taking, and your experiences with / suggestions for alternative approaches are very welcome.
Finally, and in case it is relevant, the payment processor we are dealing with here is GoCardless.
Thanks all!