5 Replies Latest reply on Sep 28, 2015 8:52 AM by Powerbook

    Summarize recurring fields


      I'm discovering the power of Filemaker, and I started to se if i could do a sum of a recursive field, and store the result in a variable to be used in a report.

      I' was able to summarize those fields, but I'm not able to store the result, ore better I do not know how

      This is what I would like to accomplish :


      “<MyField>” is the field that has different values in Text format (es: aaa, bob, ccc, ddd, eee, ,,,,,,,,,,)

      “Total” is the  number field that I would need to summarize for each different “<MyField>”  occurrence.

      And then store the single result value tof each one o be use or in layouts or reports.


      This is how my table looks like:


      <MyField> Total


      aaa 10

      aaa 3

      aaa 12

      bbb 12

      bbb 7

      ccc 4

      ccc 10

      ccc 6

      ….. ……


      This is what I need to get: (a single line for each Case occurrence)


      Case Total

      aaa 25

      bbb 19

      ccc 20


      Once I obtained this results, how can I memorize them as variables ?

      I would love to be able to store them as multiple global field es:







      tin order to use them after in different Layout or reports.


      Is ti possible? How can I do that?


      Thank you in advance,



        • 1. Re: Summarize recurring fields

          I suggest you rename your field; there is already a Case() function, and that may get confusing … for now, let me call it <yourField>.


          Do you actually need to capture the resulting values, or do you just want to display them?


          For display in a report that is based on that field, use a layout in List view with a sub-summary part sorted by <yourField>; create a summary field that counts the primary key (or your <yourField>); put that field into the sub-summary part, if necessary, remove the body part, and sort by <yourField>.


          If you really need the result in a bunch of variables, let us know …

          • 2. Re: Summarize recurring fields

            Hallo eroist,


            Yes you are right, case was a mismatch......

            I need to store the different results in order to display them ih a particular layout of a report.

            Otherwise with a normal layout I will not be able.

            • 3. Re: Summarize recurring fields

              Screen Shot 2015-09-28 at 16.26.12.png

              this is a simplified report I would like to produce.

              • 4. Re: Summarize recurring fields

                OK; so I'm not sure that a bunch of $$vars is really what you need, but you can create these variables quite easily, with a script like this one (assuming you have at least FM12):


                Set Variable [ $allValues; Value:

                  ExecuteSQL ( "

                    SELECT DISTINCT ( testField )

                    FROM Tasks

                    WHERE testField IS NOT NULL

                    " ; "" ; ""



                Set Variable [ $CountAllValues; Value:ValueCount ( $allValues ) ]


                  Exit Loop If [ Let ( $i = $i + 1 ; $i > $countAllValues ) ]

                  Set Variable [ $dummy; Value:

                    Let ( [

                      curValue = GetValue ( $allValues ; $i ) ;

                      res =

                        ExecuteSQL ( "

                          SELECT COUNT (*) FROM Tasks

                          WHERE testField = ?

                          " ; "" ; "" ; curValue


                      ] ;

                      Evaluate ( "Let ($$" & curValue & " = " & res & "; \"\" ) " )



                End Loop

                • 5. Re: Summarize recurring fields

                  WOW thanks.


                  I will try the script as soon as I get to my computer.

                  If i get stuck can i disturb you again for another hint?