14 Replies Latest reply on Apr 1, 2017 5:26 PM by philmodjunk

    Sum of summary fields

    LeszekŁuczkanin

      I have a table with all my customers. There are different types of customers. One type is "HOUSE" and second is "PERSON".

       

       

      I have relation between two TO (CUSTOMERS::House ---< CUSTOMERS::Person).

       

      This way I can describe persons living in different houses.

       

      I have a summary field which counts how many person lives in every house.

       

      I have a report which is sorted by "CITY" and shows HOUSES and how many PERSON lives in every house. I would like to improve it and have a summary of persons in every city, but it needs to sort of summary of summary fields. How to set it up?

        • 1. Re: Sum of summary fields
          philmodjunk

          CUSTOMERS::House ---< CUSTOMERS::Person

           

          Is not possible in a FileMaker Relationships graph. I think that you have a self join and it looks more like this:

           

          CUSTOMERS::House ---< CUSTOMERS 2::Person

           

          At least some part of the table occurrence name has to be different for the second occurrence of Customers. This is important, because this looks like a case where you have based the report layout on customers when it should really be based on Customers 2. Either that, or you are referring to the summary field via the "other" table occurrence when you should not be.

           

          A summary report should be possible where you group all customers from the same house into a group under a sub summary part. Put the summary field in the sub summary part and get the total for that house. Put the same field into a footer, header or grand summary part and get a total over all records in the found set.

          • 2. Re: Sum of summary fields
            LeszekŁuczkanin

            It is my mistake: I have 2 different TO of CUSTOMERS table:

             

            CUSTOMERS_house ---< CUSTOMERS_person

             

            It is a self join table.

             

            I have a report based on CUSTOMERS_house with a portal from CUSTOMERS_person. I have two categories (gender, employed) and a field which sum this categories. Every house has a city and country described, persons don't have this information, there is only connection between house and person so I know how many persons live in the house.

             

             

            SUMMARY PART - COUNTRY (grouped when sorted by country

            BODY PART - only houses filtered with a script, portal from CUSTOMERS_person and two summary fields (count of gender, count of employed). This is how I know how many men/women and employes is in every house. But when I move this summary fields to summary part, it doesn't work...

            • 3. Re: Sum of summary fields
              philmodjunk

              What I am recommending would not use any portal on the report table.

               

              Base the layout only on one of these two occurrences. Use only fields from this one occurrence. You can then group records with a sub summary part and put summary fields in the sub summary part to show sub totals for the records in that group. Put the summary field into a grand summary, header or footer to get a total over all the records in your report.

              • 4. Re: Sum of summary fields
                LeszekŁuczkanin

                It only works with portal and shows correct numbers for each house / gender. When I made a report based on 1 table, there is "0" in every summary field

                • 5. Re: Sum of summary fields
                  philmodjunk

                  Then those fields are not summary fields.

                  • 6. Re: Sum of summary fields
                    LeszekŁuczkanin

                    Those are summary fields (Count of name).

                     

                    Idea is to keep track of persons living in every house. When person moves from one to another I just change this self join relation and it is enough. That is why I don't have to change address for every PERSON, because they are using address of HOUSE.

                     

                    This report with portal works good. It shows who lives in house and counts PERSON and GENDER. But it doesn't make summary

                    • 7. Re: Sum of summary fields
                      philmodjunk

                      Then something else isn't right. What I am describing is a simple standard summary report with sub summary parts and records sorted to group them into sub summary groups. This is something used in countless FileMaker solutions.

                       

                      The one value that you won't get in a summary field in that context is zero unless the field being counted is empty.

                      • 8. Re: Sum of summary fields
                        LeszekŁuczkanin

                        All fields are filled up. In another place of my data base I've had a similar situation, but it is not self join relationship. I was unable to sum summary fields so I've added a calculation filed (c_number person = TABLE 2::s_sum field) and then I summarized this calculation field.

                         

                        In this case this doesn't work.

                        • 9. Re: Sum of summary fields
                          philmodjunk

                          No self join or any other type of relationship is used in what I am suggesting. The fields and layout should all refer to the same table occurrence.

                           

                          To repeat there is no way that what you describe would put a zero in that summary field.

                           

                          Try this:

                          Put your layout into layout mode, capture a screenshot of it and post that picture here. Make sure that field names are visible, not dample data.

                          • 10. Re: Sum of summary fields
                            philmodjunk

                            Here's why a zero is not possible for such use of Summary if "counting" a field that is not empty for any of your records in the found set:

                             

                            Summary, when from the same table occurrence as the layout, counts the records in your found set. Since you must have at least one record in your found set or the layout would be blank, there has to be at least one record to "count". This is also true of sub summary totals from the same field. If the count is zero, there are no records in the sub summary group and the group thus does not exist. If you get a sub summary part appearing then there is at least one record and thus the count has to be at least 1.

                             

                            Thus, something is not quite right in your set up. Compare your report set up to this demo file. Note that there are no relationships of any kind in this file as this reporting method does not need them.

                            • 11. Re: Sum of summary fields
                              LeszekŁuczkanin

                              I didn' explained it clearly, my mistake.

                               

                              My table look like that:

                              Zrzut ekranu 2017-04-01 o 23.11.26.png

                               

                              This is why I use self join relationship to describe how many person live in every building.

                              First TO is CUSTOMER_BUILDING and second is CUSTOMER_PERSON.

                              My report is sorted

                              COUNTRY

                              CITY

                              Customer_Building with portal from customer_Person

                              • 12. Re: Sum of summary fields
                                philmodjunk

                                This data really should be in two different tables linked in a relationship instead of a single table linked in a self join. Different records here represent completely different entities. The building records should be in one table and people records in the other.

                                 

                                You can, however, still set up the summary report if you set it up based on Customer_Person with a find performed that omits all building records. (perform a find for "person" in the type field.)

                                 

                                Your summary field will then count records correctly in each sub summary part.

                                 

                                You'll be able to sort records by country and by city by referring to those fields from the Customer_Building table occurrences and thus still be able to group records under sub summary parts with subtotals in those sub summary parts.

                                 

                                But I'd move that data into separate tables at the first opportunity if I were you.

                                • 13. Re: Sum of summary fields
                                  LeszekŁuczkanin

                                  I'm still beginner with this building databases stuff. Main objective for my database is different and it was correct to have all that customers together in one table. When my database grow up I find it useful to have this self join relationship. I use it only twice a year, but it makes life easier. If I change my existing table to two smaller I probably will have to change all my database and this is a lot of work.

                                  • 14. Re: Sum of summary fields
                                    philmodjunk

                                    But why would a building be a customer?

                                     

                                    moving the building records into one table while keeping the people should ultimately simplify your solution.

                                     

                                    Yes, I can imagine that this could take a bit of work. That's why I said "as soon as possible", not immediately.

                                     

                                    In the mean time, my previous post tells you how to get this report from the current set up.