1 Reply Latest reply on Mar 12, 2010 3:11 PM by mrvodka

    Conditional sum



      Conditional sum


      I have two tables: Expenses and Budget_Overview.


      Table: Expenses

      Fields: Trade_Show_ID, Trade_Show, Category, Date, Amount


      Table: Budget_Overview

      Fields: Trade_Show_ID, Airfare_Actual, Hotel_Actual....etc


      Trade_Show_ID are related.


      So the Expense table will have one expense is one record. In Each expense there is a specific category and trade show id. I need to sum up all the amounts that fall within a given category within a trade show id.


      I have a field in the Budget_overview table called Airfare_actual. I have this set to a calculation field. This is what I have: Airfare_Actual=if ( Expenses::Categoy = "Airfare" ; Sum (Expenses::Amount))


      It sums the amount but has no regard for what the category is set to. It does only sum the amounts for the related trade show id though.


      Any Help would be great.


        • 1. Re: Conditional sum

          You could have a calculation field return the string "Airfare" and then relate that calculation to the category in a table occurrence of your expenses table.


          Alternatively, why dont you just create a report that shows your expenses grouped by category? In FMP10 and + you can even have this subsummary report in browse mode.