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

    Conditional Sum Function

    gmremson

      Title

      Conditional Sum Function

      Post

      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?

       

      Gary

       

        • 1. Re: Conditional Sum Function
          mrvodka
            

          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 ) ) ]