3 Replies Latest reply on Feb 11, 2009 3:28 AM by Orlando

    Sum total based on value in corresponding text field

    littlejon

      Title

      Sum total based on value in corresponding text field

      Post

      Newbie to FMP question.

       

      I have a table with 'type' and 'amount' fields. I'd like to sum the amounts for each type.

        

      How do I create the equivelant of:    select sum(amount) from budget where type = 'income'?

       

      I can find examples of: "Sum(Income::Budget)" but I need to add the equivalent of where criteria to a calculated field. 


        • 1. Re: Sum total based on value in corresponding text field
          Orlando
            

          Hi littlejon and welcome to the forum

           

          Where do you want these values totaled and displayed, is it on a standard record layout or as part of a report? And also do you have a Type Table of values, or is it simply set in a value list?

          • 2. Re: Sum total based on value in corresponding text field
            littlejon
              

            Hi, Orland. Thanks. It's good to be part of the community. 

             

            There may be better designs, but I was trying to create a calculated field in this table. This table's 'category' field will be the value list for a category field in a separate 'transaction' table. I toyed with the idea of creating a category table that would be the master list and still could but for now this table's category field is the master list. 

             

            Basically, I'm trying to create a budget db. The table I'm referring to is the budget table. I'm trying to create a calculated field for total income and a separate calculated field for total expenses.

             

            I'm new to FMP and don't know the terminology, but wanted to use the total in various reports and layouts. 

             

            Appreciate your help.

             

            littlejon 

            • 3. Re: Sum total based on value in corresponding text field
              Orlando
                

              Hi littlejon

               

              If I am reading this correctly, you are trying to calculate the sum of all Transactions related to the Category field in the Budget table you are viewing. Is this correct?

               

              If so then you need to create a relationship between your two tables based on the Category fields:

               

              BUDGET             Budget_TRANSACTIONS

              Category  --=--   Category

               

              Then in your BUDGET table create a new calculated field called cExpenses, and assuming the field in TRANSACTIONS for the amount you want to total is called 'Amount', then the calculation would be:

               

              Sum ( Budget_TRANSACTIONS::Amount )

               

              This will then sum the value of Amount for all the related records, so if you want to filter this down you would need to modify the criteria of the relationship.

               

              Let me know if this help, or if I am going down the wrong path altogether.