3 Replies Latest reply on Feb 18, 2015 1:20 PM by philmodjunk

    Newbie Question about Groups and sums

    johnjohn

      Title

      Newbie Question about Groups and sums

      Post

      Hi to all,

      I am total newbie on this so sorry for the stupid question.

       

      In my struggle to understand filemaker i have the following scenario

      I have a table with the following records and fields

      ............field1...........field2

      rec1.....1/1/2014.........3

      rec2.....1/1/2014........5

      rec3....3/3/2014........12

      I need to create another table that i keep the grouped sums of the above table and updated every time a new rec created.

      like..

       

      rec1.......1/1/2014.....8

      rec2.......3/3/2014....12

      similar the sql command select field1,sum(field2) from table GROUP BY field1

      How can i do this?

        • 1. Re: Newbie Question about Groups and sums
          philmodjunk

          It's quite possible that you do not need the second table at all. You can generate a summary report with one row of data for every unique value of Field1 with a sub total placed next to it. See this thread for a tutorial on summary reports: Creating Filemaker Pro summary reports--Tutorial

          But you can also define a table where field 1 is the date shown and used as a match field to set up a relationship in Manage | database | relationships linking the two tables by date:

          Table1::DateField = Table2::DateField

          Then Table2::field2 can be defined as a calculation field: Sum ( Table1::Field2 ) to compute the total.

          It is also possible to set up calculation fields that use ExecuteSQL to compute such a an aggregate value.

          • 2. Re: Newbie Question about Groups and sums
            johnjohn

            Thx for the info...

            I would like the table option i need the data to munipulate them (do some other callcs - send them to external charting engine....)

            The problem now is on how to populate the new table with unique values from the Table1::DateField..

            • 3. Re: Newbie Question about Groups and sums
              philmodjunk

              What you describe does not sound like reasons to have the second table. Your other calcs might possibly be done as part of your summary report design and if you have to export this data for the "external charting engine", you can export the summarized data such that you get one row of exported data per date just as you would have in your summary report.

              There are many ways to populate such a table. Here's just one:

              Define your relationship to match by date fields, but also double click the relationship line and select "allow creation of records via this relationship" for the second table.

              Then this script step, performed from each record of the original table:

              Set Field [Table2::DateField ; Table1::DateField ]

              Will create new records in Table2 without creating any duplicate records. Please note, however, that you may need to take additional steps to handle situations where a record in Table1 is deleted or the date is modified to be a new date, leaving an "orphan" record in Table2.