2 Replies Latest reply on Oct 10, 2012 8:37 AM by dhillock

    Conditional Sum Field



      Conditional Sum Field


           I have a table called product, and another table called orders. It's a one-to-many relationship.

           I have a field product::TotalSales that is the Sum(Orders::TotalAmount). This works great.

           I want to creat a field (product::Sales2009) that is the Sum(Orders::TotalAmount), if the Orders::Year = "2009"


           I tried using a if clause in my calculation, but that does not work. I know that I can write a script to populate the Sales2009 field, but it would be more convenient to have a calculation field. Can this be done?

           All ideas are appreciated.

        • 1. Re: Conditional Sum Field

               Sum cannot conditionally sum values. To sum just the orders where the year is 2009, you need to limit the values that Sum can total up to just 2009 entries before the values get to the sum function.

               Note: I am assuming that the totals you want are for a specific product and year. The relationship details for Option 1 would change if you want a total of all products purchased in a given year.

               Option1: Create this relationship to a new occurrence of Orders:

               Products::ProductID = Orders2009::ProductID AND
               Products::constYear2009 = Orders2009::Year

               Define constYear2009 as a calculation field that returns the number 2009.

               Now Sum ( Orders2009::TotalAmount ) will compute your total for that year.

               Option2: creat this calculation field, c2009TotalAmt, in orders:

               If ( year = 2009 ; TotalAmount )

               Now Sum ( Orders::c2009TotalAmt ) will compute totals for the year 2009.

               Both of these options would require adding a new calcualtion field and (for option 1) a new relationship for every year. This need not be the case, however. In option 1, you can replace constYear2009 with a global number field and then you can get a yearly total for any year that you enter into that global field. In Option 2, you can rewrite the calcualtion field to be If ( Year = globalYearField ; TotalAmount ) and then it also would allow you to get a total for any given year without having to add new details to your datbase design for each new year.

               Option 3: Don't use the sum function at all, use a filtered portal with a summary field from Orders in it. This requires FileMaker 11 or newer. Define sTotalTotalAmount as the total fo TotalAmount. Put it in a one row portal to Orders and give it a filter expression that limits the related records to just a given year. Like options 1 and 2, you can make the year something the user specifies in a field or you can "hardwire" the expression to a specific year.

               Option 4: Don't use ths sum function, use ExecuteSQL to compute the desired total value. This requires FileMaker 12.


          • 2. Re: Conditional Sum Field

                 What a great response Phil. Thank you.

                 You are correct in assuming that I'm looking for totals for a specific product and year. And while I'm intreged by the ExecuteSQL approach, I'm under-the-gun to deliver this report, so I'm going with Option 2. It's not elegent, and it requires me to have four fields in each table (Sales2009 to Sales20112), but it's not a lot of overhead, so I'm comfortable with that.

                 Again, thank you for a great reply.