Jump to content

Any smart and fast way to count by group?

This topic is 2592 days old. Please don't post here. Open a new topic instead.

Recommended Posts

MS Access has 'group by' and it easily shows the number of records after grouped by particular field.



[DATA ( 5 records ) ]

Type     Quantity

001       1

001       1

002       3

003       1

003       2


MS Access shows 3 (The number of records after grouped by 'Type') and result below in a second.


001      2

002      3

003      3



As far as I know, I have to make another table to have unique record(by 'Type) , and to count quantity.

This is too much work compared to MS Access.


In another way, I have to Make List view and add'sub-summary when sorted by' to it, and sort every time :(

In addition, it won't tell me 3 (The number of records after grouped by 'Type')


Are there any easy tips to achive this in another way?



Link to comment
Share on other sites

Filemaker is not Access and if you're going to approach it with an Access state-of-mind, you're likely to become very frustrated.


Case in point: when it comes to summarizing, you need to ask yourself: what do I want summarize? Filemaker has two different types of sets of records: the found set and the related set. You use summary fields to summarize the former (and yes, it needs to be sorted for sub-summary values to show), and calculation fields and/or summary fields to summarize the latter (and yes, you do need to define the tables and the relationships in advance for this to work).


Version 12 and higher offer a third option in the form of the ExecuteSQL() function - which allows you to both define and summarize a set of records of its own.

Link to comment
Share on other sites



As far as I know, I have to make another table to have unique record(by 'Type) , and to count quantity.

This is too much work compared to MS Access.



The example you describe does not need an extra table in FM to work.

You just need a new summary field to count the IDs,

Then create a layout with a subsummary part when sorted by Group, put the summary field in the subsummary part, no body part required for that layout.

Then sort your records by Group and you'll see the results that you are after.


As Comment indicated; this is only one of the ways you can do this.  And depending on the number of records you are displaying you may need to manage the display of any summary fields carefully.

Link to comment
Share on other sites

You could also use SQL to accomplish this.

Set Variable[$result ; 



SELECT Type,count(Type)
FROM MyTable
This would give you exactly what you are looking for as well, but if you need to do anything specific with the result you will need to parse it.
Be careful if you suspect your query will need to process/return record sets > 500. FileMaker will do it, but performance degrades quickly if the resulting data set that has to be processed is too large.  Use of the WHERE clause is very important when dealing with SQL in FileMaker to keep your performance up to snuff.
Link to comment
Share on other sites

Thanks guys. I know how to do summary using the found set or the related set.


But I have to have an actual new field(summary) for that or, I have to created new relationship for that.

I can get the same result by just few step if I use MS Access.

So I just wanted to know how to do this real quick.


Brian's way looks the best solution in my case so far.

Link to comment
Share on other sites

This topic is 2592 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Similar Content

    • By Sinky
      Hi guys!
      I hope someone can help me with this.
      I have a SUMMARY LIST field which contains types of items eg. (shoes, shoes,  sneakers, boxers, jumpers, boxers, jackets, shoes, sneakers...). These items are also listed in a value list (boxers, jumpers, shoes, jackets, sneakers)
      Is there a way how I can generate a count list of items by and in order of the value list? Taken the example above,  this count list would look like this (2, 1, 3, 1, 2). The commas here should actually be the carriage return.
      Tnx a lot! 
    • By Peterteneldas
      Hello, everyone, here’s my latest question. I hope this is the proper area to post it.
      I’m creating a flat-file movie database to keep track of where my movies are in my home. I added a serial number field, which gives me a running total of how many movies are in my collection.
      But I think I need something different, and am requesting help for creating it. I have several duplicate movies in my collection, and I don’t want the duplicates included in any total count of my movies. At least, not in my forms or reports. I don’t care if duplicates get serial numbers, and I don’t care if the count in the toolbar (i.e. 516 unsorted) counts them all. I understand that the serial numbers and toolbar count are for “records.” But I want a total count of my movies, excluding duplicates.
      What I’ve done is create a checkbox for duplicates. If the box is checked, then of course, the movie is a duplicate. Is it possible to create a field that has a running count of the movies in my collection, EXCLUDING the duplicates? If it is possible, what is the procedure from omitting them from any such subtotal, summary, or running total?
      Thank you! PT
    • By -dp-
      I have a layout that includes two sub-summaries. The topmost includes a summary field called studentGradeCount, which counts the number of students in a given grade.
      The subordinate sub-summary contains applicationStatusCount, which is a summary field providing a count of applicationStatus.
      Adjacent to the status count, I would like to provide a percentage, indicating what percent of the students have applications at each status. Logically, it would be
      applicationStatusCount / studentGradeCount
      But FileMaker doesn't return anything when I attempt this. Any help would be appreciated.
      FMPA 16.0.3
      macOS 10.13.3

    • By bcooney
      I'm running into an issue hiding a text object in a sub summary part when a summary field in the same part is empty or zero.
      I created a little demo file to show my issue. I resorted to using Get Summary, but that still doesn't work in my solution (but it's much more complicated report there).
      I'd appreciate feedback as to why I needed Get Summary.
    • By Matthew Bloomfield
      I made a Filemaker Database a couple of years ago for a correspondence school, now I'm building the same thing for another school. The database has a record for each student and a field for each possible lesson they can be sent. When a lesson is sent the date goes in the appropriate field.
      For reporting I had no idea what I was doing, so I just made a table with a field for each lesson and a script that would search within a date range, take a record count of the returned records take that number and put it in my report table. The date range is set by global fields for the start date and the end date. The script would repeat for the 200 or so lessons that we had available. I would then have a report of the number of each lesson that was sent in a date range. 
      I guessing that there must be a better way, I have never used a summary report, is that something that would work? If someone could point me in the right direction I would be very very grateful.
      Thanks heaps in advance for any help or pointers. Using Filemaker 16.
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.