This is a fairly frequent question.
If ( Boolan Expression ; Sum ( fieldName ) ; 0 )
Fails two different ways.
Sum ( FieldName ) is the same as just entering the reference to the field name. It's lik asking "what's the sum of 2"? And the sum of 2 is 2 of course. Other uses of Sum result in a list of fields passed as a parameter to it.
Sum ( field1 ; field2 ) sums the value in these two fields.
Sum (Repeatingfield) sums the repetitions.
Sum ( relatedTable::FieldName ) sums the value in FieldName over the set of related records.
The If function doesn't work in this context either.
See this thread for ways to selectively sum data that do work: Sum_Calculation based on condition
And if you have FileMaker 12, the Execute SQL function can also be used to selectively sum values: FMP 12 Tip: Summary Recaps (Portal Subtotals)
If you have any questions on this, please feel free to use Post A Answer to post them here in this thread.
Thank you. I've successfully created the portal row mentioned in the first thread you link to (Option 2), but would like to use that value in a other calculations, and wasn't able to get Option 1 to work. I have a table Expenses, in which are the fields ExpenseID, AmountOfExpense, and Paid (yes/no). If I understand correctly I need another instance of the Expenses table, but am unclear about the relationships (ExpenseID = ExpenseID AND Paid = ?).
You'll need to use option 1. Option 2 is great for displaying such totals, but you can't "get to" the value to use it in a calculation.
If I understand correctly I need another instance of the Expenses table, but am unclear about the relationships (ExpenseID = ExpenseID AND Paid = ?).
Yes, you'll need a new occurrence of the expenses table then use a calculation field that returns a constant value as part of the relationship so that only "unpaid" records are related.
Define constUnpaid as a calculation that returns text. Put "No" as the sole term in its calculation.
use it in a relationship like this:
Your Table::constUnpaid = UnPaidExpenses::Paid
You'll want additional fields in this relationship, but I don't know enough about your database design to know what table should be used in place of "your table" nor what additional fields will correctly match to the group of records you want to sum. You want a value that matches to a group of records in UnPaidExpenses, that might be an InvoiceID, budget category, a date range. Whatever that is, you'd add more match fields to make that part of your relationship such that
Sum ( UnPaidExpenses::ExpenseAmount )
computes the total of a set of related, unpaid expenses in the Expenses table.
I'll do some testing, but that seems to work! I created another instance of the table, in which expense id = expense id, and paid = unpaid expenses (unpaid expenses being a calculated field ="no"). Then I displayed "total expenses" from the second instance of the expense table, and seem to be getting the correct result. Many thanks.
I am trying to do a sum that is not quite the scenario above and would like assistance. In the cTotalAdministration field I would like to put a calculation that
The table ProposedBudget has a field called Category.
In the cTotalAdministration field in the ProposedBudget table, I would like to put in a calculation that gives me the sum from all records that have "Administration" in the category field.