Author Topic: Can Membership Type be included in Exports? (Read 2641 times)

LindseyM · November 18, 2011, 09:00:58 am

We frequently pull up individuals via searches/groups and export their info. We need to see their Membership Type in these exports but when I select fields for export I can't see how to include Membership Type as that field doesn't show in the dropdown. How can I achieve this?

Kurund Jalmi · November 23, 2011, 01:46:05 am

Are you using Export Members? You will have to first Find Members and the use task action "Export Members"

HTh
Kurund

LindseyM · November 23, 2011, 10:25:34 am

Hi Kurund
The issue is that I rarely want to take the Find members route. I frequently want to identify a particular subset of people for all manner of reasons eg by group, postcode, event involvement or whatever... but I also want to see their membership type for reference when I look at the people the search has given me. At present, following a search, Membership fields do not show up as an option when I select fields to export. They only show up when taking the Find Members route.

Membership Type is an important indicator for us ie what membership type are the people who attended our newcomers' event in January? Have some become full members while others remain undecided? How connected are the people linked to our toddler group?

Is it possible for Membership to be added to the type of fields allowed in a main export (as Membership type is surely a subset of Individual fields, rather than a different sort of field like Household or Organisation). If this is not currently possible can i perhaps mirror the Membership Type in an individual contact's field so that it can be included in exports? i realise that with some behind-the-scenes work Membership Type can show in the search results page as a column, but it's the export that will be most useful.

Many thanks.

Donald Lobo · November 23, 2011, 02:14:28 pm

exposing membership type (or other membership fields) is currently not availaible with civi 4.1 or below. Some of the main stumbling blocks is:

* the query is already quite complex. adding more info from other tables will make it even more complex
* each contact could have multiple memberships and hence multiple membership types. U'll need to export all of them

For now the easiest solution might be to implement the export hook and add the membership type just before you output the csv

http://wiki.civicrm.org/confluence/display/CRMDOC40/CiviCRM+hook+specification#CiviCRMhookspecification-hookcivicrmexport

if you do so, please publish your changes so others can use it as a sample for their modifications

lobo

LindseyM · November 23, 2011, 03:05:07 pm

Thanks lobo. We're also looking at the option of using a hook to mirror the Membership Type information in a 'view only' custom field in an individual's contact record so that the info can be included in an export that way.

Galieth · November 09, 2012, 02:39:27 pm

For anyone who runs across this later here is a hook that I wrote that will do something close to what the original poster wanted. This is written in a joomla hook, If you are on something else you will have to adjust it for that.

This code grabs the most recent user membership (that is not a test membership) when exporting from a find contacts search and you are exporting the internal id.

Code: [Select]

	function civicrm_export( $exportTempTable, $headerRows, $sqlColumns, $exportMode ) {
		switch($exportMode)
		{
			case 1:
				//Find Contact Export
				//Check if internal id is one of the fields if not break;
				if(!isset($sqlColumns['civicrm_primary_id']))
				{
					break;
				}

				$query = "SELECT * FROM ( SELECT e.*, cm.end_date, mt.name AS membershiptype, ms.name AS membershipstatus, cm.contact_id AS memberid FROM $exportTempTable AS e LEFT JOIN civicrm_membership AS cm ON e.civicrm_primary_id = cm.contact_id LEFT JOIN civicrm_membership_type AS mt ON cm.membership_type_id = mt.id LEFT JOIN civicrm_membership_status AS ms ON cm.status_id = ms.id WHERE (cm.is_test = 0 OR cm.is_test IS NULL) ORDER BY end_date DESC) AS t GROUP BY memberid";

				require_once 'CRM/Core/Report/Excel.php';

				$dao = CRM_Core_DAO::executeQuery( $query );

				//add the new columns into the sqlColumns and into the header rows
				$headerRows[] = 'Expire Date';
				$headerRows[] = 'Membership Type';
				$headerRows[] = 'Membership Status';
				$sqlColumns['end_date'] = '';
				$sqlColumns['membershiptype'] = '';
				$sqlColumns['membershipstatus'] = '';
 
		        if ( $dao->N <= 0 ) {
		            break;
		        }
			$componentDetails = array( );
		        while ( $dao->fetch( ) ) 
		        {
		            $row = array( );
		            foreach ( $sqlColumns as $column => $value ) 
		            {
		                $row[$column] = $dao->$column;
		            }
		            $componentDetails[] = $row;
		        }
		        CRM_Core_Report_Excel::writeCSVFile( "Export_Records", $headerRows, $componentDetails );
 
		        break;
			case 2:
				//Contributions Export
				break;
			case 3:
				//membership export
				break;
			case 4:
				//Event Participants export
				break;
			default:
				//all other exports
				break;
		}
		CRM_Utils_System::civiExit( );
	}//END civicrm_export

