7 Replies Latest reply on Mar 24, 2016 9:34 AM by luis@caltec.pt

    Identifying members whose membership has lapsed


      I have a membership database where the Member table is one-to-many related to the Dues table. I want to identify those members who were members last period but who have not yet renewed this period and send them an email reminder.


      The Dues table contain the DuesPeriod field, which holds the membership year. Though likely not relevant, membership periods start on even numbered years and run for 2 years, so the DuesPeriod field will contain numbers like 2010, 2012, 2014, 2016.


      The query is easily expressed in SQL or as sets: { return the set of members such that there exists is at least one Dues record for that member with DuesPeriod = 2014 and there does not exist a Dues record for that member with DuesPeriod = 2016}.

        • 1. Re: Identifying members whose membership has lapsed

          I have something similar. What I use is a Current Member field that uses the membership end year set by script when the Dues are paid. I can use that year field to find immediate past members or members that have missed 2 cycles etc. no SQL required. I am assuming you can only be a member once per cycle although someone could pay their dues more than once in a cycle so some error checking might be required. You would also need to calculate the end year based on a Dues paid date to update your current records. Would that work?


          • 2. Re: Identifying members whose membership has lapsed

            I wrote something like this too. My solution lets you pick three different renewal period frequencies. The solution then calculates the next due date based on the type of membership period.


            The actual "Renewals" layout (button at top) allows you to send one or all notifications by email or print (the email button doesn't show up on that layout, of course, if you haven't entered an email address for the client).


            Is this type kind of thing you're doing?


            - m


            • 3. Re: Identifying members whose membership has lapsed

              Assuming you can see a portal in the "Members" table that shows you the contents of the related "Dues" records, do a 2-part Find operation. Part 1 is for values of DuesPeriod = 2014. Part 2 (new request with Omit option) is for values of DuesPeriod = 2016.

              • 4. Re: Identifying members whose membership has lapsed

                I would have a field in the member table for the date of the next membership due.  The field would be generated via a script trigger.


                I’d also have a field for people who have discontinued.  This enables you to keep those members in the database and look at trends over previous years.  It also allows that person to resumer her membership.


                It is then simply a matter of finding records where the membership date is the current year.  In the attached file, you would enter find mode and type 2016 in the nextPaymentDue field.



                • 5. Re: Identifying members whose membership has lapsed

                  Is my screenshot above too simplistic or does it approximate what would be needed for a membership application?  I'm seriously wondering since I did this solution in a couple hours. The complicated part was calculating the correct renewal date given the membership renewal type automatically.


                  But, yes, I also have the next membership date in the member table.  My screenshot above also includes an "Active" radio button so you can track active vs. non-active members.


                  On the 'Renewals' layout (not shown, but button at top), you would see all the current renewals pending, automatically. If the email address is filled in, you can either email or print the renewal. If no email, then only print button shows.


                  My sample solution also includes a "Defaults" table to hold constants and other amounts so they are not hard-coded.


                  (other cool stuff)




                  (Meant to mention that the name - Cindy Abergast - in my screenshot above is completely fictional -- I made it up for the screenshot.)


                  - m

                  • 6. Re: Identifying members whose membership has lapsed

                    thanks for all the suggestions. Reviewing them I came upon a solution which I'm happy with.


                    I created an unstored calc field on the member record called lastduesperiod which is populated by an executesql equivalent to select max(DuesPeriod) from Dues where _fk_memberid = memberid of current record.

                    • 7. Re: Identifying members whose membership has lapsed

                      Have a server script do it for you. It can run on a daily basis and check for void memberships....