If ( Category = "Student Support | Bursar" ; Sum ( Amount) )
Makes no sense. Since there are no tables names, Category, Amount and this calculation would seem to be defined in the same table (Monthly Accounting?) and thus the calculation would return nothing or the value of that record's Amount field. (You might want to look up Sum in FileMaker help to note what syntax should be used to get a sum.
Selectively summing values can be done through several different approaches:
If you have FileMaker 12 or 13, you can use the ExecuteSQL function: FMP 12 Tip: Summary Recaps (Portal Subtotals)
If you don't have one of those versions or you don't wish to use SQL, there are other options: Sum_Calculation based on condition
Thanks for the feedback, Phil. I hope answering this question yet again helps build your wealth - yes I have read the other posts on this form and this questions seems to pop up a number of times , so it seems to not be a trivial question! Deceptively simple to sum based on values in another column.
My two tables are connected by the Expense ID match field. I have attached the relationship map.
I am very frustrated at this point. Have 2 tables. One for data, one for calculations. What I posted previously was an attempt to calculate within the data table. Have gone back to calculations table ("Expense Framework") which only holds a match field, fiscal year, fiscal month, and calculations. Data table holds match field, record id, category, an explanation field, and the amount of the transaction.
No matter what I do, I get the total sum of the amount rather than selecting out by category. I need income for month, expenses for month based on value in "Category". Then will subtract from beginning of month (this is correctly carried forward from prior record) to get end of month total (and this calculates correctly).
I am sure it's something simple that I am totally overlooking, but I just don't see it....
The methods I've already posted links to can be used to selectively sum your data. Here's a more detailed description of just one of those methods:
Define a Summary field in Monthly Expenses Data to compute the total of Amount.
Put a one row portal to Monthly Expenses Data on a layout based on Expense Framework. Define this portal filter expression:
Monthly Expenses Data::Category = "Student Support | Bursar"
Put the new summary field inside the single row of this portal.
It will now show the total that you want.
You can now add additional one row portals and just set up each with a different portal filter expression.
But if you want a report listing all expense categories with a single row for each category and the total of amount for each such category, I would use a summary report layout based on Monthly Expenses Data. A sub summary layout part "when sorted by Category" can be set up and both the category field and the same summary field I described using with the filtered portals can be placed inside this layout part. You can remove the body layout part to limit the output to one row of data per category. You can perform finds, constrain found sets and use Go To Related Records to control what records are included in such a report so long as you always keep the found set of records sorted by category.
Here's a tutorial on summary reports: Creating Filemaker Pro summary reports--Tutorial