6 Replies Latest reply on Nov 26, 2013 1:54 PM by philmodjunk

    Summarize

    IlseVerhaert

      Title

      Summarize

      Post

           If you have the following table:
            
            Project     City                   Invoice    #trainings

                A           Seattle             $ 500            1

                A           Washington    $300             2

                A           New York         $100             2

                B          Texas                $300            3

                B          Orleans             $250            1

           I am mixing up everything; and the aswer is probably very easy... but what is the function again to get a report with the weighted average invoice of per type project and the sum of the trainings executed per project? (not the real case but same reasoning, that is why I use a simple example). The city name is thus not relevant anymore, only the type of project.
       
       Thanks!!

            

            

            

            

            

            

            

        • 1. Re: Summarize
          philmodjunk

               It's not necessarily a function, but a field of type summary and a weighted average is one of the options that you can specify as well as the option to compute a total.

          • 2. Re: Summarize
            IlseVerhaert
             Should you then create a new layout or table to display only the summary fields?
            • 3. Re: Summarize
              IlseVerhaert
              I have 100 citys, distributed over 8 type of projects. I achieved in getting the summary fields but if I design a new layout (report layout) - I select 'project type' as category - and continuously add all the subtotals, I still have 100 records, of which only the subtotals and project type are displayed. However, I only want to end up with 8 records (1 per project type). Could you help me out please? Thanks!
              • 4. Re: Summarize
                philmodjunk

                     The summary fields must be defined in the same table as the data that they summarize.

                     To get one row (not a record) of data for each project type, remove the body layout part from your layout. Add a sub summary layout part "when sorted by" your project type and put your summary field(s) in this layout part. Then sort your records by project type. Please note that sub summary layout parts are not visible unless the records are sorted by the same field as that specified for the sub summary layout part.

                     You may find this tutorial on summary reports of interest: Creating Filemaker Pro summary reports--Tutorial

                • 5. Re: Summarize
                  IlseVerhaert
                   Alright, Thank you! Is there also a way to summarize fields from related tables?   If I select 'leading subtotals' from such a field, I am not able to select anything.
                  • 6. Re: Summarize
                    philmodjunk

                         When you summarize data from related table, the rules change as to what records are summarized. It's all a matter of "context".

                         When you use a summary field on a layout based on the same table as where you have defined the summary, the total is based on the found set or a group within the found set produced by a sort.

                         When you refer to that same summary field from the context of a related table, such as putting it on a layout based on a related table, the summary computed is based on the related set of records instead of a found set.

                         And you can put that summary inside a one row filtered portal and then the total is based on the filtered set of related records...