Filemaker for Membership Orgs
Forgive me if this is not well explained. I've been at this for hours.
I've been asked by a friend to develop a Filemaker database for his small membership organization (<200 members.) I use FM for contact management and know the program fairly well, though I'm not an expert.
I've built a database with all the necessary stuff (name, address, phone/fax nos. etc. I've assigned each member a unique, self-generating Membership Number, along with start and expiry dates. (Memberships are $25./yr)
Where I'm running into problems is the year over year situation. After the first year is done, I'm not quite sure how to handle renewals. In late 2004, he'll send out renewal notices and it won't make sense to re-key everything into a new database, but at the same time there will be new start and expiry dates. (And the Membership Number must stay the same across years for tracking purposes.) Overwriting the old date info would leave him without a proper record of the past year.
I'd like to keep everything in one master database for as long as the organization operates, but I can't think of a graceful, easy-to-use way to pull it off.
Any Filemaker gurus here ever handle something like this?
I've been asked by a friend to develop a Filemaker database for his small membership organization (<200 members.) I use FM for contact management and know the program fairly well, though I'm not an expert.
I've built a database with all the necessary stuff (name, address, phone/fax nos. etc. I've assigned each member a unique, self-generating Membership Number, along with start and expiry dates. (Memberships are $25./yr)
Where I'm running into problems is the year over year situation. After the first year is done, I'm not quite sure how to handle renewals. In late 2004, he'll send out renewal notices and it won't make sense to re-key everything into a new database, but at the same time there will be new start and expiry dates. (And the Membership Number must stay the same across years for tracking purposes.) Overwriting the old date info would leave him without a proper record of the past year.
I'd like to keep everything in one master database for as long as the organization operates, but I can't think of a graceful, easy-to-use way to pull it off.
Any Filemaker gurus here ever handle something like this?
Comments
1) You could put in multiple start/expire date fields. This is a kludgy solution, but I have seen it used many times.
2) You could add another field to the record with a year marker, and then duplicate the record, changing the proper fields. Then make sure that the database is always sorted by the year field, and you will have the latest record always on top.
For bonus points on that last one, you could add in a self-referencing relationship keyed on your customer-id (or name if you are using it as such) and put a portal on each page linking to the other years.
3) Or, you could move the yearly information to a second file, and then relate it in. Then you could change the start/expire dates on the main file to be the fields in the related record. If you sorted the relationship by one of the dates, it would always have the latest information. Then you just need a portal to show the old information.
For bonus points here you could even create a calculation that subtracts the current date from the expire date as a number. Then number format the display field to be red on negative and use days as a suffix.
If you need more help, ask. Or if you want to contract for this, I could do it either Thursday or Friday for $50. If you want to contract for it email me a [email protected]
If he just wants to know who has a current membership, add a button to increment the 'end' field for subscriptions. For example:
Joe Smith was a member from Jan 2003 to Jan 2004. He renews, so your friend just goes into the database, clicks the 'Renewal' button, and now the expiry date for Joe Smith's membership reads Jan 2005.
There's no real reason to touch the Start date for memberships, unless someone has chosen to let it lapse for a year. In that case, you either want to give them a new membership number, or you have to attach two memberships to the same number... which would be best in a related database of memberships.
You could simply click the Renew button, but it would then span the lapsed year, which can cause problems later. What if there was a gift T-Shirt for the previous year, and he calls complaining he didn't get one? Well, he shouldn't, since he wasn't a member that year... but under this system, he would show as if he were.
I know it feels nice to have everything tucked into one database, but generally speaking it's best to have contact info in a seperate database, and just link it to other DBs for other information (membership, project groups, "do not call" lists, etc.).