5 Replies Latest reply on Dec 25, 2012 12:13 AM by BeachedWhale

    Grouping Product Type and Sorting

    BeachedWhale

      Title

      Grouping Product Type and Sorting

      Post

           I need to group by Product Type firstly, and then subtotal their cost of their orders. So each product has a subtotaled cost. This worked fine before because then it was sorted by ascending Product Type Number. However now I need to sort by descending subtotaled cost amount.

           When I create the sort,  I just sort by this subtotaled cost amount but it keeps splitting up subtotals for the same Product Type. Is there any way I can group by Product Type FIRST and then sort by subtotaled cost?

        • 1. Re: Grouping Product Type and Sorting
          philmodjunk

               Yes. Open up the sort records dialog and look at the options near the bottom of the dialog. There is a "reorder" option that will produce the results that you need. Keep in mind that this is essentially a second sort based on summary subtotals--which don't have an "index" and this this will take significantly more time to sort than your current sort.

               I don't know how much speed improvment there would be, but there are also ways to get these subtotals from a layout based on a table where you have one record for each product type. The challenge to using this approach is incorporating criteria such as a date range when you need subtotals based on more than just the type of product. This can be done with global match fields in the relationship or via filtered portals that reference the same global fields.

          • 2. Re: Grouping Product Type and Sorting
            BeachedWhale

                 I did the reorder option and had it "reorder by summary field". However, it doesn't summarize by Product Type anymore. It will give me the 2 or more subtotals for one Product Type and then sort by these incorrect subtotals. I also tried sorting first by ascending Product Type, so that it groups by Product Type first and then reorders by summary field. This doesn't work either because it still divides the same Product Type and gives me several subtotals for the same Product Type and then sorts by these subtotals for each Product Type.

                 What I want is to get the subtotals by Product Type and then sort by these subtotals from greatest to least.

                  

            • 3. Re: Grouping Product Type and Sorting
              philmodjunk

                   That is what the reorder option should do. You should still get the group based subtotals, but with the groups ordered in ascending or descending order.

              • 4. Re: Grouping Product Type and Sorting
                philmodjunk

                     And Here's a demo copy that you can download to compare to yours: https://dl.dropbox.com/u/78737945/ReorderedSubTotalsDemo.fmp12

                      

                • 5. Re: Grouping Product Type and Sorting
                  BeachedWhale

                       Thank you! That worked perfectly, I didn't know I could reorder Product Type by the subtotal, I was trying to do a two stop sort, and sorting by btotal while reordering by subtotal, which I now realize doesn't make sense.