1 Reply Latest reply on Feb 9, 2010 8:35 AM by mrvodka

    Conditional Sum Function



      Conditional Sum Function


      I am looking to conditionally add a field from a series of records together to create a calculation.


      I have the Master Address List (MAL) and Donations databases linked by an MAL_ID field. When I use Sum(Donations::Cash Amount) function I get the grand total of all the donations for each MAL address.


      I wish to add together the donations made for the current year together to get the total for the current year only, thus eliminating the previous years from the calculated total.


      Ideally I thought the following would work . . . Sum (If (Year(Donations::Date)  ≥  Year(Get(CurrentDate)) ; Donations::Cash Amount))


      Alas, the above function does not work. Could someone steer me in the right direction to create the formula?




        • 1. Re: Conditional Sum Function

          Create a stored calculation field in donations. Year ( Donation::Date )


          Create a global number field  ( gYear )in the parent table MAL.


          Create a new relationship keyed from:


          MAL::MAL_ID = Donation::MAL_ID

          MAL::gYear =Donation::cDateYear


          Now all you have to do is set the gYear field with any year that you want. On startup you can want to call a script that sets it to the current year.


          Set Field [ gYear; Year ( Get ( CurrentDate ) ) ]