Dynamic group for upcoming renewals

[Chris Burgess]

CiviMember makes it possible to automate membership renewal reminders via editing each membership type, but I also wanted to be able to search for members who were up for renewal "this month". I could instruct the field office staff in how to search for people with a membership ending this month in their region, or I could create a dynamic group for "Upcoming Renewal" and use that to search.

Here's how I implemented the latter:

1. Search for members whose membership ends within a specific date range. Use the advanced search screen. Any date range is fine, provided that there is at least one result; I used the current month.

2. Save the search as a group, and call it "Upcoming renewal". Or call it whatever you want

3. Via SQL, examine your civicrm_saved_searches table. Look at the most recently created search; it will have a where_clause value of something like,

Code: [Select]

( civicrm_membership.end_date >= '20071101' AND civicrm_membership.end_date <= '20071130' )
4. Compose some replacement SQL which will use CURDATE() to make this query dynamic. I used,

Code: [Select]

( civicrm_membership.end_date >= DATE_FORMAT(CURDATE(), '%Y-%m-01') AND civicrm_membership.end_date <= LAST_DAY(CURDATE()) ) (nb. this SQL uses MySQL extensions - you may want to use different date/time functions for different results)

5. Update the saved_search via SQL. Mine has ID=13, so I used

Code: [Select]

update civicrm_saved_search set where_clause = "( civicrm_membership.end_date >= DATE_FORMAT(CURDATE(), '%Y-%m-01') AND civicrm_membership.end_date <= LAST_DAY(CURDATE()) )" where id = 13 ;
6. Test it out ...

Hope this is useful to someone! It would be cool to be able to use the advanced search interface to create queries with SQL like this, but it's not hard to tweak the created queries to do this either.

Would love to hear about improvements (eg, this query may return deceased members whose families don't want to be called about renewal ..)

PS. In "Renewal reminders" on demo.civicrm.org, the "more info" link points to a deleted page.

If you would like Membership Renewal Reminder emails sent to members automatically, you need to create a reminder message template and you need to configure and periodically run a "cron" job on your server (more info...).

[Dave Greenberg]

Thanks for posting this - it's a cool solution!

For 2.0 we're implementing a framework for custom searches with developer defined input fields, queries and listing columns. You can follow progress on this feature here:
http://issues.civicrm.org/jira/browse/CRM-2360

Also thx for tip on the bad "more info" link - we'll fix this in the next 1.9 rev.

[petednz]

Does this relate to the Sample Report in CiviReport which says
Expiring Memberships     Member/MembershipExpiration.rptdesign     Lists contacts whose memberships are expiring within a given date range. Includes links from the report rows to the CiviCRM contact summary screen for each member.
http://wiki.civicrm.org/confluence/display/CRMDOC/Sample+Reports
Hi Chris!

[Dave Greenberg]

Peter - Chris' "recipe" is not related to the BIRT / CiviReport tool - but rather a way to tweak the built-in smart group functionality to use dynamic search criteria (for the date range).