Alpha Video Training
Results 1 to 9 of 9

Thread: Reporting on Expired Members

  1. #1
    Member CubDriver's Avatar
    Real Name
    Steve Johnson
    Join Date
    May 2003
    Location
    Kansas City, MO
    Posts
    21

    Default Reporting on Expired Members

    Hi folks! I'm fairly new to Alpha5 (acutally, I used it a gillion years ago for a bit when I was a big dbase/fox programmer) and it really seems to be the cat's meow. Of course, I'm used to doing things the hard way, so sometimes I can't see the simple solution. I've read and reread a lot of posts and the help data, but can't seem to figger this one out:

    I have a member database (set) for a website I run.

    The Set is:
    Master - (demographic data)
    -> Detail (donation deatil information, date, amount, etc)
    -> Comments (general notes about the member)

    This just works great for entering data and displaying it in forms, etc.

    When someone makes a donation to the site, they are "members" for another year. This donation gets entered in the subform in the detail table. All this is PEACHY.

    Now the crux: I need to create a report of people who have not donated in a year or more (actually, an email, but let's just try the report first). This means that I have to look at all their latest (date) donation detail record (they may have four or five each at this time) and see if it suits the critera or not, if not, then I need to spit them out on the report.

    I have tried a lot of things, including writing an xbasic script that would update a field in the master file with the latest date from the detail file, I could not even figure out how to do that (with a set), but that is a whole 'nuther question.

    Any help or direction would be appreciated! I someday hope to be smart enough to contribute back!

    THANKS!

    sj

    P.S. Referencing other threads or resources that would help with this would be great, I may just be too stupid to find them.

  2. #2
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,311

    Default

    This problem comes up from time to time. You asking how to identify member records when a detail record is missing for the current year. It's sometimes hard to see records when they aren't there :)

    An easy approach to this is to use two steps. In the first you flag or mark the records which have a detail record in the current year. Then all the other (unmarked) member records are the ones which haven't paid.

    If this makes sense you could implement this as follows:

    a) create a new set. Members, linked to Details 1:1 on last matching record. (I'm not quite certain how Alpha determines which Details record is last for any given Member, so check the results carefully).
    b) query the set to show only records with a Details donation date other than the current year
    c) mark all the returned records

    Steps b & c can be combined in a single filtered update operation

    d) Release the query, then run a second one to show only the Member table records that haven't been marked. These are the ones that need to get "we miss you" letters.

    -- tom

    ps. a good first step is to unmark all member records before running the update operation. this assures a clean slate when your processing begins, so to speak.
    Last edited by Tom Cone Jr; 12-04-2005 at 03:43 PM.

  3. #3
    "Certified" Alphaholic
    Real Name
    Bill Belanger
    Join Date
    Sep 2003
    Location
    Caribou Maine
    Posts
    1,053

    Default

    Steve,
    Couldn't you just set a date in the master record that indicates an expiration date for their subscription? When they pay, their expiration date gets updated.
    Then send notices monthly, or whatever, to those with expired subscriptions.

    Just a thought

    Bill Belanger

  4. #4
    Member CubDriver's Avatar
    Real Name
    Steve Johnson
    Join Date
    May 2003
    Location
    Kansas City, MO
    Posts
    21

    Default

    Bill,

    That is kinda where I have ended up. Thanks!

    sj

  5. #5
    "Certified" Alphaholic
    Real Name
    Bill Belanger
    Join Date
    Sep 2003
    Location
    Caribou Maine
    Posts
    1,053

    Default

    Steve,
    By the way, Alpha includes a membership manager template, at least in the V6 and V7 versions.

    Bill Belanger

  6. #6
    Volunteer Moderator Steve Workings's Avatar
    Real Name
    Steve Workings
    Join Date
    Apr 2000
    Location
    The Dreaded Chair
    Posts
    5,629

    Default

    I've built dozens of membership systems in the last 20 years. What I've always done is use a posting rule in the contributions table. Each time a contribution is made, it posts the date of the contribution to a field in the Member's record. I usually call the field something like LastGiftDate.

    This gives you all sorts of flexibility. You can easily query for members who have one month left; query for just active members, etc.
    -Steve


  7. #7
    "Certified" Alphaholic
    Real Name
    jim chapman
    Join Date
    Apr 2000
    Posts
    1,779

    Default

    Hello Steve,

    I need to create a report of people who have not donated in a year or more
    I agree with what has been posted. The options mentioned will give you flexability. But you can print out or filter to show only those parent records where a child "donations" does not exist ( and of couse apply your date range as well). The filter expression:

    .not. scanning("donations")

    should do it for you.

    BTW is that Cub as in Piper?

    Good luck,
    Jim

  8. #8
    Volunteer Moderator Steve Workings's Avatar
    Real Name
    Steve Workings
    Join Date
    Apr 2000
    Location
    The Dreaded Chair
    Posts
    5,629

    Default

    I actually maintain far more info than just the last gift date. If the date is empty, you know they haven't contributed. I also maintain such items as highest-previous contribution (HPC). If someone gave my organization $1,000 once there is no way I'm ever going to ask them for just $25 again. I'll start with their HPC and build suggested contribution amounts from there.
    -Steve


  9. #9
    Member CubDriver's Avatar
    Real Name
    Steve Johnson
    Join Date
    May 2003
    Location
    Kansas City, MO
    Posts
    21

    Default

    Quote Originally Posted by Jim Chapman
    BTW is that Cub as in Piper?
    Super Cub / Piper Cub - You betcha!

    Thanks for all the help folks, you have really gotten me headed in the right direction - At least for the moment.. ;)

    sj

Similar Threads

  1. Beta Period has expired
    By Dennis Mathias in forum Alpha Five Version 5
    Replies: 2
    Last Post: 01-22-2004, 07:57 AM
  2. Bug Reporting
    By Bill Griffin in forum Alpha Five Version 5
    Replies: 4
    Last Post: 07-07-2003, 02:17 PM
  3. Help with Reporting
    By Scott Karrick in forum Alpha Five Version 4
    Replies: 1
    Last Post: 11-04-2002, 01:23 PM
  4. Reporting
    By alexmuir in forum Alpha Five Version 4
    Replies: 4
    Last Post: 10-20-2002, 05:31 PM
  5. Grace period expired
    By John Webster in forum Alpha Five Version 5
    Replies: 3
    Last Post: 07-25-2002, 12:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •