6 Replies Latest reply on Mar 11, 2011 9:13 AM by brett_

    Summary field based on conditions

    brett_

      Title

      Summary field based on conditions

      Post

      I've created a summary field that does an average of a numeric field.  However, I'd like to get an average of only negative values and another of positive values, resulting in two summary fields.  How do I get a summary field to calculate averages only for negative values then another field for positive values?   

      Also, is there a way to put these summary fields into the footer and have them display in Browse mode, table view?

        • 1. Re: Summary field based on conditions
          philmodjunk

          Summary fields can be used in Browse mode, just keep in mind that they summarize all the records in your found set when placed in a header or footer.

          Unless you use a filtered portal in FileMaker 11, to get your conditional summaries, you'll need to define two calculation fields such as:

          If ( field < 0 ; field ; "" )

          and

          If ( field > 0 ;field ; "" )

          (And you can leave out the ; "" and get the same results)

          Then you can define two summary fields, each summarizing one of these calculation fields.

          • 2. Re: Summary field based on conditions
            brett_

            Thanks.  I have the summary fields working using calculated fields.  What is the difference in using the portal you mentioned?

            • 3. Re: Summary field based on conditions
              philmodjunk

              It may not apply to your solution, but if you have a relationship such as:

              Master----<Detail   (---< means "one to many" )

              and you define a summary field in the Detail table, you can place a portal to Detail on the Master layout and if you use a filter expression in the portal, the summary field will compute the summary (total, average, count, Standard Devation...) of just the records that meet the conditions of both the relationship and the filter.

              Thus, a filter expression such as: Detail::field < 0

              Would display the average of only the summary of fields in records related to the current Master record where the value in field being averaged is < 0. This can be a single row portal with invisible borders to conceal the presence of a portal if you want.

              • 4. Re: Summary field based on conditions
                brett_

                Thanks.

                So in browse table view mode, there isn't a way to put these summary fields in a header or footer so they are not a column?  Trying to avoid how wide (number of columns) this is getting.

                • 5. Re: Summary field based on conditions
                  philmodjunk

                  Enter layout mode, go to layout setup... and you can get to a place where you can specify that table view can show headers and footers.

                  The other option is to use a List View instead of a table view.

                  • 6. Re: Summary field based on conditions
                    brett_

                    Nice.  Thanks.