Any smart and fast way to count by group?

[mak]

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

 

e.g.

[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?

 

 

[comment]

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.

[Wim Decorte]

 

 

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.

[bruceR]

Doesn't seem so difficult. See attached example.

SimpleSummary.fmp12.zip

[Brian C]

You could also use SQL to accomplish this.

Set Variable[$result ; 

 

ExecuteSQL("

SELECT Type,count(Type)

FROM MyTable

GROUP BY Type

";"";"";"")

 

]

 

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.

[mak]

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.