5 Replies Latest reply on Dec 1, 2009 11:44 AM by comment_1

    Page Breaks and Sub-summary not based on first sort criterion

    Stella

      Title

      Page Breaks and Sub-summary not based on first sort criterion

      Post

      FMP Version 6.0v4 (for reasons out of our control)

      Mac OS 10.5.8

       

      I'm trying to have page breaks and a sub-summary part, based on a field that cannot be my first sort criterion.

       

      My first sort criterion is a field of "sequential numbers" (so that the sequence of records appears in the order we want).

       

      But I'd like the pages to break based on a field of "categories"(sequential numbers group all by category, but not alphabetically).

       

      A sort by "sequential numbers" with a page break, yields only one record per page. A sort by "categories" yields the desired page breaks, but causes the sequencing of records to be alphabetical by category, which we don't want.

       

      First time in the forum...will be grateful for any sugguestions: there must be a work-around that I'm not recognizing.

       

       

       

        • 1. Re: Page Breaks and Sub-summary not based on first sort criterion
          comment_1
             I must be missing something in your description, because your requirements seem to conflict: if you want records to be grouped by category, you MUST sort by category first. Then, if you want them to be sorted by another field WITHIN the category, use that as the second field in the sort order.
          • 2. Re: Page Breaks and Sub-summary not based on first sort criterion
            philmodjunk
              

            Is this what you want?

             

            category A

              rec 3

              rec 4

              rec 5

              rec 7

            category B

              rec 1

              rec 6

              rec 8

            category c

              rec 9

             

            If so, first sort by category then by "sequential number". If this is not what you want, perhaps you could post an example?

            • 3. Re: Page Breaks and Sub-summary not based on first sort criterion
              Stella
                

              An brilliant office mate just solved this problem.

               

              We created a new field which assigns each category a number in the order we want the categorie to appear (non-alphabetical). Each record in a category has the category's number in this new field.

               

              Now I can sort by category number, then sequence number. The page break and sub-summary is set for category number field, a trick to get the desired flow of categories, while keeping the deired record sequencing.

               

              !!

               

               

              • 4. Re: Page Breaks and Sub-summary not based on first sort criterion
                Stella
                  

                Just to clarify, if anyone is still puzzling over my description of the problem or finds this post in the future, searching solutions for the same problem.

                 

                The problem was solved (see previous post) by adding a field to represent categories numerically.

                 

                I wanted to have a sub summary with page breaks based on my second sort criterion (sub summary parts can only be based on the first sort criterion), however FMP only allows sub summary parts based on first sort criterion.

                 

                "Category" could not be my first sort criterion because I did not want them to appear in alphabetical order. My first sort criterion is record sequence numbers, to make the records fall in desired order.

                 

                The solution should have occured to me, because it's the same concept used to get the records to fall in desired (non-alphabetical) order: assign sequential numbers to each record (our use of decimal points is because records got inserted and deleted, so whole running numbers got modified with decimals).

                 

                Each category was assigned a number, i.e. in the example below, all records in C category assigned "001." All records in A Category assigned "002" and all in W Category assigned "003."

                 

                That way my first sort criterion (category numbers) puts the categories in desired order and drives the sub summary, with its page break, based on category. Second sort criterion drives the order of the records.

                 

                Sounds more complicated than it is. Basically, if you want non-alphabetical sorting, think about using numbers, either sequential numbers or same numbers (for a like group).

                 

                C category

                      record 01.02

                      record 01.021

                      record 01.022

                 

                A category

                     record 01.03

                     record 02

                 

                W category

                     record 02.02

                     record 02.05

                 

                Q category

                     record 02.06

                     record 03

                     record 04.01

                 

                MANY THANKS TO ALL WHO POSTED. FORUMS ROCK!

                 

                • 5. Re: Page Breaks and Sub-summary not based on first sort criterion
                  comment_1
                    

                  Stella wrote:
                  all records in C category assigned "001."

                  A simple way to do this would be to create a separate table for Categories, with a field for the category's rank number. Then either sort by the related Category::Rank field, or use a calculation field in the data table to get the related rank number from the Categories table.

                   

                  Another option is to define a custom value list of the categories, and use it to sort the records.

                   

                   

                   


                  Stella wrote:
                  sub summary parts can only be based on the first sort criterion

                  No, that is not true. A sub-summary part can be based on any field. A layout can even have several sub-summary parts, based on different fields. If the specified field is anywhere in the sort order, the sub-summary part will be shown - thus you can re-order the sub-summaries by changing the sort order.