6 Replies Latest reply on Sep 5, 2013 5:25 PM by philmodjunk

    Grouping, Totaling

    janslort

      Title

      Grouping, Totaling & summarizing

      Post

           I have a problem coming up with a scheme to present and develop a report for a client.  He want's to have a report that shows the result of his sales in 4 main sements of his bakery business.  He wants to be able to expand each of the 4 main segments to itemize specific parts within each segment (5 to 8 items from a pick-list) so if he calls out 2 items from a list, the remaining 3 to 6 items show as a remeaining total of items that are not highlighted in detail. Can anyone suggest a framework or somewhere i can read how to do this?

           More details are shown below.

           Thanks

           Jan Slort

            

            

            

           Romelo's Bakery

            

           Baked Goods

                    Cakes

                        <list a kind of cake from a pick list>                                                                                         312.00

                        <list another type of cake from the same pick list>                                                                  219.00

                        <add as many lines as there are kinds of cakes in the pick list if I want>                                190.00

                        <remaining Sum of the cakes not listed above>                                                                    1,379.00

                         Total Cakes                                                                                                                          $2,100.00

            

                     Cookies

                           <list a kind of cookie from a pick list>                                                                                      720.00

                           <list another kind of cookie from the cookie pick list>                                                               245.0

                          <Add as many lines as there are other cookies on the pick list if I want>                                 175.00

                          <remaining sum of cookies not listed above>                                                                            563.00

                          Total Cookies                                                                                                                            1,703.00

            

                      Other

                              <Add as many lines as there are kinds of baked goods on the pick list if I want>

                              <list other kinds of baked goods from their pick list>

                              <Add other baked goods from the chosen pick list>

                              <remaining sum of baked goods not listed above>

                               Total other baked goods                                                                                                           6,530.00

            

                      <Remaining sum of kinds of baked goods not listed above>                                                           8,540.00

            

           Total Baked goods                                                                                                                                      $18,873.00

            

           Total Catering                                                                                                                                                14,900.00

            

           Total Beverages                                                                                                                                               7.300.00

           Grand Total Sales                                                                                                                                        $41,073.00

            

           Remelo wants to know the details of his sales, so he needs to have reports available of each item. His banker doesn't. Remelo wants to show the total of baked goods, and catering, and drinks for a grand sum of his sales for the week to his banker while allowing him to (drill down) expand any category to reveal the detail items for his own reports, enabling him to change the detail of which items as he chooses to highlight each week.

            

           Sample Pick lists:

      Baked goods Cakes

           Cakes Angel food

           Cookies Devil food

           Bread Sheet

           Pastries Wedding towers

           Donuts Cup Cakes

        • 1. Re: Grouping, Totaling & summarizing
          philmodjunk

               I think I see a way to do this. But first, what skills you you already have when it comes to generating reports in FileMaker? Do you know how to set up a summary report with sub summary layout parts?

               And how is the data structured from which this report must be generatled? What tables/records/fields store the data from which this report must come?

          • 2. Re: Grouping, Totaling & summarizing
            janslort

                 PHIL;

                 i have good skills in sub-summary parts design, including eliminating t he Body part and showing the sub summary parts and grand total part. The data is in separate FM fields in a file derived from invoices issued to customers and cash sales teports.  Lot's of good separate records. 

                 Jan

                 btw thanks for the info on digits starting field names. I'm importing his field names and mapping them to my own designed fields that are not using digits to start a name. 

            • 3. Re: Grouping, Totaling & summarizing
              philmodjunk

                   What I'm thinking here, in very general, simplified terms is to try setting up a system where, when the user selects a sub category such as a type of cake, all records for this subcategory gain a common value in a field set up as the sub summary layout part's break field via either a calculation or a script. And records not part of a selected sub category all, regardless of subcategory, get a different common value to produce the "other" group.

                   Let's say your records are setup up with a category field: Cake, Cookie, Other and a sub category field: Type of Cookie, Type of Cake, etc.

                   If you then set something up so that the user's selected sub categories are listed in a global filed separated by returns, then the following calculation might produce the needed break field:

                   If ( Not IsEmpty (FIlterValues ( GlobalSubCategoryLIst ; SubCategory ) ) SubCategory ; "zzzzOther" )

                   There are a number of ways to build that global list field, from using List to list the value from a set of related records to a script to formatting the global field with checkboxes where each checkbox value is a subcategory name.

              • 4. Re: Grouping, Totaling & summarizing
                janslort

                     Phil:

                     i'm going to have to work this through completely to realy understand it but it looks like it will work thank you. 

                     Jan

                • 5. Re: Grouping, Totaling & summarizing
                  janslort

                       While I'm at it, is it possible to use "set field" to place data from one sub summary total field in one file into another field in another file?

                       jan

                  • 6. Re: Grouping, Totaling & summarizing
                    philmodjunk

                         Take a look at the getSummary function. The "break" field you specify as a parameter in this function is the same "sorted by" field you'd specify for a sub summary part to get the sub total.