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?
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?
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.
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.
Member Dues.fmp12.zip 69.3 K
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.)
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.
Have a server script do it for you. It can run on a daily basis and check for void memberships....