A Summary report can do this easily,
- Define a summary field that is the "count of" the Drug ID field.
- Create a layout based on your drug requests table.
- In layout mode, double-click the "body" layout label and change the body to a subsummary when sorted by your DrugID field.
- Place your DrugID, the new summary field and any other fields you want (like a description or name field) in this part.
- Return to browse mode, select list view and sort your records by DrugID.
- 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.
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
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.
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...
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.
Yes. It is in List View, but since there are no records, the Sort option in the records menu is dimmed.
Well, you have to perform a find to pull up the records you want in your report, then sort them.
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?
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?
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.
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.
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.
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.