4 Replies Latest reply on Apr 24, 2012 9:08 PM by KBGF75

    comparing field content on different dates



      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?

        • 1. Re: comparing field content on different dates

          It's not clear what you mean by "compare data on the last day of two different months".

          Do you want to compare data for all donations for the Month of Feburary for two different years? Perhaps monthly totals or averages?

          If that's what you have in mind, I don't see any need for working with multiple copies of data nor using a spread sheet program with exported data.

          • 2. Re: comparing field content on different dates

            Yes, I want to compare data from two different months. Usually the months will be in different years. Some finds are easy, such as total donations in a range of dates. That's because each donation is a separate record that contains date and amount fields. It's easy to sum all donations in a date range.

            But it's not easy if I want to find the number of active members on a certain date in the past, say the last day of Feb 2012. The contact record does have a field for membership status, but the only date fields on the contact record are for Date Record Created, Date Record Last Modified, and Month (and Year) Membership Expires.

            In order to do such a search, I'd want to find all Active Members within a date range (assuming date-range can be narrowed to a single date).  Trouble is, none of those date fields in a contact record seems relevant for such a search. The only alternative I see is to pull up a copy of the file archived on the last day of the month in question, then count the number of Active Member records.

            So I think I'm left with the question, How can I change what I'm doing so tracking membership will be easier in the future? One simple option: Find the membership numbers at the end of each month, then export them to a spreadsheet. Accumulating future data every month in a single spreadsheet should be more efficient than case-basis searches of archived FMP files.  Maybe there's a better way?


            • 3. Re: comparing field content on different dates

              How can I change what I'm doing so tracking membership will be easier in the future?

              I'm a bit confused here. You talk about comparing things on a monthly basis and then talk about specifying a specific date--such as the last day in February. If you want this for a specific day, your monthly exports will not work as this records member status on a month to month basis.

              Assuming that you really need a monthly record of member status...

              Add a new table to your database and use it to log membership status on a monthly basis. This can be record with three fields, the memberID, the status and the date for the first day of that month.

              A script can use import records to import that data from all records (or a selected found set if you want to omit records for certain status types such as a member that has died...) in your membership table into this new table. If you host this file with FileMaker server a server schedule can control a script to do the monthly updates.

              You can then query this new table for month specific membership data.

              • 4. Re: comparing field content on different dates

                I want to do various comparisons of data. Finding the desired data is easy in some cases, hard in others. I needed help only with the hard cases. I like your suggestion of adding a new table that can store selected snapshots of data at a chosen point in time. Thanks.