I have two tables: Expenses and Budget_Overview.
Fields: Trade_Show_ID, Trade_Show, Category, Date, Amount
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.
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.