4 Replies Latest reply on Aug 4, 2009 11:16 AM by ninja

    Newbie summary field question

    paynetc

      Title

      Newbie summary field question

      Post

      I'm brand-spanking new to FileMaker, so I'm sure this question is probably ridiculously easy to answer.  But I just can't figure out how to do it.

       

      I want a field that adds values of a field together when the date is equal across records, no matter what the sort order is.

       

      I have a date field and the field I want to sum over dates.  When I create a summary field, it seems that the records must be sorted by date in order for the summary field to work the way I want.  That is, clicking Restart summary for each sorted group and choosing the date gets me what I want - but only when the records are sorted by date.  When the records are unsorted, the summary field does not provide the desired result.

       

       

       

        • 1. Re: Newbie summary field question
          philmodjunk
            

          It sounds like you've placed your summary field in a Sub-Summary part. The part will only be visible if you sort the found set by the part's designated sort field and this would explain what you describe.

           

          place the field in a trailing grand summary part, a header or a footer and you should see the same "sum of the entire found set" regardless of sort order.

          • 2. Re: Newbie summary field question
            paynetc
              

            That gives me a grand total across all dates but does not give me a total for each date.  Perhaps some sample data would help me explain what I want:

             

            Date Value

            8/3 3

            8/3 4

            8/4 9

            8/4 3

             

            I want the summary field to show 7 (i.e., 3 + 4) any time I'm in Form view on a record that has a date of 8/3, and 12 (i.e., 9 + 3) any time I'm in form view on a record that has a date of 8/4.

            • 3. Re: Newbie summary field question
              philmodjunk
                

              Then you have to sort your records by date. The records must be grouped in clusters of records of all the same date or your summary sub-totals will be meaningless.


              What problem are you trying to solve by not having the records sorted by date?

              • 4. Re: Newbie summary field question
                ninja
                  

                Howdy paynetc,

                 

                Ya said the magic word..."Form View"...so it isn't a subsummary report.

                 

                One way to do this would be:

                 

                Self-join to another TO (Self2) by the date value

                Have an unstored calculation field (not summary field) in your table that equals Sum(Self2::Value)

                 

                It works in my brain, and I'm pretty sure I've used it before succesfully...but the brain is foggy today...

                 

                Does this work for you?

                 

                Edit: doing it this way might be sort of confusing for other users of the Dbase who don't understand where the summation comes from...think about how others would percieve the number and possibly consider a portal so that they know where the number comes from...