Currently I have two tables, Contacts and Memberships. One contact can have only one membership that can be renewed each year or at any given date. Each membership can be shared by multiple contacts
Contacts >---- Memberships
This works great, but more recently I've decided that I would like to pull reports from these tables to produce figures like:
how many new members in 2015?
how many members renewed in 2015?
how many members didn't renew in 2015?
At the moment, the membership table contains three fields the creation date, a renewal date, and an expiry date. When a membership is renewed the renewal date is overwritten and thus a record of each membership is lost with each renewal. I hope this makes sense. Essentially I would like to undo this lossy process and record a running list of renewal dates for each membership in order to pull reports. So I figure there are two ways I could do this. One, either create a field inside of memberships that contains a list of renewal dates or two, create a second table that is related to the memberships table containing a new renewal date field that copies the contents of the renewal date into the new renewal date inside the new table.
1) Starting with my first thought, I could create a field that would contain a list of renewal dates.
Renewal Date List Field
Then create a script that is triggered when a membership is renewed and marked as paid in order to add a new renewal date to the field list. I would also have to be able to remove dates from this list incase a membership is placed accidentally by the user. This is where I have started to go... but I'm having issues adding and removing renewal dates to the renewal date list for each membership.
For instance, I've created a script that adds values to the renewal date list field inside the Memberships table. Then uses the FileMaker Custom Function:RemoveDuplicates ( Text ) to remove the duplicate values incase the user triggers the script on the same date more than once. This is what I've got so far:
Set Field (DateRenewalList)
RemoveDuplicates(DateRenewalList & ¶ & DateRenewal)
My result is:
¶2013-02-13¶ OR after a few triggers ¶2013-02-13¶¶2014-02-13¶¶2015-02-13¶
But what I want is:
2013-02-13 OR after a few triggers 2013-02-13¶2014-02-13¶2015-02-13
This script is triggered when the user marks the membership as paid. Which brings to mind....what if the user marks the membership as paid only to realize they made a mistake and then go ahead and mark the membership as unpaid? In this instance, I will need to set up a script that is triggered when the payment is marked as unpaid in order to remove the renewal date from the renewal date list.
So far I have not been successful in removing the renewal date. I tried using the FileMaker Custom Function:RemoveValue ( Value ; ValueList ) but I don't understand how to adapt this function to search for a date value rather than a string of letters.
2 ) Lastly, I also realized that I may be able to achieve the same solution by creating another table name Membership Renewals that relates to to the Memberships ID field in the Memberships table.
Contacts >---- Memberships ----< Membership Renewals
I'm not sure which option would be more efficient or more difficult. Or apparently how to fully execute either of them. If anyone with a bit more experience could offer some advice I would be incredibly appreciative. Also if there's another option that may be simpler and more elegant...please enlighten me!