Finding one membership among many

[Eileen]

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]

Just catching up after Joe pointed out this thread...

I wonder why the include/exclude search is not workable - especially if you made it a smart group it would be quite easy to retrieve the list.

jamie

[joemcl]

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]

Initially I was quite enthusiastic about the prospects of adding this kind of information to summary fields, but I have since grown a little bit more hesitant for the following reasons:

 * It adds a new table to the data being generated. Adding  new data that can be generated using the contribution or event tables is not so difficult - really it just means adding a few lines to one file. But adding a new table makes things more complicated (the membership data currently used is actually coming from the contribution table, not the membership table).
 * The summary fields were originally designed to show data that is otherwise hard to see when looking at a record (such as total contributions last year compared with this year). This need seems a bit more suited for a custom search.

I'm not fully opposed... just trying to think through the best options.

[joemcl]

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?

[jamie]

There is no need to add a new table to the schema if we add this field to summary fields. However, additional code will need to be added to handle referencing the membership table (since no other summary fields need to reference the membership table).

With a custom search - you could create a smart group with the results. Then, any report that allows you to build the results based on membership in a group could be used.

[Eileen]

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

[ptpmark]

This conversation so far hasn't addressed an issue that we see on a regular basis: an organization that has multiple membership types and the contact may have more than one valid membership.  If this goes into summary fields extension, we'd have to allow for tracking ALL the membership types that are defined.
that seems sticky to me.

[jamie]

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.

[guruevi]

A subquery would probably be the fastest option.

select DISTINCT t1.id as cid
from Contact t1, Memberships t2
where t1.id = t2.contact_id AND t1.id NOT IN (SELECT contact_id FROM Memberships WHERE end_date > NOW()).

You can remove the distinct if you'd like to react to each membership instead of contact.
Basically "unselect" everyone that has a current membership. I wish there were an SQL-like query builder in CiviCRM...

Not sure whether a custom report can feed into a mailing list?