6 Replies Latest reply on Jun 6, 2012 6:04 AM by DavePres

    Filtering Sub-summaries

    DavePres

      I have a report on incidents. There are about 100 types of incident. I would like to be able to run the report and just get the top 20 incidents which I then graph. The report is in order - top incident first. I can do it manually by sorting by the report and omitting the records I don't want. I was thinking of a looping script but thought there may be an easier way.

      Thanks in advance, David

        • 1. Re: Filtering Sub-summaries
          erolst

          For constraining the found set to the first 20 records, this should work faster:

           

          Sort Records [ Restore; No dialog ]

          Go to Record/Request/Page [No dialog ; 21 ]

          Omit Multiple Records [ No dialog ; 1000000 ]

           

          Note that when using Omit Multiple Records manually this way, FileMaker will complain

          that only x records can be omitted; in a script, it just goes ahead and omits all remaining records.

          • 2. Re: Filtering Sub-summaries
            DavePres

            Hi Erolst, Thanks for that. I usually have, say 15,000 records grouped into about 100 incidents.

            On the screen i just see 100 lines, a different incident on each. If I click on the 20th most common incident, it may be record 11,000. Or it could be record 6000 depending on the found set.

            Currently I would click at that record and do omit 9999999 as you said.

            Your method would work if I knew how to script what the record number was on any particular line on the sub summary report. I would like to start script and just input a number (say 20) and output report with top 20.

            Thanks, David

            • 3. Re: Filtering Sub-summaries
              erolst

              Oops, sorry, then I guess I have misunderstand your question. You say “type of incidents” and “incidents”, and also I assumed that the incidents are already sorted in “top order”, whatever that may be. But now I'm bit unsure about your data structure. Are you looking for “top 20 incidents”, “top 20 types of incidents“, or “all incidents belonging to the top 20 types of incidents”?

               

              Depending on the answer, and your data model, the whole process might be vastly simplified. Maybe you could elaborate?

              • 4. Re: Filtering Sub-summaries
                DavePres

                Hi Erolst

                Sorry if I wasn't clear. In this case, an incident is a medical incident described as one word eg asthma, angina, syncope or whatever.

                We track thousands of medical incidents and report on them.

                The data is very simple in this part of an overal solution we have.

                We sort by disease and there is a sub summary with disease and number of times it was reported

                eg

                Asthma      400

                Angina       200

                Syncope    150

                etc and we have about 100 types of incident (disease). The report is quite long and often we just want to report on the top 20 incidents. and just want an easy way to find and omit the other 80.

                 

                Thanks

                • 5. Re: Filtering Sub-summaries
                  comment

                  Roughly:

                   

                  # FIND THE RECORDS YOU WANT TO INCLUDE IN THE REPORT

                  # SORT BY: Disease + REORDER BASED ON: sCount

                  Go to Record [ First ]

                  Loop

                  Set Variable [ $i ; $i + 1 ]

                  Exit Loop If [ $i > 20 ]

                  Go to Record [ Get (RecordNumber) + GetSummary ( sCount ; Disease ) ]

                  End Loop

                  Omit Multiple Records [ Get (FoundCount) - Get (RecordNumber) + 1 ]

                   

                  where sCount is the summary field that counts the records.

                  • 6. Re: Filtering Sub-summaries
                    DavePres

                    Thanks Michael, I think a looping script is the way to go. Thanks