Answers.Body: <p>This is not a fun task, but at least I made a tool to make it pretty.</p>
  <p>If you install <a href="https://lab.civicrm.org/extensions/uhoh/" rel="nofollow noreferrer">Uh-Oh troubleshooting extension</a> it includes a diagnosis page at <strong>Support » Map Contributions</strong> which lets you specify contacts, contributions, recurs, and memberships, and it will graph the links as shown here. It's not GoCardless specific. It's helped me identify a problem with non-renewing memberships.</p>
  <p>It shows how all the tables (<em>yes, line items are vital to membership</em>) that are related to a recurring membership, and you can check that they're wired up correctly.</p>
  <h2>Example</h2>
  <p>Here is a wrong un':</p>
  <p><a href="https://i.sstatic.net/e4JAd.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/e4JAd.png" alt="screenshot" /></a></p>
  <p>We can see here that most of the contributions' line items relate to the contribution table, not to the membership table. This means they're not right.</p>
  <p>After some fixing of the SQL data, it looks like this:</p>
  <p><a href="https://i.sstatic.net/cGJGs.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/cGJGs.png" alt="screenshot" /></a></p>
  <p>Note how now the line items all know they're for memberships, and have a direct yellow arrow to the membership. This is now correct.</p>
  <p>The tool is very useful for (a) documenting before and after fixes with the table IDs, and (b) learning how everything joins up.</p>
  <p>On a job I had, I used SQL like the following to fix one category of tangles, but I only include this as an example: <strong>Don't run this SQL on your data!</strong> It contains hard coded IDs that won't be right for your use case. Here I correct the line items that belong to membership contributions, but are themselves linked directly to <code>civicrm_contribution</code> instead of <code>civicrm_membership</code>. I have to change the table, the <code>entity_id</code> and the <code>price_field_value_id</code> and <code>price_field_id</code> - the price field ones must be for the membership type in question.</p>
  <p>Without those all correct, membership renewal won't work.</p>
  <pre><code>
  UPDATE civicrm_contribution cn                                                                                              
     INNER JOIN civicrm_contribution_recur cr ON cn.contribution_recur_id = cr.id                                            
     INNER JOIN civicrm_membership m ON m.contribution_recur_id = cr.id                                                      
     INNER JOIN civicrm_line_item li ON li.contribution_id = cn.id                                                           
  SET li.entity_table = 'civicrm_membership',                                                                                 
  li.entity_id = m.id,                                                                                                        
  li.price_field_value_id = CASE m.membership_type_id WHEN 1 THEN 2 WHEN 2 THEN 17 WHEN 3 ThEN 8 WHEN 4 THEN 9 else null END, 
  li.price_field_id = CASE m.membership_type_id WHEN 1 THEN 2 WHEN 2 THEN 10 WHEN 3 ThEN 5 WHEN 4 THEN 5 else null END,       
  li.membership_num_terms = 1                                                                                                    WHERE li.entity_table = 'civicrm_contribution'                                                                         
  ;
  </code></pre>
  <p>A final tip, one that's caught me out before is: A membership has a field that points to the (singular) <em>current</em> ContributionRecur record. Over time, it's possible that multiple recurs have paid for a membership, and this can lead to trouble when the membership points to the completed recur, not the current recur.</p>
  <p>I couldn't fix everything by SQL, as I recall, I think I ended up writing some PHP scripts to do the more tricky things.</p>
  <p>Backing up all the tables you're going to touch before and after each programmatic change is highly recommended.</p>
  <h2>Edit: what's the correct wiring up for a membership?</h2>
  <p>I think this:</p>
  <ul>
  <li>Contributions (CN) that are part of a recurring contribution (CR) need to point to them: <code>cn.contribution_recur_id = cr.id</code></li>
  <li>A Membership (M) should be linked to a recurring contribution (CR) : <code>m.contribution_recur_id = cr.id</code>
  <ul>
  <li>Note: if a membership has been paid my more than one CR, e.g. because one CR completed/failed/finished and was replaced by a new one, then the old CR will not be referred to by the membership as it will be pointing at the new CR (via the <code>M.contribution_recur_id</code> field)</li>
  </ul>
  </li>
  <li>A Contribution that pays for a membership should have
  <ul>
  <li>a Line Item (LI): <code>li.entity_id = m.id with li.entity_table = 'civicrm_membership'</code>
  <ul>
  <li>The Line Item must point to a Price field <code>price_field_id</code> and value <code>price_field_value_id</code> <em>for the correct Membership type</em></li>
  </ul>
  </li>
  <li>an entry in the memberships-payments link table (this is slated for deprecation but is still used as of 5.49 at least). This is shown by the dashed line in the 2nd diagram.</li>
  </ul>
  </li>
  <li>M, CR, CN records all point to a contact via <code>contact_id</code>, obvs.</li>
  </ul>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-11-22T10:52:18.140Z
