I had the same question yesterday. My table has a field named z_Record_Type, and I wanted to know how many records of each type it contained.
I have created a calculation field named. Z_Count_Type_A which contains a 1 when z_Record_Type = A and a second one named Z_Count_Type_B which contains a 1 when z_Record_Type = B
The rest was easy. 1 statistical fields count based on z_Count_Type_A. And another one based on z_Count_Type_B.
Note : in english, statistical fields are name differently, but I dont remember how this type is called in english.
Hoping it will help you.
Looks like you need a summary field in this case to give me the layout part values I wanted. You might have something like the following in your data. I'm guessing that bit!
Country County ObservedA ObservedB
A North 1
A North 2 1
A South 2 1
A South 4 3
Create two further fields:
sObservedA (Summary type referring to ObservedA)
sObservedB (Summary type referring to ObservedB)
Set your layout parts up with the following (which I'd guess you have already):
Remove the body part, it's not needed
Place the two summary fields on the layout part for County next to each other
You should get something like:
County North 3 1
County South 6 4
If you wanted to get grand totals per ObservedA and ObservedB, then put a copy of the sObservedA and sObservedB field on the Country part.
Hope that helps
Thank you very much. My problem was that instead of making a count-of summary field, I had made a calcualtion field using the count operator. Logic is not always that simple!
The count function is designed to be used in one of three ways:
Count ( Field1 ; field2 ; field3 )-->returns a number from 0 to three from just these fields in the current record
Count ( RepeatingField ) ---> counts the number of non-blank values from the repetitions of this field
Count ( RelatedTable::Field ) ----> counts the number of non-blank fields from the set of related records in a table linked by relationship to the table occurrence used as the "context" for this calculation.
Which is why, as you discovered, it didn't work for you. Count ( Field1 ) can only return a value, at most of 1 as it's only referencing a single field in a single record.
Has anyone posted a request that the COUNT function have another (internal to FMP, of course) overloaded method -- to actually count the number in the table it's in without using a self-join or other gymnastics?
Being able to get a COUNT of the table I'm in (a single table solution, for example) seems like the most expected use-case. Yet, it's not there.
You'd need to check that for yourself and see what has been posted over in Product Ideas. I can't say that I've ever had a major need for such as the available methods aren't all that hard to use, but by all means, if you don't find it posted over there, post it and see what others have to say about it.
As an expert, you can always devise a workaround. I get that.
IMHO, though, it's really a matter of semantics and expectation.
In excel, for example, count works like you'd expect: in the range in THAT sheet.
In SQL: same table (as in: "select count(*) from names")
Why FM is different is needless.
COUNT, SUM, etc. should simply be overloaded (in the (hopefully, encapsulated) FM code) to handle counts, sums, etc., on the same table. Simple. Expected. Obvious.
Thanks for your reply, PMJ.
there are quite a few Excel features that would be handing in a Table of data in FileMaker. And Count() or other aggregates working on fields in a found set to work as the summary fields work, but into CALCULATED (or Set Field) way would be handy!
works on the found set and returns the value as it would be in the summary field (based on the sort), but the summary field is first created to be used in this function.
If I read your posts correctly, fmpdude, this is what you are saying:
a 'GetSummary()' without first needing to create the summary field?
eSQL working on a found set to give us these aggregate values?
hate that auto-correct that is anything but "correct".
I can't edit at the moment...
change "handing" to "handy" in the first paragraph above!
No, what I was saying was that the COUNT, SUM, and other functions should (IMHO) automatically work on the same table without self-joins, summary fields, or other special steps. All new users to FMP bump into this counter-intuitive behavior - that is that COUNT doesn't work on the same table as expected.
I can't count the times philmodjunk helped me with things like this when I was just a babe in the FMP woods. (Thanks again, Phil!)
So, how to do this?
Programmatically, speaking, FMP, assuming it's object oriented internally would just need another overloaded function for SUM, COUNT, etc., which, based on the input parameters would decide how to do the SUM, COUNT, etc.
So, instead of three overloaded functions for COUNT like it has now (for the three use-cases COUNT currently handles), it would have four. At run-time, the correct COUNT function/method would automatically be chosen to run (from the OO programming language FM is written in) given the particular parameter list sent.
Since we're at version 15, and it doesn't work like this, I didn't submit a feature request.
FMP is fine the way it is. This is a minor enhancement I wish had been there for a while.
Yes, like GetSummary() without the summary field creation requirement, but also COUNT, SUM, etc., should just work without needing to call a separate function name.
That's the beauty of OO: you can call the same function name and IT (the language) can figure out which of possibly several different methods to call to run the right code.
So, you call COUNT, SUM, etc., and the language (behind the scenes) determines the right code to run.
As FM developers, we then just call COUNT, SUM, etc., and it just ... Works!
The beauty of this approach is you call the same function, and based on what you're doing, IT figures out what to do.
However, I do not know any internals of FMP and thus have no idea how they have implemented ... anything.