13 Replies Latest reply on Nov 19, 2010 1:57 PM by philmodjunk

    Frequency Counts (Revisited?)

    EMH

      Title

      Frequency Counts (Revisited?)

      Post

      I've reviewed a few posts regarding this topic and still can't seem to get a report of the frequency of occurances in my DB. I maintain a large DB of requests for certain restricted drugs. We receive about 2000/month. At the end of the year, I would like to rank the requested agents by frequencty of request.

      So I'd like FMP to provide a table of with drug name (fieldname is DrugID) and number of records for each drug listed in the defined date range (usually FY).  So the report would have to recognize the unique entries in the DrugID field (it is free text) and sort in declining order of frequency.

      Any help would be greatly appreciated. Thanks!

        • 1. Re: Frequency Counts (Revisited?)
          philmodjunk

          A Summary report can do this easily,

          1. Define a summary field that is the "count of" the Drug ID field.
          2. Create a layout based on your drug requests table.
          3. In layout mode, double-click the "body" layout label and change the body to a subsummary when sorted by your DrugID field.
          4. Place your DrugID, the new summary field and any other fields you want (like a description or name field) in this part.
          5. Return to browse mode, select list view and sort your records by DrugID.
          6. To limit your records to a given date range, simply perform a find for all records in the specified date range and then sort your records.


          Using a sub summary part without a body part, drops out the individual rows for each record, leaving just one row of data with your count for each type of drug.

          • 2. Re: Frequency Counts (Revisited?)
            EMH

            This is what I tried to begin with. When I return to browse mode (while viewing as list), the report is empty.  The report contains only the fields:

            DrugID (Text, Indexed)

            Frequency (Summary, =Count of DrugID)

            Body (Part Definition) :  Sub-summary when sorted by DrugID

             

            Any ideas?

             

             

             

             

            • 3. Re: Frequency Counts (Revisited?)
              philmodjunk

              That's not quite what I described. There should not be any body layout part at all. The frequency field should be in a Sub Summary part and the records must be sorted by the same "sorted by" field specified for the subsummary part.

              • 4. Re: Frequency Counts (Revisited?)
                EMH

                Thanks. My bad. It is not labelled body anymore (had to expand it to see that). It is labelled: Sub-summary by DrugID (Leading). Since the report is empty, there is no option for sorting...

                • 5. Re: Frequency Counts (Revisited?)
                  philmodjunk

                  Since the report is empty, there is no option for sorting...

                  Did you select list view for your report? You can sort your records by selecting Sort from the Records Menu.

                  • 6. Re: Frequency Counts (Revisited?)
                    EMH

                    Yes. It is in List View,  but since there are no records, the Sort option in the records menu is dimmed.

                    • 7. Re: Frequency Counts (Revisited?)
                      philmodjunk

                      Well, you have to perform a find to pull up the records you want in your report, then sort them.

                      • 8. Re: Frequency Counts (Revisited?)
                        EMH

                        I really appreciate your help.

                        There are no records when I Show All Records. If I perform a search in another layout and switch to this one, again, an empty report. I added a date field to this layout and still, I cannot select any field in the layout (in Browse mode) in order to perform a search in this layout.  Does the fact that the DrugID field is indexed make any difference?

                         

                        Interestingly, when I view as a report, each Drug ID that appears gives a frequency count equal to the number of records displayed. When viewed as a list, the form is blank.  Does this help?

                        • 9. Re: Frequency Counts (Revisited?)
                          philmodjunk

                          The DrugID field should be indexed and probably has to be given the typical uses of an ID field like this.

                          On what table is your layout based?

                          When you say your do Show All Records and there are no records, are you looking at the blank screen and concluding that there are no records? What do you see just to the right of the Pie Chart in the status area?

                          Your layout needs to refer to the correct table in "show records from" in layout setup... so I am wondering if this is set up correctly.

                          Oh yes, and what version of FileMaker are you using?

                          • 10. Re: Frequency Counts (Revisited?)
                            EMH

                            Version 8.5

                            The layout shows records from the identified database entitled: "FMPNFDBMaster05_09On" (from which my other 10 layouts properly display)

                            I've setup the layout both as standard form and a columnar list/report with the same results.

                            Perhaps the verions are the issue. I have no pie chart or status area in my display.

                            • 11. Re: Frequency Counts (Revisited?)
                              philmodjunk

                              Every copy of Filemaker ever made has a status area. That's the grey rectangle found down the left hand side of your screen in 8.5. In FIlemaker 10 and 11, it runs across the top.

                              You don't have a piechart, but you do have a "book control" and the info just below it will tell you how many records you currently have in your table and found set.

                              Another issue that is contributing to the confusion, but doesn't explain why you can't sort any records, is that this kind of report does not display correctly in Browse mode for versions Prior to FileMaker 10. You have to enter preview mode (and correctly sort your records) or print it to see  it displayed correctly.

                              • 12. Re: Frequency Counts (Revisited?)
                                EMH

                                You are correct, I do have a status area and yes it does have a flip book for changing records (the pie chart reference threw me). Depending on my whether I am showing all records or have entered a date range (on a separate layout) I have had anywhere from 2239 to 37,323 records listed. The preview mode is also blank (but again, it is not possible to sort). Hmmm.

                                I have FMP 10.0 at home (Mac Version). I will take the DB and try to do this with it.

                                • 13. Re: Frequency Counts (Revisited?)
                                  philmodjunk

                                  What's odd is that "not possible to sort". I can't figure out why you don't have that option. Until you sort the records to group them by DrugID, you won't have anything visible in this report.

                                  Sorting them should be as easy as selecting Sort From the Records Menu.

                                  Here's a demo file you can examine: http://www.4shared.com/file/yTNNbcpA/CountingRecordsSummaryRpt.html

                                  One layout shows the summary report and the other shows the actual records.