Author Topic: How to search for members without current membership? (Read 1037 times)

Jens-Erik · June 27, 2013, 06:39:47 am

I have imported all membership fee payments of the last years from our old Access database via Excel and CSV into CiviCRM. Each imported payment results in a separate “membership” (in CiviCRM terms), so that most members have multiple “memberships”: some expired, maybe one in the grace period and one current.

This causes problems when we want to find members who haven’t paid their membership fee: When you search for memberships with membership status ”Grace” or “Expired” in “Find Memberships”, you get hundreds of hits of memberships that are expired or in the grace period, but most of the corresponding contacts (members) have a current membership in addition.

Searching for memberships with an end date this quarter or this year helps in checking members who are due to pay now, and they get scheduled reminders, but we need a way to find also those who haven’t paid, so we can ask them personally if they want to remain members or leave the association. All these members are in one CiviCRM group and have the tag “member” in addition as opposed to other contacts who are in our database, but don’t want to be, are not yet or not any more members.

So is there any way to find contacts who are in a specific CiviCRM group (or have a specific tag) who don’t have a current membership?

One could assume that Search -> Search Builder would do the trick with a search like:

Include contacts where
Contacts – Groups – = – group name
Membership – Membership Status – = – Grace

Also include contacts where
Contacts – Groups – = – group name
Membership – Membership Status – = – Expired

But no matches are found, not even a search for any membership status without the group condition yields any hit.

petednz · June 27, 2013, 11:58:18 am

and Adv Search doesn't work ie add the Group and add Membership = Expired?

Jens-Erik · June 27, 2013, 02:08:57 pm

No, that doesn’t work either: An advanced search with group and membership = grace or expired I get hits, and at least I have the restriction of the group, so that I don’t get hits of former members. But still I get members who have an expired/grace membership term in the passed and a current membership in addition (still 346 hits instead of probably very few). What would help is a NOT operator: search for contacts who are in a specific group AND have NOT a membership with status current. But I don’t see any way to do such a search. Is it possible somehow?

petednz · June 27, 2013, 02:17:57 pm

how about create a group of 'all current members' - then use the custom search to do an include/exclude eg

include all people in Group 1 (expired members) and Tag X
exclude all people in Group 2 (current members)

would that get you there?

Tony Horrocks · June 28, 2013, 02:22:07 am

You could try doing this using a Drupal View. Seems to work.

Jens-Erik · June 29, 2013, 12:31:49 pm

Pete, thanks, that works. Actually the group of members is synchronized with the membership status, so I’ve created one smart group consisting of all the contact subtype for members with grace or expired memberships that I include in the final include/exclude search, another smart group includes all contacts with new or current memberships. As you proposed I’ve then created an Include/Exclude Search including the first and excluding the second smart group as well as those with the tags for former and other non-members and finally I saved this search as a smart group of members who are due to pay their membership fee.

Tony, thank you, too. I’m going to try a view, too.

So finally it works somehow, but still I think this is a bit tricky for a not so exotic scenario: importing — in many cases — multiple membership payments per contact, which results in multiple membership records instead of one renewed membership per person, which would reflect reality better anyway and would make the search for really expired memberships easier. For all memberships that are being renewed in CiviCRM this not a problem, since renewing a membership doesn’t create a new membership record, just an entry of the renewal within the same membership. I wish this would be the case when you import the payments.

So maybe I’m going to write a little script that joins the multiple payment records of one contact into one record in the csv file to be imported., stating start day of the first and end date of the last payment per contact and skipping all the renewals in between.

petednz · June 29, 2013, 02:23:18 pm

That could be good but we may have just done something similar and I will try to remember to ask eileen to comment on here

Eileen · June 29, 2013, 02:39:07 pm

Here's a sanitised version of what we did

https://gist.github.com/eileenmcnaughton/5892775

However this is an class that extends migrate module / civimigrate rather than working on a csv. I doubt it is terribly helpful in that circumstance

Jens-Erik · July 01, 2013, 05:54:17 am

Thanks Eileen, I don’t care where I combine the membership payments, in the csv or in CiviCRM. I only had the idea of manipulating the csv because I don’t know the CiviCRM API yet So your code could be useful for us. Can you tell me how you use this? Do you replace a file in the CiviCRM code or do you use it as an extension? Where should I place it exactly?

How is it triggered? During import of the membership payments?

Eileen · July 01, 2013, 01:34:40 pm

I am using the civimigrate + migrate module to do the imports -http://fuzion.co.nz/content/migrate-2-civicrm-api-3-civimigrate

That approach might be a bit heavy for what you want - but you could try to write a hook http://wiki.civicrm.org/confluence/display/CRMDOC43/hook_civicrm_pre

CiviCRM Community Forums (archive)

News:

Author Topic: How to search for members without current membership? (Read 1037 times)

Jens-Erik

How to search for members without current membership?

petednz

Re: How to search for members without current membership?

Jens-Erik

Re: How to search for members without current membership?

petednz

Re: How to search for members without current membership?

Tony Horrocks

Re: How to search for members without current membership?

Jens-Erik

Re: How to search for members without current membership?

petednz

Re: How to search for members without current membership?

Eileen

Re: How to search for members without current membership?

Jens-Erik

Re: How to search for members without current membership?

Eileen

Re: How to search for members without current membership?