How do I set up a summary field for number of years when each year appears in multiple records? Thanks.
ExecuteSQL( "SELECT YourYearField, COUNT(DISTINCT YourYearField) FROM YourTableName";"";"" )
would return something like:
If you just need the number of years for a specific year:
ExecuteSQL( "SELECT COUNT(YourYearField) FROM YourTableName WHERE YourYearField = ?";"";""; "2012" )
Or if you want a count of how many unique years you have:
ExecuteSQL( "SELECT COUNT(DISTINCT YourYearField) FROM YourTableName";"";"" )
ExecuteSQL() is a highly flexible function for this type of aggregation. This whitepaper is a good read to see what's possible:
The Missing FM 12 ExecuteSQL Reference | FileMakerHacks
IF you don't want to use SQL and you have FileMaker 16, you could:
Set up a summary field with the "list of" option to list the year field. This will list the same year more than once if it appears in more than one record so on to step two:
Define a calculation field as:
ValueCount ( UniqueValues ( ListOfSummaryFieldHere ) )
Unlike ExecuteSQL, you can perform finds to pull up different groups of records and this unstored calculation will update to give you the count of years over your found set. That could be an advantage over SQL or a disadvantage depending on what you need.
(If you are using an older version of FileMaker, you won't have the UniqueValues function, but you might use a custom function that achieves the same result if you have FileMaker Advanced.)
I have FM 11 Pro- what would work best? There are many records with the same year- I want the field to show how many different years there are. Thanks.
When asking a question about an older version of FileMaker, it really saves you and those that try to answer your question a lot of time otherwise wasted by suggesting solutions that won't work with your older version.
None of the suggestions are possible for your older version.
Two options for unique counts come to mind from "back then":
Sum the reciprocal--this one's gnarly but it works and can return counts from a given found set of records.
Count the value list values--if you set up a "use values from field" value list, the list will omit duplicates and you can use: ValueCount ( ValueListItems ( Get ( FileName ) ; "valueListNameHereInQuotes" ) )
The second option returns a count from all records in your table.
Let me know which you need, a found set based count or the count from all records in the table and I'll describe one of these in more detail.
"it really saves time", if you tell us what version that you are using.
Define a value list for the year field, then make a unstored calculation field as
ValueCount ( ValueListItems ( "" ; "theValueListName" ) )
But this count on whole table, not found set.
Simple and elegant! Thank you. My apologies to everyone for neglecting to state which version I am using- not my usual habit. Thanks again, Jim
Retrieving data ...