comparing field content on different dates
My FMP9 file includes two tables, Contacts and Donations. I use scripts to summarize a few membership statistics, such as the number of initial donations (new members) in a range of dates. In that case I count the number of donation records that are categorized as “initial.” That task is simple because each donation record has a “date created” field and a donation category field.
I now have a different challenge. I want to compare data on the last day of two different months, such as 28 Feb 2011 and 29 Feb 2012. I want to find a set of records that meet certain criteria on a specific date, typically the last day of a month. Example: find the number of “active members” on the particular date. The contact record has a field showing membership status, but the field contents are not associated with a particular date. One contact may be an active member continuously for years, another will lapse and rejoin randomly.
I can see only one way to assemble data for such a comparison: use archived copies of the file. I would have to perform finds in each of two archived copies, then export the results (or copy by hand) into a spreadsheet for review and sharing. So, are there changes I can make (simpler the better) to enable a more efficient process for handling future data?