CiviCRM Community Forums (archive)

This might be better in the reporting forum but I'll try here first to ask about the setup aspect. I have a question from an end user: "The accountant needs to know for which membership year the contribution was made - currently, when membership is renewed, it appends to existing membership end date. Is there a separate record of each membership in the DB so it can be displayed on the reports?"

In the data this client has there is just one membership record in the civicrm_membership table for a contact who has renewed a couple of times so they are using the "renewal extends existing membership" setup.  In some cases there are related records in the civicrm_membership_payment table that link to the civicrm_contribution table and there are some changes shown in the civicrm_membership_log table.  Whether these are there depends on how the data was entered - online or in the back end and how careful the back end data entry was done.

I was hoping I could match by contributions by date and then extract the year change from the membership_log but found that the receive_date on the contribution table doesn't necessarily match with a date in the membership_log.

I realize one option is to not use membership renewals at all and create a new membership record each year. Short of doing that can anyone suggest the best *setup* for getting this collected during renewals?

• Create a new contribution page each year and use the contribution page name to determine the year? This wouldn't work easily with rolling memberships
• Or add a hidden custom field to the contribution that grabs the year?

Do you think this area might benefit from a data structure change?

• Perhaps the civicrm_membership_log table could have a column for change_log_type that could include statuses like new and renewal?
• Perhaps the civicrm_membership_log table could have a column for membership_payment_id that links to either the contribution directly or the civicrm_membership_payment table? This would show in the log that the status changed due to a payment.