2 Replies Latest reply on Mar 18, 2014 8:46 AM by BrooksLeisure

    Calculate Total based on ID

    BrooksLeisure

      Title

      Calculate Total based on ID

      Post

           Hi,

           I am using Filemaker Pro 12 Advanced.

           I have a table called 'Info' with four fields; ID, Number, Total, Twenty Percent.

           I want the 'Total' field to calculate the total of the 'Number' field based on the 'ID' and then I need 20% of that total

           I understand that I could get the total by ID using a summary field but I need to reference these fields on another related table and I cant seem to do that with a summary field.

           For example:

           Record 1: ID = 1  Number = 100 Total = 400 Twenty Percent = 80

           Record 2: ID = 1 Number = 50 Total = 400 Twenty Percent = 80

           Record 3: ID = 1 Number = 250 Total = 400 Twenty Percent = 80

           Record 4: ID = 2  Number = 60 Total = 240 Twenty Percent = 48

           Record 5: ID = 2 Number = 30 Total = 240 Twenty Percent = 48

           Record 6: ID = 2 Number = 150 Total = 240 Twenty Percent = 48

           ect...

            

           I have tried:

           Case(ID = "1" ; Sum(Number) ; ID = "2" ; Sum(Number))

           but this doesn't seem to work, it just shows each records 'number' in the total field

            

           I hope that makes sense and thank you in advance for any help

        • 1. Re: Calculate Total based on ID
          philmodjunk
               

                    but I need to reference these fields on another related table

               Is the key detail as it defines the needed "context" here. Define a calculation field in that "other related table" in one of two ways:

               Sum ( YourTable::NumberFieldHere ) * 0.2   // multiple by 0.2 to get 20% of the total

               or

               YourTable::SummaryFieldHere * 0.2

               When you refer to a summary field defined in table 1 from the context of table 2, you get a sum (or other aggregate value) based on the set of related records (all the records in Table 1 with the same ID as the ID of the current record in table 2). Sum uses the same relationship to get the same total and will update more smoothly if you are using a portal on the "related table" layout to edit these records in ways that will require that your computed value update to show a new total.

          • 2. Re: Calculate Total based on ID
            BrooksLeisure

                 Thank you very much, works perfectly :)