Hope this helps someone out.

LindseyM · November 10, 2012, 06:59:13 am

Thanks Galieth. We eventually decided not to use Civimember as we don't have paid memberships so other solutions within Civi work better for us... but I'm sure some others will find this helpful as it's a useful addition.

Jason W · November 13, 2012, 10:04:55 am

Hello Lindsey,

Have you tried using the advanced search? In the top right hand corner you can set it to display results as memberships. run your search, select all contacts, and select export from the actions menu. from the export wizard, select choose which fields to export, and continue. On the next page, on select record type, select membership, in the dropdown that appears, you should be able to select Membership Type.

Cheers!
Jason

flug · December 14, 2012, 08:17:04 am

FWIW our solution is to create some custom fields to hold info about the contact's membership status--this includes membership type, membership since date, membership start date, membership expiration date, the membership status message that we want to send them when we're contacting them regarding their membership via mail or email, and a couple other relevant things.

It also generates a membership status for EVERYONE in our contact database, since our primary way of interacting with people is based on their status as members. Basically, everyone is either a current member, expired member, or a prospective member. So for non-members we generate a status message like "You are not an XXX member, please join today!"

Then we set up a cron job to run through all that contact's membership and figure out those values based on the last/best/most valid membership that they have and save that info to the custom data fields for the contact. Right now the job runs/updates once a day. We're also planning to implement hooks so that whenever a contact or membership is updated the associated custom fields are immediately updated to reflect the new membership status (right now it can be up to 24 hours before the fields are updated which can cause problems in certain situations).

That code is similar to the code posted here, except modified to update that info to the custom fields for the contact.

I could post the code if it would help anyone.

Our use case might be different from some (though probably similar to many) in that we do not want to give people a complicated message about their membership status where we list 5 different memberships for them, some expired, some cancelled, some valid, etc etc etc. That is too complicated and confusing. You have to spend a lot of time and energy explaining to people why they have all these different memberships and membership statuses and what they all mean.

We just want ONE SINGLE membership status message we can give people--because it is a signal to them whether their membership is current and doesn't need renewal, or it is expired and they need to stop by the website and renew.

We also need to be able to export this current membership status info and status message simply and easily to send them by mail, email, or any other way. So the custom data approach works well from all those perspectives, even though it is a bit kludgy and brittle in implementation.

Eileen · December 14, 2012, 11:28:20 am

Note that Lindsay's question was 4.1. There was a fix in 4.2 http://issues.civicrm.org/jira/browse/CRM-10617 for her original request

CiviCRM Community Forums (archive)

News:

Author Topic: Can Membership Type be included in Exports? (Read 2641 times)

LindseyM

Can Membership Type be included in Exports?

Kurund Jalmi

Re: Can Membership Type be included in Exports?

LindseyM

Re: Can Membership Type be included in Exports?

Donald Lobo

Re: Can Membership Type be included in Exports?

LindseyM

Re: Can Membership Type be included in Exports?

Galieth

Re: Can Membership Type be included in Exports?

LindseyM

Re: Can Membership Type be included in Exports?

Jason W

Re: Can Membership Type be included in Exports?

flug

Re: Can Membership Type be included in Exports?

Eileen

Re: Can Membership Type be included in Exports?