1 2 3 Previous Next 37 Replies Latest reply on Dec 7, 2016 4:56 AM by fmpdude

    Sum Calculation Issue

    sansae

      Hi all,

       

      I've been struggling to figure this one out. I've searched the forums and I've only come across posts that talk about sum fields that are blank, but not sum fields that return a value (but an incorrect one).

       

      Would anyone like to help me figure this out?

      Here is the breakdown.

       

      Summary: I have a list of records sorted by zipcode. Within each zipcode are records with a field called "turnaround" whose value is a number.

       

      Goal: I would like to add all the turnarounds for each zipcode

       

      Problem: Summing the turnarounds (i.e. sum (turnarounds) ) gives me only the last turnaround as my result. This is the case for each zipcode.

       

      Example:

      Zip: 00725

       

           id_Project: 1

           turnaround: 20

           id_Project: 2

           turnaround: 22

           turnaround_sum: 22 (what I expect is 42)

       

      Zip: 00726

       

           id_Project: 5

           turnaround: 15

           id_Project: 6

           turnaround: 10

           turnaround_sum: 10 (what I expect is 25)

       

      Question: Does this have anything to do with my "perspective"? That is, where I'm "sitting" and what I'm "seeing" or not seeing?

       

      Notes (hope it's not too confusing; I'm working on a humongous db and I just started learning fm 4 weeks ago while on the job):

      My layout is based on a table called Projects (source table is Projects), but the layout contains fields that are from a TO called Services|Services (source table is Services), which is a duplicate of and is related to another TO called Services|Projects (source table is Services). In other words, (going from left to right), Services|Projects extends Projects, and Services|Services extends Services|Projects.

       

      If anything, I'd love it if any of you could share some common reasons as to why a sum calc would only return the last record value?

       

      Thanks in advance. Please let me know if you need any more info.

        • 1. Re: Sum Calculation Issue
          erolst

          Sum() doesn't work that way. It lets you sum several fields from the current record, or related fields.

           

          That's why on the last record of a sub-group (or in a trailing summary, which uses values from that last record) the result is identical with the referenced field itself.

           

          What *does* work like you expected are fields of type summary, which summarize the found set. To see sub-totals, you can use sub-summary parts:

           

          The simplest "native" method is:

           

          - create a layout

          - add a sub-summary part, and specify zip as the break field

          - if you only want to see summarized results for your records, you don't need the body part

          - create a summary field of type 'total', and select turnaround as the field to summarize

          - place the summary field into the sub-summary part (and the zip field, so you know what it refers to)

          - find the desired subset of records to summarize (or show all)

          - sort by zip

           

          Make sure the layout is displayed in List view. For a layout that's only ever going to be used that way :

          - switch to Layout mode

          - open Layouts > Layout Setup

          - go to the Views tab

          - uncheck all views except List

          2 of 2 people found this helpful
          • 2. Re: Sum Calculation Issue
            BruceRobertson

            Here is a short version of some of what erolst has mentioned.

            Sum( ) is a function and can be used within calculations; including a calculated field.

             

            FileMaker Pro 15 Help

             

            A summary field is an entirely different thing! It seems to me that this is what you want. But it will be important to understand both, and to select the right one for the right circumstance.

             

            FileMaker Pro 15 Help

            1 of 1 people found this helpful
            • 3. Re: Sum Calculation Issue
              sansae

              Hello erolst,

              Thank you so much for the response.

              The point about how sum() works is helpful and I definitely experienced a "doh!" moment.

               

              The steps you listed under the native method are the same steps I already took when creating my layout, with exception to the "summary" field. The field I currently have is using the sum() function (i.e. sum (turnaround) ). Since you've helped me understand that sum() doesn't work that way, I took your advice and changed the type for my "turnaround_sum" field from "calculation" to "summary". I selected turnaround as the field to summarize. I have my records sorted by zipcode. Everything looks/works nicely as far as I can tell, but...

               

              Result: same thing.

               

              Note1: I have a leading and trailing sub-summary and both are categorized by zip. My reason having two is that for leading, I have a merged field called "Zip" that displays the zipcode, and for trailing, that's where I put my turnaround sum. I also have a body that contains id_Project, start_date, due_date, and turnaround.

               

              Note2: After changing my field type from calc to summary, when placing the turnaround_sum field in the trailing sub-summary, I get the last turnaround as result; when placing turnaround in the leading sub-sum, I get the first. Frustratingly insteresting hahaha

              • 4. Re: Sum Calculation Issue
                sansae

                Thank you for the response Bruce.

                 

                Initially, when I first started on my layout, I created a turnaround field with type, summary. But the result was an empty field. That was when I switched the type to calc. I recalled in my school db (a very very simple "practice" db that I was working on as part of my training), I used sum() on "units" for each student and it worked perfectly. In my db, there were 5 students, each taking a certain amount of units. I used the sum function on units and it worked.

                 

                Could my issue be a result of the "context" (thanks for helping me with the terminology erolst), or where I'm "sitting". Perhaps from where I'm currently sitting, I can't see every turnaround within a zipcode? I bet that's the real problem.

                 

                As well, could be an issue with the way I'm setting up the relationships between my table occurrences.

                • 5. Re: Sum Calculation Issue
                  BruceRobertson

                  Here's a simple example file, see attached.

                  summary report.png

                  1 of 1 people found this helpful
                  • 6. Re: Sum Calculation Issue
                    erolst

                    Beaten to the punch

                     

                    Screen Shot 2016-12-06 at 20.21.42.png

                    1 of 1 people found this helpful
                    • 7. Re: Sum Calculation Issue
                      sansae

                      Hi Bruce,

                       

                      That example, indeed, is very simple. All the fields are on one table. That I can definitely do, haha.

                       

                      But I still have the same issue.

                      I looked over my layout once more and I don't see what the issue is. My turnaround_sum field is currently a summary type, and the field it is summarizing is the turnaround field. Additionally, the turnaround field and the turnaround_sum field are both from the same TO. My layout is sorted nicely by zip.

                       

                      THANK YOU BOTH FOR TAKING THE TIME TO HELP. That's super cool.

                      • 8. Re: Sum Calculation Issue
                        erolst

                        If you want to summarise the records in the current table, use a summary field; e.g. if your Invoice layout is based on the LineItem table.

                         

                        But while you're on the layout of the Invoice record, you'd use Sum() to total the related LineItem records. (Actually, you could use the related summary field.)

                         

                        Using your student example: you could use Sum() to total the related exam results of the current student record. But if you wanted to average the results for all students in the last semester, you would perform a search by date in the exams table and use an Average: result summary field.

                         

                        Note that FileMaker usually has several similar approaches, so you could do all that a different way.

                        1 of 1 people found this helpful
                        • 9. Re: Sum Calculation Issue
                          BruceRobertson

                          You're leaving out some information about what table the current layout is based on.

                          You have mentioned something about relationships; but zero meaningful detail.

                          Nothing in what you have described so far would involve any relationships or any other tables.

                          1 of 1 people found this helpful
                          • 10. Re: Sum Calculation Issue
                            sansae

                            Your response here brings me back to the note that I made in my initial post, and I'll repeat it here (because perhaps that's what's tripping me up?):

                             

                            My layout is based on a table called Projects. BUT...

                            my turnaround and turnaround_sum fields are both based on a TO called Services. ALSO... my zip field is based on a completely separate table called Sites, which is related to the Projects table via an id.

                             

                            As you can see, I've got fields from all over the place. Is that the issue here?

                             

                            I would think that as long as I have the turnaround numbers on my layout, and sorted by a zipcode, that a simple sub-summary part containing a turnaround_sum field should do the trick in summing up the turnarounds for each zipcode.

                            • 11. Re: Sum Calculation Issue
                              BruceRobertson

                              "I would think that as long as I have the turnaround numbers on my layout..."

                               

                              Hm, let me paraphrase.

                              "I know context is important but if I just throw fields from anywhere onto the layout, it should know what I mean and it should just work. Right?"

                               

                              Well; no.

                              1 of 1 people found this helpful
                              • 12. Re: Sum Calculation Issue
                                sansae

                                hahaha...

                                thanks for the laugh [insert smiley face from ear to ear]

                                • 13. Re: Sum Calculation Issue
                                  BruceRobertson

                                  Can you upload your file? Or can you create a simplified example of your file and upload that?

                                  1 of 1 people found this helpful
                                  • 14. Re: Sum Calculation Issue
                                    sansae

                                    Along those same lines, me thinks the issue is that my layout is working with too many different tables.

                                    To summarize:

                                    my layout is based on Projects

                                    my zip field is from "Sites"

                                    and my turnaround & turnaround_sum fields are from Services

                                     

                                    I think that's the issue

                                    1 2 3 Previous Next