« Last post by joemcl on Today at 03:43:31 pm »
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.