CiviCRM Community Forums (archive)

I am struggling with finding a method where I can regularly export new membership sign ups and renewals in an automated fashion via a cron job.

My goal is to create two CSV files and deliver it to a third party mail processing company that generates membership cards and "welcome!" or "Thank you for renewing" letters to membership.  Via the interface I've found it nearly impossible because a renewal remains a member with a "Current" status.  The only easily identifyable trick I've found is to look for renewal activities in a certain date range.   New members are relatively easy because they are "New" -- but the problem is I believe if I convert these to a Current in the process of mailing them they'd likely convert back to a new until 3 months once the status job runs.

I'm also looking for a very simplistic way to export all current, expired, and grace members email addresses to change the members status in a forum system where I would match on the email address.

My initial thoughts were to use direct SQL queries in an external script to manipulate members.  But now i'm wondering if this is something more cleaning done via the API scripts.

Am I really the only person that needs to automate something like this?

Regarding export of emails for all current, expired, grace - obviously you can do that via Find Memberships => Export in the UI. And you should be able to use the Membership api (get ...) if you want to do it programmatically.

This I understand.  What I wasn't able to find was a way to search for a contact based on the thank you status of a contribution.  Is that available in the UI?

For the welcome / thank-you's - seems like you're looking for a flag to record that letter was sent. You could use a custom field on the membership (w/ the date). OR you could go off the contribution records (assuming these are paid memberships) - in which case you'd have a new contribution record for each signup and renewal to key off and the contribution record has a built in field for Thank-you Sent date.

I didn't think of the custom field.  This may have been the easy solution staring me in the face the whole time and really is the problem I'm trying to solve.  Lets say the entire membership base is considered "current" and then a two week period goes by.  The complication I have is finding a field to use to identify those who joined or renewed in that period of time.  I think this might do it if I can find a way to blank it out or reset it at a join or renewal.

I came back to this and finally figured out how to query for membership contributions and then gather the information I need.

However I either encountered a bug, a limitation of the API, or I'm just doing this wrong:

$params = array('version' => 3, 'thankyou_date' => null, 'contribution_status_id' => 1, 'contribution_type_id' => 2,);
$result = civicrm_api( 'contribution','get',$params );
How can I query for thankyou_date being undefined?  No matter what I try I get back rows with thankyou_date populated.   I've tried 0, '', null, and I can't find any v3 examples where a get is looking for a field to be undefined.   What I'm basically doing is exporting all contributions where the type is a completed membership contribution.  Where the thankyou is blank I gather the mailing address of the contact and insert the current date into thankyou_date for that specific contribution.

This will work perfect if I can limit the results of the 'get'.  I can do it in php but I'd rather the API return just those rows I need.   That should keep the result set really small.

Is there a way to only return results where the date is blank/null ?