5 Replies Latest reply on Oct 23, 2009 9:17 AM by philmodjunk

    Sorting Records in Subsummary Report

    lotus

      Title

      Sorting Records in Subsummary Report

      Post

      Product: FileMaker Pro 9

      Platform: Windows XP Professional (5.1)

       

      I have a subsummary report for a database table with the following fields: Country, Certified, Not Certified, Percentage Certified. How do I sort by Percentage Certified? In Excel for example you could click on the column header and sorting would be possible (after expanding into other columns). Another question: does Filemaker 10 have dynamic sorting for such an application without requiring scripts?

       

      I would appreciate an answer as soon as possible. Thank you for your support.

        • 1. Re: Sorting Records in Subsummary Report
          philmodjunk
            

          "How do I sort by Percentage Certified?"

           

          Select Sort from the records menu. Select this field and click "move" to add it to the sort order list shown on the right side of the sort dialog. Since this is a sub-summary report, be careful to keep any fields that are "sort by" fields for your Sub-Summary layout parts.

           

          "does Filemaker 10 have dynamic sorting for such an application without requiring scripts?"

           

          Don't know what you mean by "Dynamic Sorting". In FMP 10, once you sort a found set, filemaker will enforce that order until you either unsort the records or specify a different sort order. This means that if you edit a field that is part of the current sort order, the record may "jump" to a different postition in the found set when you commit the record. Many developers have complained long and hard about this as there is no way to turn this feature off and work arounds are very cumbersome. Any time you want to change the sort order without using a script, follow the steps I posted at the beginning.

          • 2. Re: Sorting Records in Subsummary Report
            lotus
              

            "How do I sort by Percentage Certified?"

             

            I did what was suggested below - moved "Total Percentage certified by country" and the sorting hung there with the message: "2267 records remaining to sort".  Could you please review and let me know what is wrong? Thanks.

            • 3. Re: Sorting Records in Subsummary Report
              philmodjunk
                

              That indicates that you are sorting on an unindexed/unstored field. Filemaker WILL sort the data and report back but it will take a while. Going by the field name, it's likely that this field cannot be stored/indexed. Summary fields or calculation fields that refer to data in related tables or a global field, for example cannot be stored/indexed in Filemaker.

               

              If this is not the case, you can double check the field's storage options in Manage | Database | Fields and try to turn on indexing for the field. (The sort you attempted, if you let it run to completion, will turn on indexing for you in some cases. If so, then the next time you attempt to sort on the field, it'll go a lot faster.)

              • 4. Re: Sorting Records in Subsummary Report
                lotus
                   Thanks. Maybe I did not explain clearly. I was unable to turn on indexing because it says " .. referencing summary field in calculation". Here is the way the calculation is done for Certified (C) and Not Certified (NC). C= count (certification date). NC = count (last name) - count (certification date). Total percentage certified = 100*{C/(NC+C)}. This field is the one that needs to be sorted and Filemaker perhaps needs a script to do it. My question is: What script is needed to sort by this field? Thanks again for your continued prompt responses.
                • 5. Re: Sorting Records in Subsummary Report
                  philmodjunk
                    

                  I don't know if there is a solution short of taking a break and checking back later after filemaker finishes sorting on that field.

                   

                  Just to see if there is a work around try posting an outline of your report showing the location of this field (is it in a sub-summary part?) to show what results you need from sorting on this field. I'm not sure that sorting on this field will get you what you expect. There may be a different field you can sort on to see the results you need.