But are you counting the VALUES or the number of RECORDS?
If you are counting unique values, there's a count of 3 here. If you want a record count, you'd get:
Both are possible in FileMaker And SQL is even an option for getting either count though not the only option.
It also can make difference as to whether you want this count over a) all the records in your table, b) all the records in your current found set or c) a set of related records in the table (such as all records you might list in a portal).
Thanks PhilModJunk for your quick response. Let me answer your questions and then refer to an attachment that I will upload.
I am wanting counts of records, so in your example I want to display on a layout what you posted in your reply:
This count comes from a related table to the layout that displays the list and would be only those related records. My attachment displays a dashboard. On the dashboard highlighted in yellow is the area where I want this list to appear. The list will be dynamic and different from each user's dashboard to the next. I can't find a way to make this work and would appreciate your help. The related table has the userid and the name of the alert as fields. Just don't know how to summarize those counts and only display those summarized results on the dashboard.
And what is the relationship used?
This sounds like an approach best implemented with the ExecuteSQL function and then set up your chart with the Delimited data source option charting the delimited data from the SQL query. This should put you on familiar territory given your reference to wanting to use SQL in your original post.
You can check out the specific details of how FileMaker SQL works here: https://fmhelp.filemaker.com/docs/13/en/fm13_sql_reference.pdf
No not wanting to build a chart - I want to build a list (see my previous post). Ok ExecuteSQL function might be the way to go, but not having much success with that. I know SQL and can build the correct query. But this query will produce a list of rows - having difficulty knowing how to (1) execute it in Filemaker, and (2) displaying the results of the query on the layout in my attached file in the yellow area. That's my issue.
Just define the ExecuteSQL() function call in an unstored calculation field and put it on your layout. You can specify a return (¶) as the Record separator and the circled box on your example can be the one single field.
This is not the only option given your comment that this is related data.
Have a very similar question, but unfortunately, do not know SQL.
Have a table about residential homes that have sold. Each record contains data about a single home. Need to grab:
- counts (number of homes sold) by year sold,
- average square footage by year sold, and
- average price/square foot by year sold.
To that end, have the following fields for each record:
- COEYear (Close of Escrow Year) - calculated from Close of Escrow date [ Year ( COEDate ) ]
- SalesPrice - number
- SquareFoot - number
- PricePerSquareFoot - calculated = SalesPrice / SquareFoot
It is really the "by year sold" that is giving me challenges. Know how to do simple counts and averages using the "Summary" type calculator.
I know this must be really easy, but having trouble getting it started. As always, really appreciate your help!