CiviCRM Community Forums (archive)

We have had several customers ask us how to find members who are expired. This sounds easy but in fact if a contact has an expired membership AND a current membership then these don't qualify as expired.

We do have a convoluted method of creating a smart group of current members & one of expired members & a third smart group including expired but not current. Not surprisingly however, they consider this 'a bit hard' when they just want to know about 'what values does their latest membership have'.

We can see a couple of paths

1) enhance core search with a checkbox that allows them to say 'only query latest membership based on end date' - behind the scenes we would create a temp table much like happens in relationships. This approach could include unit tests that would be run as part of jenkins so would be most robust - but would it be confusing?

2) add a patch to the summary fields extension to cache the latest start &  end dates and status fields into a contact custom field which would then be searchable. The customer was keen on this approach although Jamie would need convincing

3) create a separate extension to cache the fields. We wouldn't need to convince Jamie of this but without the benefit of tying in with an existing collaboration we would be unlikely to do this in a way that is portable between sites

4) create an extension that adds the mentioned checkbox to the advanced search per #1. I suspect this would not be a terribly maintainable approach however.

Jamie, you're  right, creating two Smart Groups using Search Builder, one of Membership Status = Current and another of Membership Status = Expired, then using the custom search Include/Exclude Contacts in a Group/Tag and creating a third Smart Group including Expired and excluding Current, creates a clean list of just people with only Expired status. I think I had figured that out when Eileen suggested it.


But I think that at the time (and still) my goal was to have a report and/or custom field set to show not just that list of names, but the join date/member since date, start date and end date of that expired membership (because for many people, there may be previous expired memberships) , the membership type, and the source - more info to provide to folks doing membership renewal calls/asks. I think I'm the customer Eileen refers to below when she wrote "2) add a patch to the summary fields extension to cache the latest start & end dates and status fields into a contact custom field which would then be searchable. The customer was keen on this approach although Jamie would need convincing".

Eileen suggested a SQL query that I've run in the back end using PHMyAdmin which seems to do the trick to show most recent membership status  - current, expired or terminated - for anyone who is or has been a member -
SELECT * FROM civicrm_membership m INNER JOIN (
SELECT * FROM
(
SELECT * FROM civicrm_membership ORDER BY start_date DESC
) as i
GROUP BY contact_id
)
as latest ON m.id = latest.id

So my thought is, if the fields from that query result could be added to your Summary Fields extension, that would be really useful, for my organization and others.

Jamie, thanks, and valid concerns. A custom search would definitely be useful. Does adding the data to appear as a report, for example, in the Civi interface require necessarily a new table, though, or couldn't the results just be shown as a query result in a Civi report? Or maybe simpler, just a report based on the custom search results?

So, to throw it in there - it is possible to add things to advanced search via an extension - that don't necessarily show up as their own fields.

My inclination IS to go the triggered custom fields way - Joe is wanting to access this data by searching but the more common request we get is to have this information available for CiviMail tokens (latest current membership type, latest current membership expiry date) - this seems common enough that I feel there should be a less ad hoc way of delivering it - & one that also addresses Joe's angle

I think the need for tokens really makes it a summary fields feature.

As for Mark's concern... one option would be to specify which membership types should be checked. It doesn't actually solve Mark's issue (because it would still only show one membership type) but at least it would add some logic.

I don't know how to have a variable number of fields depending on which membership type you want.