There are a lot of ways to count the records that store a specific value in a specific field. Which solution is best can depend on the structure of your database.
Here's a very simple way to count them:
Enter find mode by clicking the find button at the top of your screen.
Enter y in the field
Click the perform find button.
In the status area, you'll see what looks like a fraction x/y where y is the total records in your table and x are the number in your found set. This total is the count of all records in your database matching the specified criteria.
Here's a more sophisticated approach that only works in FileMaker 11:
Open Manage | Database | Relationships and find the table occurrence "box" for this table. Select it and click the button with two green plus signs. This creates a new "occurrence" of the same table. You have not created a new table. Drag from any field in the one box to any field in the other so that they are connected by a relationship line. Double click this line and use the operator drop down to change the = to X so that you get a relationship that looks a bit like this:
YourOriginalTable::somefield X YourOriginalTable 2::somefield
Now place a portal to YourOriginalTable 2 on your layout. Make it a one row portal and click the portal filter option in Portal Setup... Enter this expression (modify it to fit your table occurrence and field names): YourOriginaltable 2::FieldwithY = "Y"
Put the total_entries summary field inside this one row portal and it will report the total records with "y" in the field you specify in the portal filter expression.
Okay, terrific, Phil. I'll approach this using a portal / occurence method.
I frequently use the "search *" and sort functions to report these types of totals. The Dashboard layout I'm working on now will (hopefully) provide a top-level view of these common searches and sorts. In other words, I'm spending 40 hours to save 10 minutes. (But hopefully I learn enough through this exercise to save another 10 minutes sometime in the future.)
After I explore your excellent solution I'll be facing the sum of records found based on specific text data from TWO or more fields. For example, "Total number of high school graduates that are Hispanic: <number>" I'm going to shelve that inquiry until I fully grok this first step.
Thanks, Phil! I appreciate your help.
Once you can filter by a value in one field, you can use more complex filter expressions to filter on values in more than one field, by using And, Or or even a Case function.
You can even include a reference to a field on your layout so that selecting a value in this field controls what value is used in the filter so that one portal can dynamically report different totals based on how the user manipulates the value in this field.
Maybe relevant, maybe not. The field I am trying to return a sum of records for either contains a Y or is blank.
Ran into a few obstacles. I'll replicate it step-by-step:
1. In my dashboard layout I open the "Manage Database" panel and go to the Relationships tab.
2. I click on the double-plus button to create an occurence of the table.
3. I select the Graduate field in my OriginalTable and drag it to the matching Graduate field in the OriginalTable 2 occurence.
4. I change the default "=" symbol to an "X" symbol in the link between those two fields.
5. I click OK to close that panel.
6. I go into Edit Layout mode and from the drop-down Insert menu, click on "Portal."
7. I change the default number of rows (5) to "1", confirm the portal is showing related records from OriginalTable2, then toggle the box for "Filter portal records". This action automatically opens the "Specify / Options" panel.
(NOTE: I can OK at this stage and exit layout mode to view the results. The portal returns nothing...a blank box.)
9. I manually add " = Y" to the end of the summary / calculation so that it now reads, "OriginalTable 2::graduate = Y.
10. When I click OK to accept this I get an error that reads "The specified table cannot be found."
11. Undeterred, I remove the "= Y" from the calculation and just close that portal control panel.
12. Within the portal shape, I insert a field that returns my (previously created) "graduate_count" summary field. It returns the results of a "Count of graduate".
I feel like I'm missing a critical step here. Up until now I have not successfully specified that I am looking only for the sum of "Y" data.
13. I click Okay and exit layout mode to preview my handiwork.
I have a beautiful gray box that would make my mother proud... but it is empty.
Phil, do you see my critical error?
Put the Y inside quotation marks so that FileMaker knows this is a literal value, not the name of a field.