Answers.Text: you said "you can check that they're wired up correctly". Do you know is there an official guide anywhere on what constitutes correctly?
Answers.UserDisplayName: michaelmcandrew
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-11-22T10:53:02.927Z
Answers.Text: ... otherwise I suppose I can just look at some correct ones.
Answers.UserDisplayName: michaelmcandrew
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-11-22T10:54:09.987Z
Answers.Text: The graph looks amazing by the way!!
Answers.UserDisplayName: michaelmcandrew
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-11-22T13:04:25.303Z
Answers.Text: Have edited answer. The graph is generated by php creating mermaid code which is rendered by javascript. Incidentally, you can copy paste the mermaid code into gitlab which also supports mermaid via ```mermaid markdown codeblocks. The mermaid code includes a lot of styling to help make it cleaerer.
Answers.UserDisplayName: artfulrobot
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-11-22T18:36:48.353Z
Answers.Text: wow - is all i want to say - but StackExchange requires 12 characters minimum - so ... wow again
Answers.UserDisplayName: petednz - fuzion
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-11-24T09:26:58.740Z
Answers.Text: Thanks so much, artfulrobot. This is so useful!
Answers.UserDisplayName: michaelmcandrew
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-11-25T11:55:46.167Z
Answers.Text: @artfulrobot you said that `civicrm_membership_payment` "is slated for deprecation but is still used as of 5.49 at least". Is there any official word on this? see https://chat.civicrm.org/civicrm/pl/49pzu1zi3jnxpypn413oi5ny7c
Answers.UserDisplayName: michaelmcandrew
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-11-25T13:36:48.250Z
Answers.Text: One 'final' thing (and I am not asking this directly at you @artfulrobot, but) the answer above doesn't mention template contributions. Anyone know anything about the thinking behind these and how they fit into the picture?
Answers.UserDisplayName: michaelmcandrew
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-11-25T13:52:56.730Z
Answers.Text: OK - it wasn't final :) I have also noticed that there is a column `civicrm_line_item.membership_num_terms`. In the good membership record, this has been set to 1 in the most recent renewal. In previous renewals, it is empty.
Answers.UserDisplayName: michaelmcandrew
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-11-21T10:05:41.310Z
Answers.LastActivityDate: 2022-11-29T12:57:23.543Z
Answers.LastEditDate: 2022-11-29T12:57:23.543Z
Answers.UserDisplayName: artfulrobot
Body: <p>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).</p>
  <p>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.</p>
  <p>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.</p>
  <p>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.</p>
  <p>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.</p>
  <p>So my first question is <strong>What tables should we be looking in?</strong></p>
  <ul>
  <li><code>civicrm_membership</code></li>
  <li><code>civicrm_membership_payment</code></li>
  <li><code>civicrm_contribution</code></li>
  <li><code>civicrm_contribution_recur</code></li>
  </ul>
  <p>Seem like obvious candidates. So does <code>civicrm_line_item</code> though I am less clear about what we should be looking at in that table and how it connects to memberships and contributions. <strong>Are there relevant columns in <code>civicrm_line_item</code>?</strong></p>
  <p><strong>Are there other tables I should be looking in?</strong></p>
  <p>The following tables seem unlikely to be relevant but I add them here just in case and am happy to be corrected.</p>
  <ul>
  <li><code>civicrm_financial_item</code></li>
  <li><code>civicrm_financial_trxn</code></li>
  <li><code>civicrm_entity_financial_trxn</code></li>
  </ul>
  <p>Once we know all of the tables,</p>
  <p><strong>Are there specific ways I should be joining tables when doing the comparison?</strong> e.g. the first or last in a set of contributions related to a contribution recur?</p>
  <p>What about template contributions? I am not sure if they are relevant in this case as there are no contributions with <code>is_template = 1</code> but feel free to address this point in your answer :)</p>
  <p><strong>Any thoughts on the approach we are taking, and your experiences with / suggestions for alternative approaches are very welcome</strong>.</p>
  <p>Finally, and in case it is relevant, the payment processor we are dealing with here is GoCardless.</p>
  <p>Thanks all!</p>
  
Tags: <civimember><recurring-contributions><auto-renew><renewals>
Title: Cleaning up legacy contributions so that renewals happen as expected
ThreadText: # Cleaning up legacy contributions so that renewals happen as expected
  
  (Asked by: username_0 on 2022-11-18)
  
  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!
  
  **Tags:** civimember, recurring-contributions, auto-renew, renewals
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2022-11-21. Score: 4
  
  This is not a fun task, but at least I made a tool to make it pretty.
  If you install Uh-Oh troubleshooting extension it includes a diagnosis page at Support » Map Contributions which lets you specify contacts, contributions, recurs, and memberships, and it will graph the links as shown here. It's not GoCardless specific. It's helped me identify a problem with non-renewing memberships.
  It shows how all the tables (yes, line items are vital to membership) that are related to a recurring membership, and you can check that they're wired up correctly.
  Example
  Here is a wrong un':
  
  We can see here that most of the contributions' line items relate to the contribution table, not to the membership table. This means they're not right.
  After some fixing of the SQL data, it looks like this:
  
  Note how now the line items all know they're for memberships, and have a direct yellow arrow to the membership. This is now correct.
  The tool is very useful for (a) documenting before and after fixes with the table IDs, and (b) learning how everything joins up.
  On a job I had, I used SQL like the following to fix one category of tangles, but I only include this as an example: Don't run this SQL on your data! It contains hard coded IDs that won't be right for your use case. Here I correct the line items that belong to membership contributions, but are themselves linked directly to civicrm_contribution instead of civicrm_membership. I have to change the table, the entity_id and the price_field_value_id and price_field_id - the price field ones must be for the membership type in question.
  Without those all correct, membership renewal won't work.
  
  UPDATE civicrm_contribution cn                                                                                              
     INNER JOIN civicrm_contribution_recur cr ON cn.contribution_recur_id = cr.id                                            
     INNER JOIN civicrm_membership m ON m.contribution_recur_id = cr.id                                                      
     INNER JOIN civicrm_line_item li ON li.contribution_id = cn.id                                                           
  SET li.entity_table = 'civicrm_membership',                                                                                 
  li.entity_id = m.id,                                                                                                        
  li.price_field_value_id = CASE m.membership_type_id WHEN 1 THEN 2 WHEN 2 THEN 17 WHEN 3 ThEN 8 WHEN 4 THEN 9 else null END, 
  li.price_field_id = CASE m.membership_type_id WHEN 1 THEN 2 WHEN 2 THEN 10 WHEN 3 ThEN 5 WHEN 4 THEN 5 else null END,       
  li.membership_num_terms = 1                                                                                                    WHERE li.entity_table = 'civicrm_contribution'                                                                         
  ;
  
  A final tip, one that's caught me out before is: A membership has a field that points to the (singular) current ContributionRecur record. Over time, it's possible that multiple recurs have paid for a membership, and this can lead to trouble when the membership points to the completed recur, not the current recur.
  I couldn't fix everything by SQL, as I recall, I think I ended up writing some PHP scripts to do the more tricky things.
  Backing up all the tables you're going to touch before and after each programmatic change is highly recommended.
  Edit: what's the correct wiring up for a membership?
  I think this:
  
  Contributions (CN) that are part of a recurring contribution (CR) need to point to them: cn.contribution_recur_id = cr.id
  A Membership (M) should be linked to a recurring contribution (CR) : m.contribution_recur_id = cr.id
  
  Note: if a membership has been paid my more than one CR, e.g. because one CR completed/failed/finished and was replaced by a new one, then the old CR will not be referred to by the membership as it will be pointing at the new CR (via the M.contribution_recur_id field)
  
  
  A Contribution that pays for a membership should have
  
  a Line Item (LI): li.entity_id = m.id with li.entity_table = 'civicrm_membership'
  
  The Line Item must point to a Price field price_field_id and value price_field_value_id for the correct Membership type
  
  
  an entry in the memberships-payments link table (this is slated for deprecation but is still used as of 5.49 at least). This is shown by the dashed line in the 2nd diagram.
  
  
  M, CR, CN records all point to a contact via contact_id, obvs.
  
  > **Comments:**
  > **username_0**: you said "you can check that they're wired up correctly". Do you know is there an official guide anywhere on what constitutes correctly?  
  > **username_0**: ... otherwise I suppose I can just look at some correct ones.  
  > **username_0**: The graph looks amazing by the way!!  
  > **username_1**: Have edited answer. The graph is generated by php creating mermaid code which is rendered by javascript. Incidentally, you can copy paste the mermaid code into gitlab which also supports mermaid via ```mermaid markdown codeblocks. The mermaid code includes a lot of styling to help make it cleaerer.  
  > **username_2**: wow - is all i want to say - but StackExchange requires 12 characters minimum - so ... wow again  
  > **username_0**: Thanks so much, username_1. This is so useful!  
  > **username_0**: @username_1 you said that `civicrm_membership_payment` "is slated for deprecation but is still used as of 5.49 at least". Is there any official word on this? see https://chat.civicrm.org/civicrm/pl/49pzu1zi3jnxpypn413oi5ny7c  
  > **username_0**: One 'final' thing (and I am not asking this directly at you @username_1, but) the answer above doesn't mention template contributions. Anyone know anything about the thinking behind these and how they fit into the picture?  
  > **username_0**: OK - it wasn't final :) I have also noticed that there is a column `civicrm_line_item.membership_num_terms`. In the good membership record, this has been set to 1 in the most recent renewal. In previous renewals, it is empty.  
  
  ---
  
  