5 Replies Latest reply on Oct 22, 2014 3:47 PM by PatriciaAngelini

    filtered sum to be used in calculation

    AdamReed

      Title

      filtered sum to be used in calculation

      Post

      I would like to get the sum of expenses that have not been applied, but exclude those that have been applied from the sum.  I would then like to use the sum in a calculation (deducting that amount from a payment to a client, and assigning it to the business).  My thought was:

      If ( paid = "no" ; Sum ( amount of expense ) ; 0 )

      ...but this doesn't seem to be working properly.  Any ideas?  (Is this enough information?)

      Thanks for any help,

      Adam

        • 1. Re: filtered sum to be used in calculation
          philmodjunk

          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.

          • 2. Re: filtered sum to be used in calculation
            AdamReed

            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 = ?).

            Thanks again.

            • 3. Re: filtered sum to be used in calculation
              philmodjunk

              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.

              • 4. Re: filtered sum to be used in calculation
                AdamReed

                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.

                • 5. Re: filtered sum to be used in calculation
                  PatriciaAngelini

                  Greetings.

                  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.

                  Thank you.

                  PJ