4 Replies Latest reply on Nov 14, 2012 10:08 AM by PhilCornish

    Calculated field that would sum dollar amounts within a specified date range.

    PhilCornish

      Title

      Calculated field that would sum dollar amounts within a specified date range.

      Post

           We are looking to set up a calculated field that would sum the amounts (see picture) within a specified date range.  In this case we are looking for it to sum the amounts after the year 2010, which in this example would equal $20.  However, we’d like to be able to change the date parameters in the future.  Assuming we could do that relatively easy once the initial operation/calculation is set up.

           We’d prefer to have this calculation field rather than get the same results via a search/find operation because we’d like to be able to output the dollar amount for further analysis.  Not sure if that makes sense or not...hope so!

           Thanks in advance for any help.

        • 1. Re: Calculated field that would sum dollar amounts within a specified date range.
          philmodjunk

               Would the date range always be terms of years or might it be something like 1/1/2012 to 12/31/2013?

               There are ways to set up relationships that match to a set of records by date range and then you can use that relationship to compute the total. By editing the values in the match fields used in this relationship, you can match to different sets of records to get different totals.

          • 2. Re: Calculated field that would sum dollar amounts within a specified date range.
            PhilCornish

                 Thank you fellow Phil (I am Phil as well), the date could indeed be more specific for future needs.  For my current need I am just trying find recent donations in 2011 and 2012, so >2010.  If this helps you provide further details on how I can set up this relationship, I would be very appreciative.  I am about average in knowledge of FMPro, so specifics would be great.

            • 3. Re: Calculated field that would sum dollar amounts within a specified date range.
              philmodjunk

                   I don't know the tables involved, but when you set up a relationship, you can set up something like this:

                   Maintable::gYear < Donations::cYear

                   where gYear is a global number field. and cYear is a calcualtion field that returns a number set up as: Year ( Donationdate ).

                   You can also set up relationships such as:

                   MainTable::gYear1 < Donations::cYear AND
                   MainTable::gYear2 > Donations::cYear

                   to match on a range. And you can use date fields in such relationships instead of years.

                   On the other hand, you'll find a great deal more flexibility if you perform a find for donations records on a layout based on Donations. You can easily sort and group records with sub summary layout parts to get any number of different record groupings and totals and you can easily refer to a Donors table to access data about each donor in such a report as well.

                   And the results of such a report can be exported.

                   There are also ways to use a one row filtered portal and a summary field such that you modify values in fields used in the filter expression to see different totals appear in the portal, but such totals cannot be exported.

                   IF you are using FileMaker 12, you may also want to investigate using the ExecuteSQL function as a way of extracting such aggregate values from your table of donations.

              • 4. Re: Calculated field that would sum dollar amounts within a specified date range.
                PhilCornish

                     Dear Phil, you pointed me in the right direction and I was able to figure this out!  Thanks!  I went the subsummary route.  Thanks!