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

    Summarize recurring fields

    Powerbook

      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:

       

      <<$$aaa>>

      <<$$bbb>>

      <<$$ccc>>

      <<$$…….>>

       

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

       

      Is ti possible? How can I do that?

       

      Thank you in advance,

       

      Nico

        • 1. Re: Summarize recurring fields
          erolst

          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
            Powerbook

            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
              Powerbook

              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
                erolst

                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 ) ]

                Loop

                  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
                  Powerbook

                  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?